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 列也可以构建索引，但必须提供前缀长度。
1. 对于非二进制字符串类型，前缀长度以字符形式给出；
2. 对于二进制字符串类型，前缀长度以字节形式给出。
也就是说，索引条目由“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 如下处理索引：
1. 对于非唯一索引，将发生错误(如果启用了严格的 SQL 模式)，或者将索引长度减小到最大列数据类型大小之内，并且会产生警告(如果未启用严格的 SQL 模式)。
2. 对于唯一索引，无论采用哪种 SQL 模式，都会发生错误，因为减小索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。
3. 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 定义的一部分）

如果您以后使用“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：
表格的初始 AUTO_INCREMENT 值。
- 在 MySQL 5.7 中，这适用于 MyISAM，MEMORY，InnoDB 和 ARCHIVE 表。
- 要为不支持 AUTO_INCREMENT 表选项的引擎设置第一个自动增量值，请在创建 table 后插入“虚拟”行，其值比所需值小一，然后删除虚拟行。【？？？】
- 对于在“CREATE TABLE”语句中支持 AUTO_INCREMENT 表选项的引擎，您还可以使用“ALTER TABLE tbl_name AUTO_INCREMENT = N”重置AUTO_INCREMENT值（“N”不能设置为低于该列中当前的最大值）。
AVG_ROW_LENGTH：
表的平均行长的近似值。【您仅需要为具有可变大小行的大型表设置此选项】
- 创建 MyISAM 表时，MySQL 使用“MAX_ROWS”和“AVG_ROW_LENGTH”选项的乘积来确定结果表的大小。如果您未指定任何选项，则 MyISAM 数据和索引文件的最大大小默认为 256TB。
  1. 如果要减小指针大小以使索引变小和变快，而实际上并不需要大文件，则可以可以通过设置“myisam_data_pointer_size”系统变量来减小默认指针大小。
  2. 如果希望所有表都可以超过默认限制，并且希望表的速度稍慢一些且比必要的要大，则可以通过设置此变量来增加默认指针的大小。将该值设置为 7 将允许表最大为 65,536TB。
[DEFAULT] CHARACTER SET：
指定 table 的默认字符集。
- CHARSET 是 CHARACTER SET 的同义词。
- 如果字符集名称为 DEFAULT，则使用数据库字符集。
[DEFAULT] COLLATE：
指定 table 的默认排序规则。
CHECKSUM：
如果希望 MySQL 维护所有行的实时校验和（即，当表更改时 MySQL 自动更新的校验和），请将其设置为 1。这使 table 的更新速度稍慢，但也更容易找到损坏的表。
- 仅 MyISAM 支持。
- “CHECKSUM TABLE”语句报告校验和。
COMMENT：
表格的 Comments，最多 2048 个字符。
- 可以使用table_option COMMENT子句为 table 设置InnoDB MERGE_THRESHOLD的值。【？？？】
- 【设置 NDB_TABLE 选项】在 MySQL NDB Cluster 7.5.2 和更高版本中，CREATE TABLE或ALTER TABLE语句中的表 Comments 还可用于指定 NDB_TABLE 选项 NOLOGGING，READ_BACKUP，PARTITION_BALANCE 或 FULLY_REPLICATED中的一到四个。【？？？】
COMPRESSION：
用于 InnoDB 表的页面级压缩的压缩算法。支持的值包括“Zlib”，“LZ4”和“None”。透明页面压缩功能引入了 COMPRESSION 属性。
- 仅位于 file-per-table 表空间中的 InnoDB 表支持页面压缩，并且仅在支持稀疏文件和打孔的 Linux 和 Windows 平台上可用。
CONNECTION：
FEDERATED 表的连接字符串。
- 较旧的 MySQL 版本使用“COMMENT”选项作为连接字符串。
DATA DIRECTORY、INDEX DIRECTORY：【？？？】
对于 InnoDB，“DATA DIRECTORY='directory'”子句允许在数据目录之外创建表，它们指定分别放置 MyISAM 表的“数据文件”和“索引文件”的位置。
与 InnoDB 不同，在创建带有 DATA DIRECTORY 或 INDEX DIRECTORY 选项的 MyISAM 表时，MySQL 不会创建与数据库名称相对应的子目录。
- 必须启用“innodb_file_per_table”变量才能使用 DATA DIRECTORY 子句。
- 必须指定完整目录路径。
- 从 MySQL 5.7.17 开始，您必须具有FILE特权才能使用 DATA DIRECTORY 或 INDEX DIRECTORY table 选项。
- 对于分区 table，将忽略 table 级别的 DATA DIRECTORY 和 INDEX DIRECTORY 选项。
- 这些选项仅在不使用“--skip-symbolic-links”选项时有效。您的 os 还必须有一个有效的安全线程“realpath()”调用。
1. 如果创建的 MyISAM 表没有“DATA DIRECTORY”选项，则将在数据库目录中创建“.MYD”文件。默认情况下，如果 MyISAM 在这种情况下找到了现有的“.MYD”文件，它将覆盖该文件。
2. 对于没有“INDEX DIRECTORY”选项创建的表，“.MYI”文件也是如此。若要抑制此行为，请使用“--keep_files_on_create”选项启动服务器，在这种情况下 MyISAM 不会覆盖现有文件，而是返回错误。
3. 如果使用“DATA DIRECTORY”或“INDEX DIRECTORY”选项创建了 MyISAM 表，并且找到了现有的“.MYD”或“.MYI”文件，则 MyISAM 总是返回错误。它不会覆盖指定目录中的文件。
DELAY_KEY_WRITE：
将 ENCRYPTION 选项设置为“'Y'”以对在“file-per-table”表空间中创建的 InnoDB 表启用页面级数据加密。选项值不区分大小写。
- InnoDB 表空间加密功能引入了 ENCRYPTION 选项；
- 必须先安装和配置keyring插件，然后才能启用加密。
INSERT_METHOD：
如果要将数据插入“MERGE”表，则必须用“INSERT_METHOD”指定应将行插入到的表。可使用的值：“FIRST”（将插入内容移到第一个表）、“LAST”（将插入内容移到最后一个表），“NO”（防止插入）。
- “INSERT_METHOD”是仅对“MERGE”表有用的选项。
KEY_BLOCK_SIZE：
1. 对于 MyISAM 表，KEY_BLOCK_SIZE(可选) 以字节为单位指定用于索引键块的大小。该值被视为提示；如有必要，可以使用其他大小。
  - 为单个索引定义指定的 KEY_BLOCK_SIZE 值将覆盖表级 KEY_BLOCK_SIZE 的值。
