MySQL 的 CREATE TABLE 语句用来根据指定的名称创建表，默认情况下，MySQL 创建表采用 InnoDB 存储引擎，若表已经存在，则会报错，需要结合 IF NOT EXISTS 来避免错误。
CREATE TABLE
语法
CREATE TABLE 语句有 3 种形式：
- 常规表创建；
- 从另一张表复制表结构及同步数据；
- 从另一张表只复制表结构。
具体如下：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
选项/修饰符
- TEMPORARY：创建临时表时的关键字，临时表仅在当前会话（session）中可见，并且在会话关闭时，会自动删除。
- IF NOT EXISTS：表不存在时，CREATE 语句生效，主要是为了避免表已经存在时的错误。
- tbl_name：指定的表名，如果要在当前会话的所选数据库创建表，只需指定表名，否则，若要在其它数据库创建表，需要指定库名（前提是要有相应的权限），如 db_name.tbl_name。
- create_definition：创建表的结构定义，结构如下：
具体结构如下：create_definition: { col_name column_definition | {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option] ... | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition | check_constraint_definition }-
column_definition: { data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] [ENGINE_ATTRIBUTE [=] 'string'] [SECONDARY_ENGINE_ATTRIBUTE [=] 'string'] [STORAGE {DISK | MEMORY}] [reference_definition] [check_constraint_definition] | data_type [COLLATE collation_name] [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] [check_constraint_definition] } -
index_type: USING {BTREE | HASH} -
key_part: {col_name [(length)] | (expr)} [ASC | DESC] -
index_option: { KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' | {VISIBLE | INVISIBLE} |ENGINE_ATTRIBUTE [=] 'string' |SECONDARY_ENGINE_ATTRIBUTE [=] 'string' } -
reference_definition: REFERENCES tbl_name (key_part,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]-
reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
-
-
check_constraint_definition: [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
-
- table_options：表的相关选项，如编码格式，自增起始值等等：
table_options: table_option [[,] table_option] ...-
table_option: { AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'} | CONNECTION [=] 'connect_string' | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | ENCRYPTION [=] {'Y' | 'N'} | ENGINE [=] engine_name | ENGINE_ATTRIBUTE [=] 'string' | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} | SECONDARY_ENGINE_ATTRIBUTE [=] 'string' | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1} | STATS_PERSISTENT [=] {DEFAULT | 0 | 1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}] | UNION [=] (tbl_name[,tbl_name]...) }
-
- partition_options：表分区的选项，结构如下：
partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)]-
partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'string' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [(subpartition_definition [, subpartition_definition] ...)]-
subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'string' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name]
-
-
- IGNORE | REPLACE：两选项表示从另一个表复制表时，唯一键冲突时的行处理方式。
- [AS] query_expression：从另一个表创建一个表的表达式，主要是 SELECT 语句：
query_expression: SELECT ... (Some valid select or union statement) - LIKE：只复制表结构，不同步数据时使用的关键字，示例如下：
CREATE TABLE new_tbl LIKE orig_tbl;
示例
创建用户表示例：
CREATE TABLE IF NOT EXISTS `user` (
`uid` bigint(20) unsigned NOT NULL COMMENT '用户 id',
`user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名称',
`user_email` varchar(128) NOT NULL DEFAULT '' COMMENT '用户 email 地址',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态，0、待审核，1、有效，2、无效',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`uid`),
UNIQUE KEY `uniq_user_email` (`user_email`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
