MySQL 语句：数据定义语句（DDL）：“CREATE TABLE”语句
关于
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) }
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 (expr)
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
}
data_type:
(see Chapter11, Data Types)
key_part:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT '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
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
| 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}
| 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 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]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE用给定名称创建一个 table。
- 必须具有该 table 的“CREATE”特权。
- 默认情况下，table 是使用InnoDB存储引擎在默认数据库中创建的。如果该 table 存在，没有默认数据库或该数据库不存在，则会发生错误。
- MySQL 对 table 的数量没有限制。基础文件系统可能会对表示 table 的文件数量有所限制。各个存储引擎可能会强加特定于引擎的约束。 InnoDB允许多达 40 亿个 table。
CREATE TABLE 语句有多个方面：
Table Name
“tbl_name”：
可以将 table 名指定为“db_name.tbl_name”，以在特定数据库中创建 table。不管是否存在默认数据库(假定数据库存在)，此方法都有效。
- 如果使用带引号的标识符，请分别为数据库和 table 名加上引号。例如，“'mydb'.'mytbl'”而不是“'mydb.mytbl'”。
“IF NOT EXISTS”：
如果 table 存在，则防止发生错误。但是，没有验证现有 table 具有与 CREATE TABLE 语句指示的结构相同的结构。
Temporary Tables
- 创建 table 时，可以使用TEMPORARY关键字。 TEMPORARY 表仅在当前会话中可见，并且在关闭会话时会自动删除。
table 克隆和复制
- “LIKE”：
- 使用“CREATE TABLE ... LIKE”根据另一个 table 的定义创建一个空 table，包括在原始 table 中定义的所有列属性和索引：
CREATE TABLE new_tbl LIKE orig_tbl;
- “[AS] query_expression”：
- 要从另一个 table 创建一个 table，请在 CREATE TABLE 语句的末尾添加一个SELECT语句：
CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
- “IGNORE | REPLACE”：
- IGNORE 和 REPLACE 选项指示在使用SELECT语句复制 table 时如何处理重复唯一键值的行。
列数据类型和属性
每个 table 有 4096 列的硬限制，但是对于给定的 table，有效最大值可能更少，并且取决于“table 列数和行大小的限制”的因素。
- “data_type”表示列定义中的数据类型。
- 某些属性并不适用于所有数据类型。
- 如：“AUTO_INCREMENT”仅适用于整数和浮点类型。“DEFAULT”不适用于“BLOB”，“TEXT”，“GEOMETRY”和“JSON”类型。
- 字符数据类型（CHAR，VARCHAR，TEXT类型，ENUM，SET和任何同义词）可以包括“CHARACTER SET”来指定列的字符集，可以使用“COLLATE”属性以及任何其他属性来指定字符集的排序规则。
- 【“CHARSET”是“CHARACTER SET”的同义词】
- MySQL 5.7 在字符的字符列定义中解释长度规范。 【BINARY 和 VARBINARY 的长度以字节为单位。】
- 索引前缀：【？？？】
- 对于 CHAR，VARCHAR，BINARY 和 VARBINARY列，可以使用“col_name(length)”语法指定索引前缀长度，以创建仅使用列值开头的索引。BLOB 和 TEXT 列也可以构建索引，但必须提供前缀长度。
- 对于非二进制字符串类型，前缀长度以字符形式给出；
- 对于二进制字符串类型，前缀长度以字节形式给出。
- 也就是说，索引条目由“CHAR”，“VARCHAR”和“TEXT”列【非二进制字符串类型】的每个列值的前“length”个字符；以及“BINARY”，“VARBINARY”和“BLOB”列【二进制字符串类型】的每个列值的前“length”个字节组成。像这样仅索引列值的前缀可以使索引文件小得多。
- 仅InnoDB和MyISAM存储引擎支持 BLOB 和 TEXT 列上的索引。例如：
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
- 从 MySQL 5.7.17 开始，如果指定的索引前缀超过了最大列数据类型的大小，则 CREATE TABLE 如下处理索引：
- 对于非唯一索引，将发生错误(如果启用了严格的 SQL 模式)，或者将索引长度减小到最大列数据类型大小之内，并且会产生警告(如果未启用严格的 SQL 模式)。
- 对于唯一索引，无论采用哪种 SQL 模式，都会发生错误，因为减小索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。
- JSON列无法构建索引。您可以通过在生成的列上创建索引来解决此限制，该索引从JSON列中提取标量值。
- “NOT NULL | NULL”：如果未指定NULL或NOT NULL，则该列将被视为已指定NULL。
- 在 MySQL 5.7 中，只有InnoDB，MyISAM和MEMORY存储引擎支持可以具有 NULL 值的列上的索引。在其他情况下，必须将索引列声明为 NOT NULL 或错误结果。
- “DEFAULT”：指定列的默认值。
- 如果启用了“NO_ZERO_DATE”或“NO_ZERO_IN_DATE” SQL 模式，并且根据该模式设置的日期值默认值不正确：如果未启用严格 SQL 模式，则CREATE TABLE会产生警告；如果启用严格模式，则会产生错误。例如，启用 NO_ZERO_IN_DATE 时，“c1 DATE DEFAULT '2010-00-00'”会产生警告。
- “AUTO_INCREMENT”：整数或浮点列可以具有附加属性。在索引 AUTO_INCREMENT 列中插入NULL(推荐)或0的值时，该列将设置为下一个序列值。【AUTO_INCREMENT 以1开头】
- 要在插入行后检索AUTO_INCREMENT值，请使用SQL 函数“LAST_INSERT_ID()”或 C API 函数“mysql_insert_id()”。
- 如果启用了“NO_AUTO_VALUE_ON_ZERO” SQL 模式，则可以将0在AUTO_INCREMENT列中存储为0，而无需生成新的序列值。
- 每个 table 只能有一个 AUTO_INCREMENT 列，必须对其进行索引，并且不能具有DEFAULT值。
- AUTO_INCREMENT 列仅包含正值时才能正常工作。插入负数被视为插入非常大的正数。这样做是为了避免数字从正数“换”为负数时出现精度问题，并确保您不会偶然得到包含0的AUTO_INCREMENT列。
- 对于MyISAM表，您可以在多列键中指定AUTO_INCREMENT辅助列。【？？？】
- “COMMENT”：列的 Comments 可以使用COMMENT选项指定，最长为 1024 个字符。
- “COLUMN_FORMAT”：在 NDB 群集中，也可以使用 COLUMN_FORMAT 为 NDB 表的各个列指定数据存储格式。允许的列格式为“FIXED”（默认值，指定固定宽度存储），“DYNAMIC”（允许列为可变宽度）和“DEFAULT”（使用固定宽度或可变宽度存储）。
- 从 MySQL NDB Cluster 7.5.4 开始，对于 NDB 表，COLUMN_FORMAT 的默认值为“FIXED”。 (除 MySQL NDB Cluster 7.5.1 中，默认值为“DYNAMIC”)
- COLUMN_FORMAT 当前对使用 NDB 以外的存储引擎的表的列无效。在 MySQL 5.7 和更高版本中，COLUMN_FORMAT 被静默忽略。
- “STORAGE”：对于 NDB 表，可以使用STORAGE子句指定该列的存储位置：是存储在磁盘上（STORAGE DISK）还是存储在内存中（STORAGE MEMORY）。
- 使用的CREATE TABLE语句必须仍然包含 TABLESPACE 子句：
mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) ENGINE NDB; ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140) mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) TABLESPACE ts_1 ENGINE NDB; Query OK, 0 rows affected (1.06 sec)
- 对于 NDB 表，STORAGE DEFAULT等效于STORAGE MEMORY。
- STORAGE子句对 NDB 以外的存储引擎的表不起作用。 NDB Cluster 随附的mysqld版本仅支持STORAGE关键字；在任何其他版本的 MySQL 中都无法识别它，其中使用STORAGE关键字的任何尝试都会导致语法错误。
- “GENERATED ALWAYS”：用于指定生成的列表达式。
- 存储的生成列可以被索引。
- InnoDB支持虚拟生成的列上的二级索引。
索引和外键
适用于创建索引和外键的关键字：
- CONSTRAINT symbol：
- 可以提供CONSTRAINT symbol子句以命名约束。如果未提供该子句，或者在CONSTRAINT关键字后不包含“symbol”，则 MySQL 将自动生成约束名称。
- 如果在外键定义中未提供 CONSTRAINT symbol 子句，或者在CONSTRAINT关键字后未包含“symbol”，则NDB使用外键索引名。
- “symbol”值(如果使用)对于每种模式(数据库)和每种约束类型必须是唯一的。重复“symbol”导致错误。
- 【SQL 标准指定所有类型的约束(主键，唯一索引，外键，检查)都属于同一名称空间。在 MySQL 中，每个约束类型每个模式都有其自己的名称空间。因此，每种约束类型的名称在每个架构中必须唯一。】
- PRIMARY KEY：
- 唯一索引，其中所有键列必须定义为“NOT NULL”【如果未将它们显式声明为NOT NULL，则 MySQL 会如此隐式(无声地)声明它们】。
- 一个表只能有一个PRIMARY KEY。
- PRIMARY KEY的名称始终为PRIMARY，因此不能用作任何其他种类的索引的名称。
- 如果没有 PRIMARY KEY 并且应用程序在 table 中要求 PRIMARY KEY，则 MySQL 将返回的第一个没有“NULL”的“UNIQUE”索引列作为PRIMARY KEY。
- 在创建的 table 中，首先放置 PRIMARY KEY，然后放置所有 UNIQUE 索引，然后放置非唯一索引。【这有助于 MySQL 优化器确定使用哪个索引的优先级，还可以更快地检测到重复的 UNIQUE 键】
- PRIMARY KEY可以是多列索引。但是，您不能使用列规范中的PRIMARY KEY键属性创建多列索引。这样做只会将该单列标记为主要列。您必须使用单独的“PRIMARY KEY(key_part, ...)”子句。
- 如果 table 的“PRIMARY KEY”或“UNIQUE NOT NULL”索引由具有整数类型的单个列组成，则可以使用“_rowid”来引用SELECT语句中的索引列。【？？？】
- 在 MySQL 中，PRIMARY KEY 的名称为 PRIMARY。对于其他索引，如果未分配名称，则为索引分配与第一个索引列相同的名称，并带有可选的后缀(_2，_3，...)以使其唯一。您可以使用“SHOW INDEX FROM tbl_name”查看 table 的索引名称。
- FOREIGN KEY：
- 外键约束。
- 使用 InnoDB 存储引擎的分区表不支持外键。【？？？】
- reference_definition：
- 外键约束。
- InnoDB 和 NDB 支持检查外键约束。对于其他存储引擎，MySQL Server 会解析并忽略“CREATE TABLE”语句中的“FOREIGN KEY”和“REFERENCES”语法。
- 被引用表的列必须始终明确命名。支持外键上的“ON DELETE”（级联更新）和“ON UPDATE”（级联删除）操作。
- reference_option：
- 有关 RESTRICT，CASCADE，SET NULL，NO ACTION 和 SET DEFAULT 选项的信息，请参见第 13.1.18.5 节“外键约束”。【？】
- UNIQUE：
- 唯一性约束。
- 对于所有引擎，UNIQUE 索引允许可以包含 NULL 的列使用多个 NULL 值。
- 如果 table 的“PRIMARY KEY”或“UNIQUE NOT NULL”索引由具有整数类型的单个列组成，则可以使用“_rowid”来引用SELECT语句中的索引列。【？？？】
- KEY | INDEX：
- KEY 通常是 INDEX 的同义词。在列定义中给出键属性 PRIMARY KEY 时，也可以仅将其指定为 KEY【这样做是为了与其他数据库系统兼容】。
- FULLTEXT：
- 用于全文搜索的特殊索引类型。索引总是在整个列上进行；不支持列前缀索引，并且如果指定则忽略任何前缀长度。
- 仅 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引。
- 只能从 CHAR，VARCHAR 和 TEXT 列创建它们。
- 如果全文索引和搜索操作需要特殊处理，则可以将“WITH PARSER”子句指定为“index_option”值，以将解析器插件与索引关联。【此子句仅对 FULLTEXT 索引有效】
- WITH PARSER：
- WITH PARSER 选项只能与 FULLTEXT 索引一起使用。如果全文索引和搜索操作需要特殊处理，它将解析器插件与索引关联。【 InnoDB和MyISAM都支持全文分析器插件】
- SPATIAL：【？？？】
- 您可以在空间数据类型上创建SPATIAL索引。
- 仅 InnoDB 和 MyISAM 存储引擎支持空间类型。且索引列必须声明为NOT NULL。
- CHECK：
- CHECK子句已解析，但被所有存储引擎忽略。
- key_part：
- key_part 规范可以以 ASC 或 DESC 结尾。这些关键字允许将来用于指定升序或降序索引值存储的扩展。
- 目前，它们已被解析但被忽略；索引值始终按升序存储。
- index_type：
- 一些存储引擎允许您在创建索引时指定索引类型。语法为“USING type_name”。如：【USING的首选位置在索引列列表之后】
CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
- index_option：
- 指定索引的其他选项。
- KEY_BLOCK_SIZE：
- 对于 MyISAM 表，KEY_BLOCK_SIZE(可选)以字节为单位指定用于索引键块的大小。该值被视为提示；如有必要，可以使用其他大小。
- 为单个索引定义指定的 KEY_BLOCK_SIZE 值将覆盖表级 KEY_BLOCK_SIZE 的值。
- COMMENT：
- 可以使用index_option COMMENT子句为单个索引设置InnoDB MERGE_THRESHOLD的值。【？？？】
- 在 MySQL 5.7 中，索引定义可以包含最多 1024 个字符的可选 Comments。
Table 选项
table 选项用于优化 table 的行为。
- 【在大多数情况下，无需指定任何选项】
- 【除非另有说明，否则这些选项适用于所有存储引擎】（不适用于给定存储引擎的选项可以被接受并记住作为 table 定义的一部分）
如果您以后使用“ALTER TABLE”将 table 转换为使用其他存储引擎，则将应用这些选项。
ENGINE：指定 table 的存储引擎。
- 引擎名称可以不加引号或加引号。带引号的名称“'DEFAULT'”被识别但被忽略。
| Storage Engine | Description |
|---|---|
| InnoDB | 具有行锁定和外键的事务安全 table。新 table 的默认存储引擎。 |
| MyISAM | 二进制便携式存储引擎，主要用于只读或以只读为主的工作负载。 |
| MEMORY | 该存储引擎的数据仅存储在内存中。 |
| CSV | 以逗号分隔值格式存储行的 table。 |
| ARCHIVE | 归档存储引擎。 |
| EXAMPLE | 示例引擎。 |
| FEDERATED | 访问远程 table 的存储引擎。 |
| HEAP | 这是MEMORY的同义词。 |
| MERGE | MyISAM 表的集合用作一个 table。也称为 MRG_MyISAM。 |
| NDB | 群集的，基于内存的容错 table，支持事务和外键。也称为 NDBCLUSTER。 |
默认情况下，如果指定了不可用的存储引擎，则该语句将失败并显示错误。您可以通过从服务器 SQL 模式中删除“NO_ENGINE_SUBSTITUTION”来覆盖此行为，以便 MySQL 允许将指定的引擎替换为默认的存储引擎。
- 通常，在这种情况下，它是 InnoDB，这是“default_storage_engine”系统变量的默认值。禁用“NO_ENGINE_SUBSTITUTION”时，如果不遵守存储引擎规范，则会发生警告。
AUTO_INCREMENT：