2. 对于 InnoDB 表，KEY_BLOCK_SIZE 指定要用于压缩的 InnoDB 表的page大小(以千字节为单位)。 KEY_BLOCK_SIZE 值被视为提示；如有必要，InnoDB可以使用其他大小。
  - KEY_BLOCK_SIZE 只能小于或等于“innodb_page_size”值。
    - 可能的 KEY_BLOCK_SIZE 值包括 0、1、2、4、8 和 16，具体取决于“innodb_page_size”。（ 0 表示默认的压缩页面大小，是 innodb_page_size 值的一半）
  - InnoDB 仅在 table 级别支持 KEY_BLOCK_SIZE。
  - KEY_BLOCK_SIZE 不支持 32KB 和 64KB innodb_page_size值。 InnoDB 表压缩不支持这些页面大小。
- Oracle 建议在为 InnoDB 表指定 KEY_BLOCK_SIZE 时启用“innodb_strict_mode”：
  1. 启用 innodb_strict_mode 时，指定无效的 KEY_BLOCK_SIZE 值将返回错误。
  2. 如果禁用 innodb_strict_mode，则无效的 KEY_BLOCK_SIZE 值将导致警告，并且 KEY_BLOCK_SIZE 选项将被忽略。
- 响应“SHOW TABLE STATUS”的“Create_options”列报告了最初指定的 KEY_BLOCK_SIZE 选项，而“SHOW CREATE TABLE”也是如此。
MAX_ROWS：
计划在 table 中存储的最大行数。这不是硬性限制，而是向存储引擎提示 table 必须至少能够存储这么多行。
- 从 NDB Cluster 7.5.4 开始，不建议使用 MAX_ROWS 和 NDB 表来控制 table 分区数。为了向后兼容，它在更高版本中仍受支持，但将来的版本中可能会删除它，改用“PARTITION_BALANCE”。
- MAX_ROWS 最大值为 4294967295；较大的值将被截断到此限制。
MIN_ROWS：
计划在 table 中存储的最小行数。 MEMORY 存储引擎使用此选项作为有关内存使用的提示。
PACK_KEYS：
【仅对 MyISAM 生效】
1. 如果要使用较小的索引，请将此选项设置为“1”。（这通常会使更新速度变慢，读取速度也会加快）
2. 将选项设置为“0”将禁用所有键打包。
3. 将其设置为“DEFAULT”会告诉存储引擎：仅打包长 CHAR，VARCHAR，BINARY 或 VARBINARY 列。
- 如果不使用 PACK_KEYS，则默认值为打包字符串，但不打包数字。如果您使用“PACK_KEYS=1”，数字也会被打包。
- 在打包二进制数字键时，MySQL 使用前缀压缩：
  1. 每个密钥都需要一个额外的字节，以指示上一个密钥的多少个字节与下一个密钥相同。
  2. 指向行的指针直接在键之后以高字节优先 Sequences 存储，以提高压缩率。
