CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
create_definition:
col_name column_definition
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]
| YEAR
| CHAR[(length)]
| VARCHAR(length)
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT
| TEXT
| MEDIUMTEXT
| LONGTEXT
| ENUM(value1,value2,value3,...)
table_options:
table_option [[,] table_option] ...
table_option:
ENGINE [=] engine_name
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
partition_options:
{[LINEAR] HASH(expr)
| PARTITION BY [linear] KEY (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)}
}
[(partition_definition [, partition_definition] ...)]
注意：
-
engine_name仅能为忽略大小写的“InnoDB“
-
不建议含有枚举类型的表作为分片表，比如表结构：CREATE TABLE `test` (`id` enum('1','2','3') DEFAULT '1')。因为此种表在插入id列时，既可以使用枚举值插入，也可以使用枚举值的下标，'1'的下标是1，以此类推。若用户以枚举值进行分片，但是插入时确使用枚举值下标，因为dble不会将下标转换为枚举值，所以分片会出现问题，详细可参考issue ： https://github.com/actiontech/dble/issues/816。
例：
create table if not exists test(
id bigint primary key AUTO_INCREMENT,
col1 int not null default 5,
col2 int null COMMENT 'info for col1',
col3 varchar(20) not null,
col4 varchar(20) unique key
);
create table test(
id int primary key,
col_bit BIT(1),
col_tinyint TINYINT(2) UNSIGNED ZEROFILL,
col_smallint SMALLINT(3) UNSIGNED ZEROFILL,
col_mediumint MEDIUMINT(4) UNSIGNED ZEROFILL,
col_int INT(5) UNSIGNED ZEROFILL,
col_integer INTEGER(6) UNSIGNED ZEROFILL,
col_bigint BIGINT(7) UNSIGNED ZEROFILL,
col_real REAL(8,1) UNSIGNED ZEROFILL,
col_double DOUBLE(9,2) UNSIGNED ZEROFILL,
col_float FLOAT(10,3) UNSIGNED ZEROFILL,
col_decimal DECIMAL(11,4) UNSIGNED ZEROFILL,
col_numeric NUMERIC(12,5) UNSIGNED ZEROFILL,
col_date DATE,
col_time TIME(3),
col_timestamp TIMESTAMP(4),
col_datetime DATETIME(5),
col_year YEAR,
col_char CHAR(10) ,
col_varcgar VARCHAR(20) ,
col_binary BINARY(30),
col_varbinary VARBINARY(40),
col_tinyblob TINYBLOB,
col_blob BLOB,
col_mediumblob MEDIUMBLOB,
col_longblob LONGBLOB,
col_tinytext TINYTEXT ,
col_text TEXT ,
col_mediumtext MEDIUMTEXT ,
col_longtext LONGTEXT ,
col_enum ENUM('a','b','c')
);
或者
create table test(
id int primary key,
col1 varchar(20)
)ENGINE = innodb
AVG_ROW_LENGTH = 20
DEFAULT CHARACTER SET = utf8
CHECKSUM = 1
DEFAULT COLLATE = utf8_general_ci
COMMENT = 'info of table test'
CONNECTION = '111111'
DELAY_KEY_WRITE = 1
INSERT_METHOD = LAST
KEY_BLOCK_SIZE = 65536
MAX_ROWS = 3
MIN_ROWS = 2
PACK_KEYS = 1
ROW_FORMAT = DEFAULT;
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
alter_specification:
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX | KEY} [index_name]
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP {INDEX | KEY} index_name
| ADD [INDEX|KEY] [index_name] (index_col_name,...)
| DROP {INDEX|KEY} index_name
| ADD PRIMARY KEY (index_col_name,...)
| DROP PRIMARY KEY
| ALTER [COLUMN] col_name
{SET DEFAULT {literal | (expr)} | DROP DEFAULT}
| COMMENT [=] 'string'
例：
alter table test add column col5 int not null default 1 first,add column col6 int after col4;
alter table test change column col1 col1_new int after col3;
alter table test modify column col1_new varchar(20) after id;
alter table test drop column col6;
alter table test add key idx_col4(col4);
alter table test add index idx_col4(col4);
alter table test drop key idx_col4;
alter table test drop index idx_col4;
alter table test drop primary key;
alter table test add primary key (id);
alter table test alter column col set default 0;
alter table test alter column col drop default;
alter table test comment = 'string';
DROP TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
例：
drop table if exists test cascade;
drop table test restrict;
TRUNCATE [TABLE] tbl_name
例：
truncate table test;