PASSWORD：
【此选项未使用。如果您需要加密“.frm”文件并使它们无法用于任何其他 MySQL 服务器，请与我们的销售部门联系。】
ROW_FORMAT：
定义存储行的物理格式。
创建禁用了 strict mode 的表时，如果不支持指定的行格式，则使用存储引擎的默认行格式。响应“SHOW TABLE STATUS”表的实际行格式在“Row_format”列中报告。 “Create_options”列显示“CREATE TABLE”语句中指定的行格式，“SHOW CREATE TABLE”也是如此。【？？？】
- 行格式选择取决于 table 所使用的存储引擎。
1. 对于 InnoDB 表：默认行格式由“innodb_default_row_format”定义，其默认设置为“DYNAMIC”。
  - 当未定义“ROW_FORMAT”选项或使用“ROW_FORMAT=DEFAULT”时，将使用默认行格式。则重建表的操作还将无提示地将表的行格式更改为“innodb_default_row_format”定义的默认格式。
  - 为了更有效地 InnoDB 存储数据类型，尤其是“BLOB”类型，请使用“DYNAMIC”。
  - 要为 InnoDB 表启用压缩，请指定“ROW_FORMAT=COMPRESSED”。
  - 仍可以通过指定“REDUNDANT”行格式来请求在旧版 MySQL 中使用的行格式。
  - 当您指定非默认的“ROW_FORMAT”子句时，请考虑同时启用“innodb_strict_mode”配置选项。
  - 不支持“ROW_FORMAT=FIXED”。如果在禁用“innodb_strict_mode”的同时指定R“ROW_FORMAT=FIXED”，则 InnoDB 发出警告并假定“ROW_FORMAT=DYNAMIC”。如果在启用“innodb_strict_mode”时指定了“ROW_FORMAT=FIXED”，这是默认值，则 InnoDB 返回错误。
2. 对于 MyISAM 表：对于静态或可变长度的行格式，选项值可以为“FIXED”或“DYNAMIC”。
  - myisampack 将类型设置为“COMPRESSED”。
3. 对于 NDB 表：MySQL NDB Cluster 7.5.1 和更高版本中的默认“ROW_FORMAT”是“DYNAMIC”。（以前是“FIXED”）
STATS_AUTO_RECALC：
指定是否自动为 InnoDB 表重新计算 persistent statistics（持久性统计）：
1. 值“DEFAULT”：使表的持久统计信息设置由“innodb_stats_auto_recalc”配置选项确定。
2. 值“1”：导致表中 10％的数据已更改时重新计算统计信息。
3. 值“0”：可防止对该表进行自动重新计算；
使用此设置，对表进行实质性更改后，发出“ANALYZE TABLE”语句以重新计算统计信息。
STATS_PERSISTENT：
指定是否自动为 InnoDB 表启用 persistent statistics（持久性统计）：
1. 值“DEFAULT”：使表的持久统计信息设置由“innodb_stats_persistent”配置选项确定。
2. 值“1”：启用表的持久统计信息。
3. 值“0”：关闭此功能；
通过“CREATE TABLE”或“ALTER TABLE”语句启用持久统计信息后，在将代表性数据加载到表中之后，发出“ANALYZE TABLE”语句以计算统计信息。
STATS_SAMPLE_PAGES：
估计索引列的基数和其他统计信息（例如“ANALYZE TABLE”计算的那些索引）时要采样的索引页数。
TABLESPACE：
用于在现有的“常规表空间”，“每表一文件表空间”（file-per-table tablespace）或“系统表空间”中创建表。
```
CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name
```
1. 要在“系统表空间”中创建 table，请指定“innodb_system”作为 table 空间名称。
  CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system
  - 使用“TABLESPACE [=] innodb_system”，无论“innodb_file_per_table”设置如何，都可以将任何未压缩行格式的表放置在系统表空间中。例如，您可以使用“TABLESPACE [=] innodb_system”将具有“ROW_FORMAT=DYNAMIC”的表添加到系统表空间。
2. 要在“每表一文件表空间”（file-per-table tablespace）中创建一个 table，请指定“innodb_file_per_table”作为 table 空间名称。
  CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table
  - 如果启用了“innodb_file_per_table”，默认即在创建InnoDB“每表一文件表空间”创建 InnoDB 表，而无需指定“TABLESPACE=innodb_file_per_table”。
- 指定的“常规表空间”在使用 TABLESPACE 子句之前必须存在。
- tablespace_name 是区分大小写的标识符。它可以被引用或不被引用。不允许使用正斜杠字符(“ /”)。以“ innodb_”开头的名称保留作特殊用途。
- “DATA DIRECTORY”子句与“CREATE TABLE ... TABLESPACE=innodb_file_per_table”一起允许，但不支持与 TABLESPACE 选项结合使用。
- 【从 MySQL 5.7.24 开始，不支持对带有“CREATE TEMPORARY TABLE”的“TABLESPACE = innodb_file_per_table”和“TABLESPACE = innodb_temporary”子句的支持，并将在将来的 MySQL 版本中删除。】
- STORAGE 表选项仅与 NDB 表一起使用。 STORAGE 确定使用的存储类型(磁盘或内存)，可以是“DISK”或“MEMORY”。
- “TABLESPACE ... STORAGE DISK”将表分配给 NDB 群集磁盘数据表空间。该表空间必须已经使用“CREATE TABLESPACE”创建。
- 没有 TABLESPACE 子句，则不能在 CREATE TABLE 语句中使用 STORAGE 子句。
UNION：
【仅适用于 MERGE 表】用于访问一个相同的 MyISAM 表的集合。
- 映射到 MERGE 表的表必须具有 SELECT，UPDATE 和 DELETE 权限。

Table 分区

partition_options可用于控制用“CREATE TABLE”创建的 table 的分区。分区可以被修改、合并、添加到表中以及从表中删除。

以下“partition_options”语法的选项并非适用于所有分区类型。

PARTITION BY：
如果使用，则“partition_options”子句以“PARTITION BY”开头。此子句包含用于确定分区的函数；该函数返回一个整数值，范围是 1 到 num ，其中“num”是分区数。

一个 table 可能包含的用户定义的分区的最大数量为 1024（包括了子分区的数量）。
- PARTITION BY 子句中使用的表达式（“expr”）不能引用不在正在创建的 table 中的任何列。
HASH(expr)：
散列一个或多个列以创建用于放置和定位行的键。“expr”是使用一个或多个表列的表达式。这可以是产生单个整数值的任何有效 MySQL 表达式(包括 MySQL 函数)。

例如，这些都是使用 PARTITION BY HASH 的有效 CREATE TABLE 语句：
```
CREATE TABLE t1 (col1 INT, col2 CHAR(5))
    PARTITION BY HASH(col1);

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
    PARTITION BY HASH ( YEAR(col3) );
```
- 不得将“VALUES LESS THAN”或“VALUES IN”子句与“PARTITION BY HASH”一起使用。
- PARTITION BY HASH 使用“expr”的余数除以分区数(即模数)。
- “LINEAR”关键字需要一些不同的算法。在这种情况下，作为一个或多个逻辑AND运算的结果，计算存储行的分区的数量。【？】
KEY(column_list)：
类似于HASH，除了 MySQL 提供散列功能以保证均匀的数据分布。“column_list”参数只是一个 1 或多个 table 列的列表(最多 16 个)。

此示例显示了一个简单的 table，该 table 按键进行分区，并具有 4 个分区：
```
CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY KEY(col3)
    PARTITIONS 4;
```
- 对于按键分区的 table，可以通过使用“LINEAR”关键字来采用线性分区。这与由“HASH”分区的 table 具有相同的效果。也就是说，使用“&”运算符而不是模数来找到分区号。
- “[SUB]PARTITION BY [LINEAR] KEY”支持“ALGORITHM={1 | 2}”选项： “ALGORITHM=1”使服务器使用与 MySQL 5.1 相同的键哈希函数； “ALGORITHM=2”表示服务器采用默认在 MySQL 5.5 及更高版本中为新的KEY分区表实现和使用的键哈希函数。
- 不得将“VALUES LESS THAN”或“VALUES IN”子句与“PARTITION BY HASH”一起使用。

RANGE(expr)：

在这种情况下，“expr”使用一组“VALUES LESS THAN”运算符显示值的范围。使用范围分区时，必须使用“VALUES LESS THAN”定义至少一个分区。您不能将“VALUES IN”与范围分区一起使用。

示例：希望根据以下方案在包含年份值的列上进行分区

Partition Number:	Years Range:
     0	     1990 及更早版本
     1	     1991 至 1994
     2	     1995 至 1998 年
     3	     1999 至 2002
     4	     2003 至 2005 年
     5	     2006 及更高版本

可以通过此处显示的CREATE TABLE语句来实现实现这种分区方案的表：

CREATE TABLE t1 (
    year_col  INT,
    some_data INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2002),
    PARTITION p4 VALUES LESS THAN (2006),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

“PARTITION ... VALUES LESS THAN ...”语句以连续的方式工作。 “VALUES LESS THAN MAXVALUE”用于指定“剩余”值，该值大于否则指定的最大值。

RANGE COLUMNS(column_list)：
RANGE上的此变体有助于使用多个列上的范围条件（即具有诸如“WHERE a = 1 AND b < 10”或“WHERE a = 1 AND b = 10 AND c < 10”之类的条件）对查询进行分区修剪。

示例：
```
CREATE TABLE rc (
    a INT NOT NULL,
    b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (10,5),
    PARTITION p1 VALUES LESS THAN (20,10),
    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
```
- “column_list”和“value_list”中可以引用的最大列数为 16。
- COLUMNS 子句中使用的“column_list”只能包含列名；列表中的每一列必须是以下 MySQL 数据类型之一：整数类型；字符串类型；时间或日期列类型。
  不允许使用 BLOB，TEXT，SET，ENUM，BIT 或空间数据类型的列；也不允许使用浮点数类型的列。也不得在COLUMNS子句中使用函数或算术表达式。
- 分区定义中使用的“VALUES LESS THAN”子句必须为出现在 COLUMNS 子句中的每一列指定 Literals 值，且“VALUES LESS THAN”中不能使用NULL；对于非第一列的给定列，可以多次使用 MAXVALUE。
LIST(expr)：
当基于具有有限的可能值集（例如 State 或国家/locale 代码）的 table 列分配分区时，此功能很有用。

在这种情况下，可以将与某个 State 或国家/locale 有关的所有行分配给单个分区，或者可以为某个 State 或国家/locale 的某个组保留一个分区。它类似于“RANGE”，不同之处在于仅“VALUES IN”可以用于指定每个分区的允许值。

示例：
```
CREATE TABLE client_firms (
    id   INT,
    name VARCHAR(35)
)
PARTITION BY LIST (id) (
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);
```
- VALUES IN 与要匹配的值列表一起使用。使用列表分区时，必须使用 VALUES IN 定义至少一个分区。
- 不能将“VALUES LESS THAN”与“PARTITION BY LIST”结合使用。
LIST COLUMNS(column_list)：
LIST 上的此变体使用多个列上的比较条件（即具有“WHERE a = 5 AND b = 5”或“WHERE a = 1 AND b = 10 AND c = 5”之类的条件）来简化查询的分区修剪。

示例：
```
CREATE TABLE lc (
    a INT NULL,
    b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
);
```
- 【关于“LIST COLUMNS(column_list)”中使用列和“VALUES IN(value_list)”中使用值的数据类型的规则，分别与“RANGE COLUMNS(column_list)”和“VALUES LESS THAN(value_list)”中使用列的数据类型相同】VALUES IN 子句不允许“MAXVALUE”，但可以使用 NULL。
- “column_list”和组成“value_list”的元素中可以使用的最大列数为 16。
PARTITIONS num：
可以选择使用 PARTITIONS num 子句指定分区的数量，其中“num”是分区的数量。如果此子句和*都使用了任何PARTITION子句，则num *必须等于使用PARTITION子句声明的任何分区的总数。
- 如果同时使用此子句和任何“PARTITION”子句，则“num”必须等于使用“PARTITION”子句声明的任何分区的总数。
- 在创建被 RANGE 或 LIST 分区的 table 时，无论是否使用 PARTITIONS 子句，都必须在 table 定义中至少包含一个 PARTITION VALUES 子句。【？】
SUBPARTITION BY：
分区可以可选地划分为多个子分区，以通过使用可选的 SUBPARTITION BY 子句来指示。可以通过 HASH 或 KEY 进行分区。这些都可以是 LINEAR。这些工作方式与先前针对等效分区类型所述的方式相同。

（无法通过 LIST 或 RANGE 进行子分区）
- 可以使用 SUBPARTITIONS 关键字后跟一个整数值来指示子分区的数量。
- 严格检查 PARTITIONS 或 SUBPARTITIONS 子句中使用的值，并且该值必须遵守以下规则：
  - 该值必须是一个非零的正整数。
  - 不允许前导零。
  - 该值必须是整数 Literals，并且不能为表达式。

partition_definition：每个分区可以使用“partition_definition”子句单独定义。组成此子句的各个部分如下：

PARTITION partition_name：
指定分区的逻辑名。
VALUES：
对于范围分区，每个分区必须包含“VALUES LESS THAN”子句；对于列 table 分区，必须为每个分区指定一个“VALUES IN”子句。这用于确定哪些行要存储在此分区中。
[STORAGE] ENGINE：
分区处理程序为 PARTITION 和 SUBPARTITION 接受 [STORAGE] ENGINE 选项。
- 当前，唯一可以使用此方法的方法是将所有分区或所有子分区设置为同一存储引擎。（为同一表中的分区或子分区设置不同的存储引擎将产生错误）
COMMENT：
用于指定描述分区的字符串。
- 分区 Comments 的最大长度为 1024 个字符。
DATA DIRECTORY和INDEX DIRECTORY：
DATA DIRECTORY 和 INDEX DIRECTORY 可用于指示目录，该分区的数据和索引将分别存储在该目录中。 data_dir 和 index_dir 都必须是绝对系统路径名。
- 从 MySQL 5.7.17 开始，您必须具有 FILE 权限才能使用 DATA DIRECTORY 或 INDEX DIRECTORY 分区选项。
- 每个分区可以指定一个数据目录和一个索引目录。如果未指定，则默认情况下，数据和索引存储在 table 的数据库目录中。
- 在 Windows 上，MyISAM 表的单个分区或子分区不支持 DATA DIRECTORY 和 INDEX DIRECTORY 选项，而 InnoDB 表的单个分区或子分区不支持 INDEX DIRECTORY 选项。
MAX_ROWS和MIN_ROWS：
分别用来指定要存储在分区中的最大和最小行数。
- max_number_of_rows 和 min_number_of_rows 的值必须为正整数。
TABLESPACE：
用于为分区指定表空间。受 NDB 群集支持。对于 InnoDB 表，可以通过指定 TABLESPACE 来为分区指定每个表文件表空间。
- 所有分区必须属于同一存储引擎。
- MySQL 5.7.24 中弃用了将 InnoDB 表分区放置在共享 InnoDB 表空间中的支持，并且在将来的 MySQL 版本中将不再支持。共享表空间包括 InnoDB系统表空间和常规表空间。
subpartition_definition：

subpartition_definition：分区定义可以选择包含一个或多个 subpartition_definition 子句。每一个都至少由“SUBPARTITION name”组成，其中 name 是该子分区的标识符。除了用 SUBPARTITION 替换 PARTITION 关键字之外，子分区定义的语法与分区定义的语法相同。

按生成的列进行分区：允许按生成的列进行分区。例如：

CREATE TABLE t1 (
  s1 INT,
  s2 INT AS (EXP(s1)) STORED
)
PARTITION BY LIST (s2) (
  PARTITION p1 VALUES IN (1)
);

分区会将生成的列视为常规列，从而可以通过变通方法来限制分区所不允许的功能。如上：EXP() 不能直接在 PARTITION BY 子句中使用，但是允许使用 EXP() 定义生成的列。【？？？】

由 CREATE TABLE 创建的文件

CREATE TEMPORARY TABLE 语句

CREATE TABLE ... LIKE 语句

CREATE TABLE ... SELECT 语句

外键约束

静默柱规格变更

CREATE TABLE 和 Generated Columns

二级索引和生成的列

设置 NDB_TABLE 选项