MySQL8-中文参考-五十七-

布客飞龙
54 阅读1小时+

MySQL8 中文参考（五十七）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

15.1.20 CREATE TABLE 语句

原文：dev.mysql.com/doc/refman/8.0/en/create-table.html

15.1.20.1 CREATE TABLE 创建的文件

15.1.20.2 CREATE TEMPORARY TABLE 语句

15.1.20.3 CREATE TABLE ... LIKE 语句

15.1.20.4 CREATE TABLE ... SELECT 语句

15.1.20.5 外键约束

15.1.20.6 CHECK 约束

15.1.20.7 隐式列规范更改

15.1.20.8 CREATE TABLE 和生成列

15.1.20.9 二级索引和生成列

15.1.20.10 隐式列

15.1.20.11 生成的隐式主键

15.1.20.12 设置 NDB 注释选项

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_constraint_definition*
}

*column_definition*: {
    *data_type* [NOT NULL | NULL] [DEFAULT {*literal* | (*expr*)} ]
      [VISIBLE | INVISIBLE]
      [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]
      [VISIBLE | INVISIBLE]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT '*string*']
      [*reference_definition*]
      [*check_constraint_definition*]
}

*data_type*:
    (see Chapter 13, Data Types)

*key_part*: {*col_name* [(*length*)] | (*expr*)} [ASC | DESC]

*index_type*:
    USING {BTREE | HASH}

*index_option*: {
    KEY_BLOCK_SIZE [=] *value*
  | *index_type*
  | WITH PARSER *parser_name*
  | COMMENT '*string*'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] '*string*'
  |SECONDARY_ENGINE_ATTRIBUTE [=] '*string*'
}

*check_constraint_definition*:
    [CONSTRAINT [*symbol*]] CHECK (*expr*) [[NOT] ENFORCED]

*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*: {
    AUTOEXTEND_SIZE [=] *value*
  | 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}
  | START TRANSACTION 
  | SECONDARY_ENGINE_ATTRIBUTE [=] '*string*'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] *value*
  | *tablespace_option*
  | 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]

*tablespace_option*:
    TABLESPACE *tablespace_name* [STORAGE DISK]
  | [TABLESPACE *tablespace_name*] STORAGE MEMORY

*query_expression:*
    SELECT ...   (*Some valid select or union statement*)

CREATE TABLE 创建具有给定名称的表。您必须对表具有 CREATE 权限。

默认情况下，表在默认数据库中使用 InnoDB 存储引擎创建。如果表已存在、没有默认数据库或数据库不存在，则会出现错误。

MySQL 对表的数量没有限制。底层文件系统可能对代表表的文件数量有限制。各个存储引擎可能会施加特定于引擎的约束。InnoDB 允许最多 40 亿个表。

有关表的物理表示信息，请参阅 第 15.1.20.1 节“CREATE TABLE 创建的文件”.

CREATE TABLE 语句有几个方面，在本节的以下主题中描述：

  • 表名

  • 临时表

  • 表克隆和复制

  • 列数据类型和属性

  • 索引、外键和 CHECK 约束

  • 表选项

  • 表分区

表名

  • *tbl_name*

    表名可以指定为*db_name.tbl_name*，以在特定数据库中创建表。无论是否存在默认数据库，都可以使用此方法，假设数据库存在。如果使用带引号的标识符，请分别引用数据库和表名。例如，写成mydb`.`mytbl，而不是mydb.mytbl。

    可接受的表名规则在第 11.2 节，“模式对象名称”中给出。

  • IF NOT EXISTS

    如果表存在，则防止出现错误。但是，并没有验证现有表的结构是否与 CREATE TABLE 语句指示的结构完全相同。

临时表

在创建表时，可以使用 TEMPORARY 关键字。TEMPORARY 表仅在当前会话中可见，并在会话关闭时自动删除。有关更多信息，请参见第 15.1.20.2 节，“CREATE TEMPORARY TABLE 语句”。

表克隆和复制

  • LIKE

    使用 CREATE TABLE ... LIKE 根据另一个表的定义创建一个空表，包括原始表中定义的任何列属性和索引：

    CREATE TABLE *new_tbl* LIKE *orig_tbl*;

    更多信息，请参见第 15.1.20.3 节，“CREATE TABLE ... LIKE 语句”。

  • [AS] *query_expression*

    要从一个表创建另一个表，请在 CREATE TABLE 语句末尾添加一个 SELECT 语句：

    CREATE TABLE *new_tbl* AS SELECT * FROM *orig_tbl*;

    更多信息，请参见第 15.1.20.4 节，“CREATE TABLE ... SELECT 语句”。

  • IGNORE | REPLACE

    IGNORE 和 REPLACE 选项指示在使用 SELECT 语句复制表时如何处理重复唯一键值的行。

    更多信息，请参见第 15.1.20.4 节，“CREATE TABLE ... SELECT 语句”。

列数据类型和属性

每个表的列有一个硬限制为 4096 列，但对于给定表，有效最大值可能会更少，并取决于第 10.4.7 节，“表列计数和行大小限制”中讨论的因素。

  • *data_type*

    data_type 代表列定义中的数据类型。有关指定列数据类型的语法以及每种类型属性的详细描述，请参见第十三章，数据类型。

    • 一些属性不适用于所有数据类型。AUTO_INCREMENT 仅适用于整数和浮点类型。在 MySQL 8.0.17 中，使用 AUTO_INCREMENT 与 FLOAT 或 DOUBLE 列已被弃用；预计在未来的 MySQL 版本中将删除对其的支持。

      在 MySQL 8.0.13 之前，DEFAULT 不适用于 BLOB, TEXT, GEOMETRY 和 JSON 类型。

    • 字符数据类型（CHAR, VARCHAR, TEXT, ENUM, SET 和任何同义词）可以包括 CHARACTER SET 来指定列的字符集。CHARSET 是 CHARACTER SET 的同义词。可以使用 COLLATE 属性指定字符集的排序规则，以及其他任何属性。有关详细信息，请参见 第十二章，字符集、排序规则、Unicode。示例：

      CREATE TABLE t (c CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

      MySQL 8.0 解释字符列定义中的长度规范为字符。BINARY 和 VARBINARY 的长度以字节为单位。

    • 对于 CHAR、VARCHAR、BINARY 和 VARBINARY 列，可以创建仅使用列值前导部分的索引，使用 *col_name*(*length*) 语法指定索引前缀长度。BLOB 和 TEXT 列也可以被索引，但必须给出前缀长度。对于非二进制字符串类型，前缀长度以字符为单位，对于二进制字符串类型，前缀长度以字节为单位。也就是说，对于 CHAR、VARCHAR 和 TEXT 列，索引条目由每个列值的前 length 个字符组成，对于 BINARY、VARBINARY 和 BLOB 列，索引条目由每个列值的前 length 个字节组成。像这样仅对列值前缀进行索引可以使索引文件变得更小。有关索引前缀的更多信息，请参见 第 15.1.15 节，“CREATE INDEX 语句”。

      仅 InnoDB 和 MyISAM 存储引擎支持对 BLOB 和 TEXT 列进行索引。例如：

      CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

      如果指定的索引前缀超过最大列数据类型大小，CREATE TABLE 将处理索引如下：

      • 对于非唯一索引，如果启用了严格的 SQL 模式，则会发生错误，或者索引长度会减小以符合最大列数据类型大小，并产生警告（如果未启用严格的 SQL 模��）。

      • 对于唯一索引，无论 SQL 模式如何，都会发生错误，因为减少索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。

    • JSON 列无法被索引。您可以通过在生成的列上创建索引来绕过此限制，该生成的列从 JSON 列中提取标量值。详细示例请参见 在生成的列上创建索引以提供 JSON 列索引。

  • NOT NULL | NULL

    如果未指定 NULL 或 NOT NULL，则该列被视为已指定 NULL。

    在 MySQL 8.0 中，只有InnoDB、MyISAM和MEMORY存储引擎支持可以具有NULL值的列上的索引。在其他情况下，必须将索引列声明为NOT NULL，否则会产生错误。

  • DEFAULT

    为列指定默认值。有关默认值处理的更多信息，包括列定义不包含显式DEFAULT值的情况，请参见第 13.6 节，“数据类型默认值”。

    如果启用了NO_ZERO_DATE或NO_ZERO_IN_DATE SQL 模式，并且日期值默认值不符合该模式，则CREATE TABLE在严格 SQL 模式未启用时会产生警告，在启用严格模式时会产生错误。例如，启用NO_ZERO_IN_DATE，c1 DATE DEFAULT '2010-00-00'会产生警告。

  • VISIBLE、INVISIBLE

    指定列的可见性。如果两个关键字都不存在，则默认为VISIBLE。表必须至少有一列可见。尝试使所有列不可见会产生错误。有关更多信息，请参见第 15.1.20.10 节，“不可见列”。

    VISIBLE 和 INVISIBLE 关键字从 MySQL 8.0.23 开始可用。在 MySQL 8.0.23 之前，所有列都是可见的。

  • AUTO_INCREMENT

    整数或浮点列可以具有附加属性AUTO_INCREMENT。当您将NULL（推荐）或0值插入到索引的AUTO_INCREMENT列中时，该列将设置为下一个序列值。通常这是*value*+1，其中*value*是当前表中列的最大值。AUTO_INCREMENT序列从1开始。

    在插入行后检索AUTO_INCREMENT值，请使用LAST_INSERT_ID() SQL 函数或mysql_insert_id() C API 函数。请参见第 14.15 节，“信息函数”，以及 mysql_insert_id()。

    如果启用了NO_AUTO_VALUE_ON_ZERO SQL 模式，则可以将0存储在AUTO_INCREMENT列中，而不生成新的序列值。参见第 7.1.11 节，“服务器 SQL 模式”。

    每个表只能有一个AUTO_INCREMENT列，它必须被索引，并且不能有默认值。AUTO_INCREMENT列只有在包含正值时才能正常工作。插入负数被视为插入一个非常大的正数。这样做是为了避免当数字从正数“环绕”到负数时出现精度问题，并确保您不会意外地获得一个包含0的AUTO_INCREMENT列。

    对于MyISAM表，您可以在多列键中指定一个AUTO_INCREMENT次要列。参见 Section 5.6.9, “Using AUTO_INCREMENT”。

    要使 MySQL 与某些 ODBC 应用程序兼容，您可以使用以下查询找到最后插入行的AUTO_INCREMENT值：

    SELECT * FROM *tbl_name* WHERE *auto_col* IS NULL

    此方法要求sql_auto_is_null变量未设置为 0。请参见 Section 7.1.8, “Server System Variables”。

    有关InnoDB和AUTO_INCREMENT的信息，请参见 Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”。有关AUTO_INCREMENT和 MySQL 复制的信息，请参见 Section 19.5.1.1, “Replication and AUTO_INCREMENT”。

  • COMMENT

    可以使用COMMENT选项为列指定长达 1024 个字符的注释。该注释将显示在SHOW CREATE TABLE和SHOW FULL COLUMNS语句中。它还显示在信息模式COLUMNS表的COLUMN_COMMENT列中。

  • COLUMN_FORMAT

    在 NDB Cluster 中，还可以使用COLUMN_FORMAT为NDB表的各个列指定数据存储格式。可接受的列格式包括FIXED、DYNAMIC和DEFAULT。FIXED用于指定固定宽度存储，DYNAMIC允许列为可变宽度，DEFAULT使列使用由列的数据类型确定的固定宽度或可变宽度存储（可能被ROW_FORMAT修饰符覆盖）。

    对于NDB表，COLUMN_FORMAT的默认值为FIXED。

    在 NDB Cluster 中，使用COLUMN_FORMAT=FIXED定义的列的最大可能偏移量为 8188 字节。有关更多信息和可能的解决方法，请参见 Section 25.2.7.5, “Limits Associated with Database Objects in NDB Cluster”。

    COLUMN_FORMAT 目前对使用除 NDB 之外的存储引擎的表的列没有影响。MySQL 8.0 会默默忽略 COLUMN_FORMAT。

  • ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 选项（自 MySQL 8.0.21 起可用）用于指定主存储引擎和辅助存储引擎的列属性。这些选项保留供将来使用。

    允许的值是包含有效 JSON 文档或空字符串（''）的字符串文字。无效的 JSON 将被拒绝。

    CREATE TABLE t1 (c1 INT ENGINE_ATTRIBUTE='{"*key*":"*value*"}');

    ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 的值可以重复使用而不会出错。在这种情况下，将使用最后指定的值。

    ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 的值不会被服务器检查，也不会在表的存储引擎更改时被清除。

  • 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 之外的存储引擎的表没有影响。STORAGE 关键字仅在随 NDB Cluster 一起提供的 mysqld 构建中受支持；在 MySQL 的任何其他版本中都不被识别，任何尝试使用 STORAGE 关键字都会导致语法错误。

  • GENERATED ALWAYS

    用于指定生成列表达式。有关生成列的信息，请参阅第 15.1.20.8 节，“CREATE TABLE 和 Generated Columns”。

    存储生成列 可以被索引。InnoDB 支持对虚拟生成列进行二级索引。请参阅第 15.1.20.9 节，“Secondary Indexes and Generated Columns”。

索引、外键和检查约束

创建索引、外键和CHECK约束时适用几个关键字。除了以下描述外，有关一般背景，请参阅 Section 15.1.15, “CREATE INDEX Statement”，Section 15.1.20.5, “FOREIGN KEY Constraints”和 Section 15.1.20.6, “CHECK Constraints”。

  • CONSTRAINT *symbol*

    可以使用CONSTRAINT *symbol*子句来命名约束。如果未给出该子句，或者在CONSTRAINT关键字后未包含*symbol，MySQL 会自动生成约束名称，以下列出的情况除外。如果使用了symbol值，必须对每个模式（数据库）的每种约束类型保持唯一性。重复的symbol*会导致错误。另请参阅有关生成约束标识符长度限制的讨论，详见 Section 11.2.1, “Identifier Length Limits”。

    注意

    如果在外键定义中未给出CONSTRAINT *symbol*子句，或者在CONSTRAINT关键字后未包含*symbol*，MySQL 在 MySQL 8.0.15 之前使用外键索引名称，并在此后自动生成约束名称。

    SQL 标准规定所有类型的约束（主键、唯一索引、外键、检查）属于同一命名空间。在 MySQL 中，每种约束类型在每个模式中都有自己的命名空间。因此，每种约束类型的名称必须在每个模式中保持唯一，但不同类型的约束可以具有相同的名称。

  • PRIMARY KEY

    唯一索引，所有关键列必须定义为NOT NULL。如果它们没有明确声明为NOT NULL，MySQL 会隐式（并悄无声息地）声明它们。一个表只能有一个PRIMARY KEY。PRIMARY KEY的名称始终为PRIMARY，因此不能用作任何其他类型索引的名称。

    如果没有PRIMARY KEY，应用程序要求表中的PRIMARY KEY，MySQL 将第一个没有NULL列的UNIQUE索引作为PRIMARY KEY返回。

    在InnoDB表中，保持PRIMARY KEY简短，以减少次要索引的存储开销。每个次要索引条目都包含相应行的主键列的副本。（参见 Section 17.6.2.1, “Clustered and Secondary Indexes”。）

    在创建的表中，首先放置PRIMARY KEY，然后是所有UNIQUE索引，然后是非唯一索引。这有助于 MySQL 优化器优先考虑使用哪个索引，并更快地检测重复的UNIQUE键。

    PRIMARY KEY可以是多列索引。但是，您不能在列规范中使用PRIMARY KEY关键属性创建多列索引。这样做只会将该单列标记为主键。您必须使用单独的PRIMARY KEY(*key_part*, ...)子句。

    如果表具有由整数类型组成的单列PRIMARY KEY或UNIQUE NOT NULL索引，您可以在SELECT语句中使用_rowid来引用索引列，如 Unique Indexes 中所述。

    在 MySQL 中，PRIMARY KEY的名称是PRIMARY。对于其他索引，如果您没有分配名称，则该索引将被分配与第一个索引列相同的名称，并带有可选后缀（_2，_3，...）以使其唯一。您可以使用SHOW INDEX FROM *tbl_name*查看表的索引名称。参见 Section 15.7.7.22, “SHOW INDEX Statement”。

  • KEY | INDEX

    KEY通常是INDEX的同义词。在列定义中给出时，PRIMARY KEY关键属性也可以简单地指定为KEY。这是为了与其他数据库系统兼容而实现的。

  • UNIQUE

    UNIQUE索引创建一个约束，使索引中的所有值必须是不同的。如果尝试添加具有与现有行匹配的键值的新行，则会发生错误。对于所有引擎，UNIQUE索引允许对可以包含NULL的列进行多个NULL值。如果为UNIQUE索引的列指定前缀值，则列值必须在前缀长度内是唯一的。

    如果表具有由整数类型组成的单列PRIMARY KEY或UNIQUE NOT NULL索引，您可以在SELECT语句中使用_rowid来引用索引列，如 Unique Indexes 中所述。

  • FULLTEXT

    FULLTEXT索引是用于全文搜索的特殊类型的索引。只有InnoDB和MyISAM存储引擎支持FULLTEXT索引。它们只能从CHAR、VARCHAR和TEXT列创建。索引始终在整个列上进行；不支持列前缀索引，如果指定了任何前缀长度，则会被忽略。有关操作的详细信息，请参见第 14.9 节，“全文搜索函数”。可以指定WITH PARSER子句作为*index_option*值，以将解析器插件与索引关联，如果全文索引和搜索操作需要特殊处理。此子句仅适用于FULLTEXT索引。InnoDB和MyISAM支持全文解析器插件。有关更多信息，请参见全文解析器插件和编写全文解析器插件。

  • 空间

    您可以在空间数据类型上创建SPATIAL索引。空间类型仅支持InnoDB和MyISAM表，并且索引列必须声明为NOT NULL。请参见第 13.4 节，“空间数据类型”。

  • 外键

    MySQL 支持外键，允许您在表之间交叉引用相关数据，并支持外键约束，有助于保持这些分散数据的一致性。有关定义和选项信息，请参见*reference_definition，以及reference_option*。

    使用InnoDB存储引擎的分区表不支持外键。有关更多信息，请参见第 26.6 节，“分区的限制和限制”。

  • CHECK

    CHECK子句使得可以创建用于检查表行中数据值的约束。请参见第 15.1.20.6 节，“CHECK 约束”。

  • *key_part*

    • *key_part*规范可以以ASC或DESC结尾，以指定索引值是按升序还是降序存储。如果没有给出顺序说明符，则默认为升序。

    • 前缀，由*length*属性定义，对于使用REDUNDANT或COMPACT行格式的InnoDB表最多可以达到 767 字节长。对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，前缀长度限制为 3072 字节。对于MyISAM表，前缀长度限制为 1000 字节。

      前缀限制以字节为单位。但是，在CREATE TABLE、ALTER TABLE和CREATE INDEX语句中的索引规范中，对于非二进制字符串类型（CHAR、VARCHAR、TEXT），前缀长度被解释为字符数，对于二进制字符串类型（BINARY、VARBINARY、BLOB），前缀长度被解释为字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑这一点。

    • 从 MySQL 8.0.17 开始，*key_part规范的expr*可以采用形式(CAST *json_path* AS *type* ARRAY)，以在JSON列上创建多值索引。多值索引提供了有关创建、使用以及多值索引的限制和限制的详细信息。

  • *index_type*

    一些存储引擎允许在创建索引时指定索引类型。*index_type*指定符的语法是USING *type_name*。

    例子：

    CREATE TABLE lookup
  (id INT, INDEX USING BTREE (id))
  ENGINE = MEMORY;

    USING的首选位置是在索引列列表之后。它可以在列列表之前给出，但是在该位置使用该选项的支持已被弃用，您应该期望在未来的 MySQL 版本中将其移除。

  • *index_option*

    *index_option*值指定索引的附加选项。

    • KEY_BLOCK_SIZE

      对于MyISAM表，KEY_BLOCK_SIZE可选地指定用于索引键块的字节大小。该值被视为提示；如果需要，可以使用不同的大小。为单个索引定义指定的KEY_BLOCK_SIZE值会覆盖表级别的KEY_BLOCK_SIZE值。

      有关表级别KEY_BLOCK_SIZE属性的信息，请参阅表选项。

    • WITH PARSER

      WITH PARSER选项只能与FULLTEXT索引一起使用。如果全文索引和搜索操作需要特殊处理，则将解析器插件与索引关联起来。InnoDB和MyISAM支持全文解析器插件。如果您有一个带有关联全文解析器插件的MyISAM表，您可以使用ALTER TABLE将表转换为InnoDB。

    • COMMENT

      索引定义可以包括最多 1024 个字符的可选注释。

      你可以使用*index_option* COMMENT子句为单个索引设置InnoDB MERGE_THRESHOLD值。参见第 17.8.11 节，“配置索引页合并阈值”。

    • VISIBLE，INVISIBLE

      指定索引可见性。索引默认可见。不可见索引不会被优化器使用。索引可见性的规范适用于主键之外的索引（显式或隐式）。更多信息，请参见第 10.3.12 节，“不可见索引”。

    • ENGINE_ATTRIBUTE和SECONDARY_ENGINE_ATTRIBUTE选项（自 MySQL 8.0.21 起可用）用于指定主要和次要存储引擎的索引属性。这些选项保留供将来使用。

    有关可接受的*index_option*值的更多信息，请参见第 15.1.15 节，“CREATE INDEX 语句”。有关索引的更多信息，请参见第 10.3.1 节，“MySQL 如何使用索引”。

  • *reference_definition*

    有关*reference_definition*语法详细信息和示例，请参见第 15.1.20.5 节，“外键约束”。

    InnoDB和NDB表支持检查外键约束。引用表的列必须始终明确命名。外键支持ON DELETE和ON UPDATE操作。有关更详细的信息和示例，请参见第 15.1.20.5 节，“外键约束”。

    对于其他存储引擎，MySQL 服务器会解析并忽略CREATE TABLE语句中的FOREIGN KEY语法。

    重要

    对于熟悉 ANSI/ISO SQL 标准的用户，请注意，包括InnoDB在内的任何存储引擎都不识别或强制执行用于参照完整性约束定义的MATCH子句。明确指定MATCH子句不会产生指定的效果，并且还会导致ON DELETE和ON UPDATE子句被忽略。因此，应避免指定MATCH。

    SQL 标准中的MATCH子句控制如何处理复合（多列）外键中的NULL值与主键进行比较。InnoDB基本上实现了MATCH SIMPLE定义的语义，允许外键全部或部分为NULL。在这种情况下，包含这种外键的（子表）行允许被插入，并且不匹配参考（父表）中的任何行。可以使用触发器实现其他语义。

    此外，MySQL 要求被引用的列需要建立索引以提高性能。然而，InnoDB不强制要求被引用的列声明为UNIQUE或NOT NULL。对于引用非唯一键或包含NULL值的键的外键引用的处理在UPDATE或DELETE CASCADE等操作中没有明确定义。建议使用只引用既UNIQUE（或PRIMARY）又NOT NULL键的外键。

    MySQL 解析但忽略“内联REFERENCES规范”（如 SQL 标准中定义的）中的引用，其中引用被定义为列规范的一部分。只有在作为单独的FOREIGN KEY规范的一部分指定时，MySQL 才接受REFERENCES子句。有关更多信息，请参见 Section 1.6.2.3, “FOREIGN KEY Constraint Differences”。

  • *reference_option*

    有关RESTRICT、CASCADE、SET NULL、NO ACTION和SET DEFAULT选项的信息，请参见 Section 15.1.20.5, “FOREIGN KEY Constraints”。

表选项

表选项用于优化表的行为。在大多数情况下，您不必指定任何选项。除非另有说明，这些选项适用于所有存储引擎。不适用于给定存储引擎的选项可能会被接受并记入表定义中。如果以后使用ALTER TABLE将表转换为使用不同的存储引擎，则这些选项将适用。

  • ENGINE

    指定表的存储引擎，可以使用以下表中显示的名称之一。引擎名称可以是带引号或不带引号的。带引号的名称'DEFAULT'会被识别但会被忽略。

    存储引擎描述
    InnoDB具有行锁定和外键的事务安全表。新表的默认存储引擎。参见第十七章，InnoDB 存储引擎，特别是如果您有 MySQL 经验但是对InnoDB不熟悉，请参见第 17.1 节，“InnoDB 简介”。
    MyISAM主要用于只读或读取频繁工作负载的二进制便携式存储引擎。参见第 18.2 节，“MyISAM 存储引擎”。
    MEMORY这种存储引擎的数据仅存储在内存中。参见第 18.3 节，“MEMORY 存储引擎”。
    CSV以逗号分隔值格式存储行的表。参见第 18.4 节，“CSV 存储引擎”。
    ARCHIVE存档存储引擎。参见第 18.5 节，“ARCHIVE 存储引擎”。
    EXAMPLE一个示例引擎。参见第 18.9 节，“EXAMPLE 存储引擎”。
    FEDERATED访问远程表的存储引擎。参见第 18.8 节，“FEDERATED 存储引擎”。
    HEAP这是MEMORY的同义词。
    MERGE一组作为一个表使用的MyISAM表。也称为MRG_MyISAM。参见第 18.7 节，“MERGE 存储引擎”。
    NDB集群化、容错、基于内存的表，支持事务和外键。也称为NDBCLUSTER。参见第二十五章，MySQL NDB Cluster 8.0。
    存储引擎描述

    默认情况下，如果指定了不可用的存储引擎，语句将失败并显示错误。您可以通过从服务器 SQL 模式中删除NO_ENGINE_SUBSTITUTION来覆盖此行为，以便 MySQL 允许将指定的引擎替换为默认存储引擎。通常在这种情况下，默认值为default_storage_engine系统变量的InnoDB。当禁用NO_ENGINE_SUBSTITUTION时，如果未遵守存储引擎规范，则会发出警告。

  • AUTOEXTEND_SIZE

    定义InnoDB在表空间满时扩展的量。在 MySQL 8.0.23 中引入。设置必须是 4MB 的倍数。默认设置为 0，这将导致表空间根据隐式默认行为进行扩展。有关更多信息，请参见第 17.6.3.9 节，“表空间 AUTOEXTEND_SIZE 配置”。

  • AUTO_INCREMENT

    表的初始AUTO_INCREMENT值。在 MySQL 8.0 中，这适用于MyISAM、MEMORY、InnoDB和ARCHIVE表。对于不支持AUTO_INCREMENT表选项的引擎，要设置第一个自动增量值，需要在创建表后插入一个值比所需值小 1 的“虚拟”行，然后删除虚拟行。

    对于支持在CREATE TABLE语句中使用AUTO_INCREMENT表选项的引擎，您也可以使用ALTER TABLE *tbl_name* AUTO_INCREMENT = *N*来重置AUTO_INCREMENT值。该值不能低于当前列中的最大值。

  • AVG_ROW_LENGTH

    表的平均行长度的近似值。只需为具有可变大小行的大表设置此值。

    当您创建一个MyISAM表时，MySQL 使用MAX_ROWS和AVG_ROW_LENGTH选项的乘积来决定生成的表有多大。如果您没有指定任何选项，MyISAM数据和索引文件的最大大小默认为 256TB。（如果您的操作系统不支持那么大的文件，表大小将受文件大小限制。）如果您想要减小指针大小以使索引更小更快，并且您实际上不需要大文件，您可以通过设置myisam_data_pointer_size系统变量来减小默认指针大小。（参见第 7.1.8 节，“服务器系统变量”。）如果您希望所有表都能超过默认限制增长，并且愿意让表比必要的稍慢和稍大，您可以通过设置此变量来增加默认指针大小。将值设置为 7 允许表大小达到 65,536TB。

  • [DEFAULT] 字符集

    为表指定默认字符集。CHARSET是CHARACTER SET的同义词。如果字符集名称为DEFAULT，则使用数据库字符集。

  • 校验和

    如果您希望 MySQL 为所有行维护一个实时校验和（即 MySQL 在表更改时自动更新的校验和），请将此设置为 1。这使得表更新稍慢，但也更容易找到损坏的表。CHECKSUM TABLE语句报告校验和。（仅适用于MyISAM。）

  • [DEFAULT] 校对规则

    为表指定默认排序规则。

  • 注释

    表的注释，最长可达 2048 个字符。

    您可以使用 *table_option* COMMENT 子句为表设置 InnoDB MERGE_THRESHOLD 值。请参阅 Section 17.8.11, “Configuring the Merge Threshold for Index Pages”。

    设置 NDB_TABLE 选项。 在创建 NDB 表的 CREATE TABLE 或修改一个的 ALTER TABLE 语句中，表注释也可用于指定 NDB_TABLE 选项 NOLOGGING、READ_BACKUP、PARTITION_BALANCE 或 FULLY_REPLICATED 中的一个到四个作为一组名称-值对，如果需要，用逗号分隔，紧随以 NDB_TABLE= 开头的引用注释文本。以下是使用此语法的示例语句（强调文本）：

    CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 VARCHAR(100),
    c3 VARCHAR(100) )
ENGINE=NDB
*COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE"*;

    引号字符串内不允许有空格。字符串不区分大小写。

    该注释将显示在 SHOW CREATE TABLE 的输出中。注释的文本也作为 MySQL 信息模式 TABLES 表的 TABLE_COMMENT 列中可用。

    这种注释语法也适用于 NDB 表的 ALTER TABLE 语句。请注意，与 ALTER TABLE 一起使用的表注释会替换表先前可能具有的任何现有注释。

    不支持在表注释中设置 MERGE_THRESHOLD 选项用于 NDB 表（将被忽略）。

    有关完整的语法信息和示例，请参阅 Section 15.1.20.12, “Setting NDB Comment Options”。

  • COMPRESSION

    用于 InnoDB 表的页面级压缩的压缩算法。支持的值包括 Zlib、LZ4 和 None。COMPRESSION 属性是通过透明页面压缩功能引入的。页面压缩仅支持驻留在 file-per-table 表空间中的 InnoDB 表，并且仅在支持稀疏文件和孔打孔的 Linux 和 Windows 平台上可用。有关更多信息，请参阅 Section 17.9.2, “InnoDB Page Compression”。

  • CONNECTION

    FEDERATED 表的连接字符串。

    注意

    旧版本的 MySQL 使用 COMMENT 选项作为连接字符串的注释。

  • DATA DIRECTORY、INDEX DIRECTORY

    对于InnoDB，DATA DIRECTORY='*目录*'子句允许在数据目录之外创建表。必须启用innodb_file_per_table变量才能使用DATA DIRECTORY子句。必须指定完整的目录路径。截至 MySQL 8.0.21，指定的目录必须为InnoDB所知。有关更多信息，请参阅第 17.6.1.2 节，“外部创建表”。

    在创建MyISAM表时，可以使用DATA DIRECTORY='*目录*'子句，INDEX DIRECTORY='*目录*'子句，或两者。它们分别指定MyISAM表的数据文件和索引文件放置的位置。与InnoDB表不同，当使用DATA DIRECTORY或INDEX DIRECTORY选项创建MyISAM表时，MySQL 不会创建与数据库名称对应的子目录。文件将在指定的目录中创建。

    您必须具有FILE权限才能使用DATA DIRECTORY或INDEX DIRECTORY表选项。

    重要

    对于分区表，将忽略表级DATA DIRECTORY和INDEX DIRECTORY选项。（Bug #32091）

    仅当您未使用--skip-symbolic-links选项时，这些选项才有效。您的操作系统还必须具有可用的、线程安全的realpath()调用。有关更完整的信息，请参阅第 10.12.2.2 节，“在 Unix 上为 MyISAM 表使用符号链接”。

    如果使用没有DATA DIRECTORY选项创建MyISAM表，则.MYD文件将在数据库目录中创建。默认情况下，如果MyISAM在这种情况下找到现有的.MYD文件，则会覆盖它。对于使用没有INDEX DIRECTORY选项创建的表的.MYI文件也适用相同规则。要抑制此行为，请使用--keep_files_on_create选项启动服务器，在这种情况下，MyISAM不会覆盖现有文件，而是返回错误。

    如果使用DATA DIRECTORY或INDEX DIRECTORY选项创建MyISAM表，并且找到现有的.MYD或.MYI文件，MyISAM将始终返回错误，并且不会覆盖指定目录中的文件。

    重要

    您不能使用包含 MySQL 数据目录的路径名与DATA DIRECTORY或INDEX DIRECTORY一起使用。这包括分区表和单个表分区。（参见 Bug #32167。）

  • DELAY_KEY_WRITE

    如果您希望延迟表的键更新直到表关闭，请将此设置为 1。请参阅第 7.1.8 节，“服务器系统变量”中的delay_key_write系统变量的描述。（仅适用于MyISAM。）

  • ENCRYPTION

    ENCRYPTION子句用于启用或禁用InnoDB表的页面级数据加密。必须在启用加密之前安装和配置密钥环插件。在 MySQL 8.0.16 之前，只能在每个表的表空间中创建表时指定ENCRYPTION子句。从 MySQL 8.0.16 开始，也可以在通用表空间中创建表时指定ENCRYPTION子句。

    截至 MySQL 8.0.16，如果未指定ENCRYPTION子句，表将继承默认的模式加密。如果启用了table_encryption_privilege_check变量，则需要TABLE_ENCRYPTION_ADMIN权限才能创建具有与默认模式加密不同的ENCRYPTION子句设置的表。在通用表空间中创建表时，表和表空间加密必须匹配。

    从 MySQL 8.0.16 开始，在使用不支持加密的存储引擎时，不允许指定值为'N'或''以外的ENCRYPTION子句。以前，该子句被接受。

    有关更多信息，请参见第 17.13 节，“InnoDB 数据静态加密”。

  • ENGINE_ATTRIBUTE和SECONDARY_ENGINE_ATTRIBUTE选项（自 MySQL 8.0.21 起可用）用于指定主存储引擎和次要存储引擎的表属性。这些选项保留供将来使用。

    允许的值是包含有效JSON文档的字符串文字或空字符串（''）。无效的JSON会被拒绝。

    CREATE TABLE t1 (c1 INT) ENGINE_ATTRIBUTE='{"*key*":"*value*"}';

    ENGINE_ATTRIBUTE和SECONDARY_ENGINE_ATTRIBUTE值可以重复而不会出错。在这种情况下，将使用最后指定的值。

    服务器不会检查ENGINE_ATTRIBUTE和SECONDARY_ENGINE_ATTRIBUTE值，也不会在更改表的存储引擎时清除这些值。

  • INSERT_METHOD

    如果要向MERGE表插入数据，必须使用INSERT_METHOD指定要将行插入的表。INSERT_METHOD仅适用于MERGE表的选项。使用FIRST或LAST的值将插入到第一个或最后一个表中，使用NO的值将阻止插入。请参见第 18.7 节，“MERGE 存储引擎”。

  • KEY_BLOCK_SIZE

    对于MyISAM表，KEY_BLOCK_SIZE可选地指定用于索引键块的字节大小。该值被视为提示；如果需要，可以使用不同的大小。为单个索引定义指定的KEY_BLOCK_SIZE值会覆盖表级别的KEY_BLOCK_SIZE值。

    对于InnoDB表，KEY_BLOCK_SIZE指定了用于压缩InnoDB表的页大小（以千字节为单位）。KEY_BLOCK_SIZE值被视为提示；如果必要，InnoDB可能会使用不同的大小。KEY_BLOCK_SIZE只能小于或等于innodb_page_size值。值为 0 表示默认的压缩页大小，即innodb_page_size值的一半。根据innodb_page_size，可能的KEY_BLOCK_SIZE值包括 0、1、2、4、8 和 16。更多信息请参见 Section 17.9.1, “InnoDB Table Compression”。

    Oracle 建议在为InnoDB表指定KEY_BLOCK_SIZE时启用innodb_strict_mode。当启用innodb_strict_mode时，指定无效的KEY_BLOCK_SIZE值会返回错误。如果禁用innodb_strict_mode，无效的KEY_BLOCK_SIZE值会导致警告，并且KEY_BLOCK_SIZE选项会被忽略。

    对于SHOW TABLE STATUS响应中的Create_options列报告表实际使用的KEY_BLOCK_SIZE，SHOW CREATE TABLE也是如此。

    InnoDB仅支持表级别的KEY_BLOCK_SIZE。

    KEY_BLOCK_SIZE不支持 32KB 和 64KB 的innodb_page_size值。InnoDB表压缩不支持这些页大小。

    在创建临时表时，InnoDB不支持KEY_BLOCK_SIZE选项。

  • MAX_ROWS

    您计划存储在表中的最大行数。这不是一个硬限制，而是一个提示，告诉存储引擎表必须至少能够存储这么多行。

    重要

    使用MAX_ROWS来控制表分区数量的NDB表已被弃用。为了向后兼容，它在后续版本中仍然受支持，但可能在未来的版本中被移除。请改用 PARTITION_BALANCE；参见设置 NDB_TABLE 选项。

    NDB存储引擎将此值视为最大值。如果计划创建非常大的 NDB Cluster 表（包含数百万行），应使用此选项确保NDB为存储表主键哈希的哈希表分配足够数量的索引槽，方法是设置MAX_ROWS = 2 * *rows*，其中*rows*是您预计插入表中的行数。

    最大的MAX_ROWS值为 4294967295；更大的值将被截断为此限制。

  • MIN_ROWS

    您计划在表中存储的最小行数。MEMORY存储引擎将此选项用作有关内存使用的提示。

  • PACK_KEYS

    仅对MyISAM表生效。如果想要更小的索引，请将此选项设置为 1。通常这会使更新变慢，读取变快。将选项设置为 0 会禁用所有键的压缩。将其设置为DEFAULT会告诉存储引擎只压缩长的CHAR、VARCHAR、BINARY或VARBINARY列。

    如果不使用PACK_KEYS，默认情况下会压缩字符串，但不会压缩数字。如果使用PACK_KEYS=1，数字也会被压缩。

    在压缩二进制数字键时，MySQL 使用前缀压缩：

    • 每个键需要额外一个字节来指示前一个键的多少个字节与下一个键相同。

    • 指向行的指针以高字节优先顺序直接存储在键后面，以提高压缩效果。

    这意味着如果在两个连续行上有许多相同的键，通常后续的“相同”键只需要两个字节（包括指向行的指针）。与普通情况下后续键需要storage_size_for_key + pointer_size（其中指针大小通常为 4）相比，如果有许多相同的数字，则通过前缀压缩可以获得显著的好处。如果所有键都完全不同，每个键会多使用一个字节，如果键不是可以有NULL值的键。在这种情况下，压缩的键长度存储在用于标记键是否为NULL的同一个字节中。

  • PASSWORD

    此选项未使用。

  • ROW_FORMAT

    定义了行存储的物理格式。

    在禁用严格模式时创建表时，如果指定的行格式不受支持，则使用存储引擎的默认行格式。表的实际行格式将在响应SHOW TABLE STATUS时的Row_format列中报告。Create_options列显示了在CREATE TABLE语句中指定的行格式，SHOW CREATE TABLE也是如此。

    表使用的行格式取决于用于表的存储引擎。

    对于InnoDB表：

    • 默认行格式由innodb_default_row_format定义，其默认设置为DYNAMIC。当未定义ROW_FORMAT选项或使用ROW_FORMAT=DEFAULT时，将使用默认行格式。

      如果未定义ROW_FORMAT选项，或者使用ROW_FORMAT=DEFAULT，重建表的操作也会将表的行格式悄悄地更改为由innodb_default_row_format定义的默认值。有关更多信息，请参见定义表的行格式。

    • 为了更有效地存储数据类型，特别是BLOB类型，使用DYNAMIC。有关与DYNAMIC行格式相关的要求，请参见 DYNAMIC 行格式。

    • 要为InnoDB表启用压缩，请指定ROW_FORMAT=COMPRESSED。在创建临时表时，不支持ROW_FORMAT=COMPRESSED选项。有关与COMPRESSED行格式相关的要求，请参见第 17.9 节，“InnoDB 表和页面压缩”。

    • 旧版本的 MySQL 中使用的行格式仍可通过指定REDUNDANT行格式来请求。

    • 当指定非默认的ROW_FORMAT子句时，考虑同时启用innodb_strict_mode配置选项。

    • 不支持ROW_FORMAT=FIXED。如果在禁用innodb_strict_mode的情况下指定了ROW_FORMAT=FIXED，InnoDB会发出警告并假定ROW_FORMAT=DYNAMIC。如果在启用innodb_strict_mode（默认情况下）的情况下指定了ROW_FORMAT=FIXED，InnoDB会返回错误。

    • 有关InnoDB行格式的更多信息，请参见第 17.10 节，“InnoDB 行格式”。

    对于MyISAM表，选项值可以是FIXED或DYNAMIC，用于静态或变长行格式。myisampack将类型设置为COMPRESSED。请参见 Section 18.2.3, “MyISAM Table Storage Formats”。

    对于NDB表，默认的ROW_FORMAT是DYNAMIC。

  • START TRANSACTION

    这是一个内部使用的表选项。它在 MySQL 8.0.21 中引入，允许将CREATE TABLE ... SELECT作为单个原子事务记录在二进制日志中，当使用支持原子 DDL 的存储引擎进行基于行的复制时。在CREATE TABLE ... START TRANSACTION之后只允许BINLOG、COMMIT和ROLLBACK语句。有关相关信息，请参见 Section 15.1.1, “Atomic Data Definition Statement Support”。

  • STATS_AUTO_RECALC

    指定是否自动重新计算InnoDB表的持久性统计信息。值DEFAULT会导致表的持久性统计设置由innodb_stats_auto_recalc配置选项确定。值1会导致当表中的数据变化了 10%时重新计算统计信息。值0会阻止对该表进行自动重新计算；在这种设置下，在对表进行重大更改后，发出ANALYZE TABLE语句重新计算统计信息。有关持久性统计功能的更多信息，请参见 Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”。

  • STATS_PERSISTENT

    指定是否为InnoDB表启用持久性统计信息。值DEFAULT导致表的持久性统计信息设置由innodb_stats_persistent配置选项确定。值1为表启用持久性统计信息，而值0关闭此功能。通过CREATE TABLE或ALTER TABLE语句启用持久性统计信息后，需发出ANALYZE TABLE语句来计算统计信息，在将代表性数据加载到表中后。有关持久性统计信息功能的更多信息，请参见第 17.8.10.1 节，“配置持久性优化器统计参数”。

  • STATS_SAMPLE_PAGES

    在估算索引列的基数和其他统计信息时要采样的索引页数，例如由ANALYZE TABLE计算的那些。有关更多信息，请参见第 17.8.10.1 节，“配置持久性优化器统计参数”。

  • TABLESPACE

    TABLESPACE子句可用于在现有的通用表空间、每个表的文件表空间或系统表空间中创建InnoDB表。

    CREATE TABLE *tbl_name* ... TABLESPACE [=] *tablespace_name*

    您指定的通用表空间必须在使用TABLESPACE子句之前存在。有关通用表空间的信息，请参见第 17.6.3.3 节，“通用表空间”。

    *tablespace_name*是区分大小写的标识符。它可以带引号也可以不带。不允许使用斜杠字符（“/”）。以“innodb_”开头的名称保留供特殊用途。

    要在系统表空间中创建表，请将innodb_system指定为表空间名称。

    CREATE TABLE *tbl_name* ... TABLESPACE [=] innodb_system

    使用TABLESPACE [=] innodb_system，您可以将任何未压缩行格式的表放置在系统表空间中，而不受innodb_file_per_table设置的影响。例如，您可以使用TABLESPACE [=] innodb_system将具有ROW_FORMAT=DYNAMIC的表添加到系统表空间中。

    要在每个表的文件表空间中创建表，请将innodb_file_per_table指定为表空间名称。

    CREATE TABLE *tbl_name* ... TABLESPACE [=] innodb_file_per_table

    注意

    如果启用了innodb_file_per_table，则无需指定TABLESPACE=innodb_file_per_table来创建InnoDB每个表的文件表空间。当启用innodb_file_per_table时，默认情况下在每个表的文件表空间中创建InnoDB表。

    DATA DIRECTORY子句允许与CREATE TABLE ... TABLESPACE=innodb_file_per_table一起使用，但在与TABLESPACE子句结合使用时不受支持。从 MySQL 8.0.21 开始，DATA DIRECTORY子句中指定的目录必须为InnoDB所知。更多信息，请参阅使用 DATA DIRECTORY 子句。

    注意

    从 MySQL 8.0.13 开始，使用TABLESPACE = innodb_file_per_table和TABLESPACE = innodb_temporary子句与CREATE TEMPORARY TABLE已不再受支持；预计将在将来的 MySQL 版本中删除。

    STORAGE表选项仅用于NDB表。STORAGE确定所使用的存储类型，可以是DISK或MEMORY中的任一种。

    TABLESPACE ... STORAGE DISK将表分配给 NDB Cluster Disk Data 表空间。除非在TABLESPACE *tablespace_name*之前，否则不能在CREATE TABLE中使用STORAGE DISK。

    对于STORAGE MEMORY，表空间名称是可选的，因此，您可以使用TABLESPACE *tablespace_name* STORAGE MEMORY或简单地使用STORAGE MEMORY来明确指定表位于内存中。

    有关更多信息，请参阅第 25.6.11 节，“NDB Cluster Disk Data Tables”。

  • UNION

    用于将一组相同的MyISAM表作为一个表访问。这仅适用于MERGE表。请参阅第 18.7 节，“MERGE 存储引擎”。

    您必须对映射到MERGE表的表具有SELECT、UPDATE和DELETE权限。

    注意

    以前，所有使用的表都必须与MERGE表本身位于同一个数据库中。这个限制不再适用。

表分区

*partition_options*可用于控制使用CREATE TABLE创建的表的分区。

此部分开头显示的*partition_options*语法中并非所有选项适用于所有分区类型。有关每种类型的特定信息，请参阅以下各个类型的列表，并参阅第二十六章，分区，以获取有关 MySQL 中分区工作原理和用途的更完整信息，以及有关表创建和其他与 MySQL 分区相关的语句的其他示例。

分区可以被修改、合并、添加到表中，也可以从表中删除。有关完成这些任务的 MySQL 语句的基本信息，请参见 Section 15.1.9, “ALTER TABLE Statement”。有关更详细的描述和示例，请参见 Section 26.3, “Partition Management”。

  • PARTITION BY

    如果使用了 partition_options 子句，它以 PARTITION BY 开头。这个子句包含用于确定分区的函数；该函数返回一个从 1 到 num 的整数值，其中 num 是分区的数量。（一个表中可以包含的用户定义分区的最大数量是 1024；本节稍后讨论的子分区数量也包括在这个最大值中。）

    注意

    在 PARTITION BY 子句中使用的表达式 (expr) 不能引用不在被创建的表中的任何列；这样的引用是明确不允许的，并会导致语句失败并出现错误。 (Bug #29444)

  • 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) );

    不能在 PARTITION BY HASH 中使用 VALUES LESS THAN 或 VALUES IN 子句。

    PARTITION BY HASH 使用 expr 除以分区数后的余数（即模数）。有关示例和额外信息，请参见 Section 26.2.4, “HASH Partitioning”。

    LINEAR 关键字涉及一个略有不同的算法。在这种情况下，存储行的分区号是通过一个或多个逻辑 AND 操作的结果计算得出的。有关线性哈希的讨论和示例，请参见 Section 26.2.4.1, “LINEAR HASH Partitioning”。

  • KEY(*column_list*)

    这类似于 HASH，不同之处在于 MySQL 提供了哈希函数，以保证数据均匀分布。column_list 参数只是一个包含 1 个或多个表列（最多：16）的列表。这个示例展示了一个简单的按键分区的表，有 4 个分区：

    CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY KEY(col3)
    PARTITIONS 4;

    对于按键分区的表，您可以使用LINEAR关键字来采用线性分区。这与按HASH分区的表具有相同的效果。也就是说，分区号是使用&运算符而不是取模来确定的（详见第 26.2.4.1 节，“线性 HASH 分区”和第 26.2.5 节，“KEY 分区”）。此示例使用按键进行线性分区以在 5 个分区之间分发数据：

    CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR KEY(col3)
    PARTITIONS 5;

    ALGORITHM={1 | 2}选项支持[SUB]PARTITION BY [LINEAR] KEY。ALGORITHM=1使服务器使用与 MySQL 5.1 相同的键哈希函数；ALGORITHM=2表示服务器使用 MySQL 5.5 及更高版本中默认实现和使用的键哈希函数。 （使用 MySQL 5.5 及更高版本中实施的键哈希函数创建的分区表不能被 MySQL 5.1 服务器使用。）不指定该选项与使用ALGORITHM=2具有相同的效果。该选项主要用于在 MySQL 5.1 和后续 MySQL 版本之间升级或降级[LINEAR] KEY分区表时，或者在 MySQL 5.5 或更高版本服务器上创建按KEY或LINEAR KEY分区的表，该表可以在 MySQL 5.1 服务器上使用。有关更多信息，请参见第 15.1.9.1 节，“ALTER TABLE 分区操作”。

    mysqldump将此选项写入版本化注释中。

    在必要时，ALGORITHM=1将显示在使用版本化注释的SHOW CREATE TABLE输出中，方式与mysqldump相同。即使在创建原始表时指定了此选项，SHOW CREATE TABLE输出中也总是省略ALGORITHM=2。

    您不能在PARTITION BY KEY中使用VALUES LESS THAN或VALUES IN子句。

  • RANGE(*表达式*)

    在这种情况下，表达式使用一组VALUES LESS THAN运算符显示一系列值的范围。在使用范围分区时，您必须至少定义一个使用VALUES LESS THAN的分区。您不能在范围分区中使用VALUES IN。

    注意

    对于按RANGE分区的表，VALUES LESS THAN必须与整数文字值或评估为单个整数值的表达式一起使用。在 MySQL 8.0 中，您可以在使用PARTITION BY RANGE COLUMNS定义的表中克服这一限制，如本节后面所述。

    假设您有一个希望根据以下方案对包含年份值的列进行分区的表。

    分区号：年份范围：
    01990 年及之前
    11991 至 1994 年
    21995 to 1998
    31999 to 2002
    42003 to 2005
    52006 and later

    实现这种分区方案的表可以通过以下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 用于指定大于其他指定最大值的“剩余”值。

    VALUES LESS THAN子句按顺序工作，类似于switch ... case块的case部分（在许多编程语言中如 C、Java 和 PHP 中找到）。也就是说，这些子句必须按照每个连续VALUES LESS THAN中指定的上限大于前一个的方式排列，其中引用MAXVALUE的子句在列表中最后出现。

  • RANGE COLUMNS(*column_list*)

    这种RANGE变体便于对使用多列范围条件的查询进行分区修剪（即，具有诸如WHERE a = 1 AND b < 10或WHERE a = 1 AND b = 10 AND c < 10条件的查询）。它允许您通过在COLUMNS子句中列出的列列表和在每个PARTITION ... VALUES LESS THAN (*value_list*)分区定义子句中设置的列值集来指定多列中的值范围。（在最简单的情况下，此集合由单个列组成。）在column_list和value_list中引用的列的最大数量为 16。

    在COLUMNS子句中使用的column_list可能只包含列名；列表中的每个列必须是以下 MySQL 数据类型之一：整数类型；字符串类型；时间或日期列类型。不允许使用BLOB、TEXT、SET、ENUM、BIT或空间数据类型的列；也不允许使用浮点数类型的列。您也不能在COLUMNS子句中使用函数或算术表达式。

    在分区定义中使用的VALUES LESS THAN子句必须为COLUMNS()子句中出现的每个列指定一个文字值；也就是说，用于每个VALUES LESS THAN子句的值列表必须包含与COLUMNS子句中列出的列数相同的值。尝试在VALUES LESS THAN子句中使用比COLUMNS子句中列出的列数更多或更少的值会导致出现错误 Inconsistency in usage of column lists for partitioning...。您不能对出现在VALUES LESS THAN中的任何值使用NULL。可以多次使用MAXVALUE来表示给定列，如下例所示：

    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)
);

    在VALUES LESS THAN值列表中使用的每个值必须与相应列的类型完全匹配；不会进行任何转换。例如，您不能为与使用整数类型的列匹配的值使用字符串'1'（您必须使用数字1），也不能为与使用字符串类型的列匹配的值使用数字1（在这种情况下，您必须使用带引号的字符串：'1'）。

    有关更多信息，请参见第 26.2.1 节，“RANGE 分区”和第 26.4 节，“分区修剪”。

  • LIST(*expr*)

    当基于具有受限可能值集的表列分配分区时，这是很有用的，例如州或国家代码。在这种情况下，所有属于某个州或国家的行可以分配到一个分区，或者可以为某个州或国家集保留一个分区。它类似于RANGE，只是每个分区只能使用VALUES IN来指定可允许的值。

    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的分区。您不能在PARTITION BY LIST中使用VALUES LESS THAN。

    注意

    对于通过LIST分区的表，与VALUES IN一起使用的值列表必须仅包含整数值。在 MySQL 8.0 中，您可以通过后面在本节中描述的LIST COLUMNS进行分区来克服这种限制。

  • LIST COLUMNS(*column_list*)

    这种LIST的变体为使用多列比较条件的查询提供了分区修剪的便利（即，具有诸如WHERE a = 5 AND b = 5或WHERE a = 1 AND b = 10 AND c = 5等条件）。它允许您通过在COLUMNS子句中使用列列表和在每个PARTITION ... VALUES IN (*value_list*)分区定义子句中使用一组列值来指定多列的值。

    用于LIST COLUMNS(*column_list*)中的列列表和VALUES IN(*value_list*)中使用的值列表的数据类型规则与用于RANGE COLUMNS(*column_list*)中的列列表和VALUES LESS THAN(*value_list*)中使用的值列表的规则相同，只是在VALUES IN子句中，不允许使用MAXVALUE，您可以使用NULL。

    在使用PARTITION BY LIST COLUMNS时，与在使用PARTITION BY LIST时使用VALUES IN的值列表之间有一个重要区别。在与PARTITION BY LIST COLUMNS一起使用时，VALUES IN子句中的每个元素必须是一组列值；每组中的值数量必须与COLUMNS子句中使用的列数相同，并且这些值的数据类型必须与列的数据类型匹配（并且以相同顺序出现）。在最简单的情况下，该集合由单个列组成。在*column_list和组成value_list*的元素中可以使用的最大列数为 16。

    以下CREATE TABLE语句定义的表提供了使用LIST COLUMNS分区的示例：

    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) )
);

  • PARTITIONS *num*

    可以选择使用PARTITIONS *num*子句指定分区的数量，其中*num是分区的数量。如果同时使用此子句和任何使用PARTITION子句声明的分区，则num*必须等于使用PARTITION子句声明的所有分区的总数。

    注意

    在创建按RANGE或LIST分区的表时，无论是否使用PARTITIONS子句，仍必须在表定义中至少包含一个PARTITION VALUES子句（见下文）。

  • SUBPARTITION BY

    分区可以选择地分成多个子分区。这可以通过使用可选的SUBPARTITION BY子句来指示。子分区可以通过HASH或KEY进行。其中任何一个都可以是LINEAR。这与先前描述的等效分区类型的工作方式相同。（不可能通过LIST或RANGE进行子分区。）

    可以使用SUBPARTITIONS关键字后跟一个整数值来指示子分区的数量。

  • 对PARTITIONS或SUBPARTITIONS子句中使用的值进行严格检查，并且此值必须遵守以下规则：

    • 值必须是正的、非零整数。

    • 不允许前导零。

    • 值必须是整数文字，并且不能是表达式。例如，PARTITIONS 0.2E+01是不允许的，即使0.2E+01评估为2。（Bug #15890）

  • *partition_definition*

    可以使用*partition_definition*子句分别定义每个分区。构成此子句的各个部分如下：

    • PARTITION *partition_name*

      为分区指定一个逻辑名称。

    • VALUES

      对于范围分区，每个分区必须包括一个VALUES LESS THAN子句；对于列表分区，您必须为每个分区指定一个VALUES IN子句。这用于确定哪些行将存储在此分区中。有关分区类型的讨论，请参见第二十六章，分区中的语法示例。

    • [STORAGE] ENGINE

      MySQL 接受 PARTITION 和 SUBPARTITION 的 [STORAGE] ENGINE 选项。目前，此选项可用的唯一方式是将所有分区或所有子分区设置为相同的存储引擎，尝试为同一表中的分区或子分区设置不同的存储引擎会引发错误 ERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in this version of MySQL。

    • COMMENT

      可以使用可选的 COMMENT 子句来指定描述分区的字符串。示例：

      COMMENT = 'Data for the years previous to 1999'

      分区注释的最大长度为 1024 个字符。

    • DATA DIRECTORY 和 INDEX DIRECTORY

      DATA DIRECTORY 和 INDEX DIRECTORY 可用于指示存储此分区数据和索引的目录。*data_dir* 和 *index_dir* 必须是绝对系统路径名。

      截至 MySQL 8.0.21 版本，DATA DIRECTORY 子句中指定的目录必须为 InnoDB 所知。更多信息，请参阅 使用 DATA DIRECTORY 子句。

      您必须具有 FILE 权限才能使用 DATA DIRECTORY 或 INDEX DIRECTORY 分区选项。

      示例：

      CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/var/appdata/96/data'
    INDEX DIRECTORY = '/var/appdata/96/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/var/appdata/97/data'
    INDEX DIRECTORY = '/var/appdata/97/idx',
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/var/appdata/98/data'
    INDEX DIRECTORY = '/var/appdata/98/idx'
);

      DATA DIRECTORY 和 INDEX DIRECTORY 的行为与用于 MyISAM 表的 CREATE TABLE 语句的 table_option 子句中的行为相同。

      每个分区可以指定一个数据目录和一个索引目录。如果未指定，数据和索引默认存储在表的数据库目录中。

      如果 NO_DIR_IN_CREATE 生效，则在创建分区表时将忽略 DATA DIRECTORY 和 INDEX DIRECTORY 选项。

    • MAX_ROWS 和 MIN_ROWS

      可以分别指定要存储在分区中的最大和最小行数。max_number_of_rows 和 min_number_of_rows 的值必须是正整数。与具有相同名称的表级选项一样，这些值仅作为服务器的“建议”，而不是硬限制。

    • TABLESPACE

      可以通过指定 TABLESPACE innodb_file_per_table`` 为分区指定一个 InnoDB 每表表空间。所有分区必须属于相同的存储引擎。

      不支持将 InnoDB 表分区放置在共享的 InnoDB 表空间中。共享表空间包括 InnoDB 系统表空间和通用表空间。

  • *subpartition_definition*

    分区定义可以选择性地包含一个或多个 subpartition_definition 子句。每个子句至少包含 SUBPARTITION *name*，其中 name 是子分区的标识符。除了将 PARTITION 关键字替换为 SUBPARTITION 外，子分区定义的语法与分区定义完全相同。

    子分区必须通过HASH或KEY完成，并且只能在RANGE或LIST分区上完成。参见第 26.2.6 节，“子分区”。

通过生成列进行分区

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

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

分区将生成的列视为常规列，这使得可以解决对于分区不允许的函数的限制（参见第 26.6.3 节，“与函数相关的分区限制”）。前面的示例演示了这种技术：EXP()不能直接在PARTITION BY子句中使用，但可以使用使用EXP()定义的生成列。

原文：dev.mysql.com/doc/refman/8.0/en/create-table-files.html

15.1.20.1 由 CREATE TABLE 创建的文件

对于在文件表空间或通用表空间中创建的InnoDB表，表数据和相关索引存储在数据库目录中的一个.ibd 文件中。当在系统表空间中创建InnoDB表时，表数据和索引存储在代表系统表空间的 ibdata*文件中。innodb_file_per_table选项控制表是在文件表空间还是系统表空间中创建，默认情况下。TABLESPACE选项可用于将表放置在文件表空间、通用表空间或系统表空间中，而不受innodb_file_per_table设置的影响。

对于MyISAM表，存储引擎会创建数据和索引文件。因此，对于每个MyISAM表*tbl_name*，都会有两个磁盘文件。

文件目的
*tbl_name*.MYD数据文件
*tbl_name*.MYI索引文件

第十八章，替代存储引擎，描述了每个存储引擎创建哪些文件来表示表。如果表名包含特殊字符，则表文件的名称将包含这些字符的编码版本，如第 11.2.4 节，“标识符映射到文件名”中所述。

原文：dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

15.1.20.2 CREATE TEMPORARY TABLE Statement

在创建表时可以使用 TEMPORARY 关键字。TEMPORARY 表仅在当前会话中可见，并在会话关闭时自动删除。这意味着两个不同的会话可以使用相同的临时表名称而不会与彼此或同名的现有非 TEMPORARY 表发生冲突。（现有表在临时表被删除之前是隐藏的。）

InnoDB 不支持压缩临时表。当启用 innodb_strict_mode（默认情况下）时，如果指定了 ROW_FORMAT=COMPRESSED 或 KEY_BLOCK_SIZE，CREATE TEMPORARY TABLE 将返回错误。如果禁用了 innodb_strict_mode，则会发出警告并使用非压缩行格式创建临时表。innodb_file_per-table 选项不影响创建 InnoDB 临时表。

CREATE TABLE 会导致隐式提交，除非与 TEMPORARY 关键字一起使用。参见 Section 15.3.3, “Statements That Cause an Implicit Commit”。

TEMPORARY 表与数据库（模式）之间的关系非常松散。删除数据库不会自动删除在该数据库中创建的任何 TEMPORARY 表。

要创建临时表，您必须具有 CREATE TEMPORARY TABLES 权限。会话创建临时表后，服务器不会对表执行进一步的权限检查。创建会话可以对表执行任何操作，如 DROP TABLE、INSERT、UPDATE 或 SELECT。

这种行为的一个含义是，一个会话可以操作其临时表，即使当前用户没有创建它们的特权。假设当前用户没有CREATE TEMPORARY TABLES特权，但能够执行一个以定义者上下文执行且具有CREATE TEMPORARY TABLES特权的用户权限的存储过程，该存储过程创建了一个临时表。在存储过程执行时，会话使用定义用户的权限。存储过程返回后，有效权限会恢复到当前用户的权限，当前用户仍然可以看到临时表并对其执行任何操作。

你不能使用CREATE TEMPORARY TABLE ... LIKE来基于mysql表空间、InnoDB系统表空间(innodb_system)或通用表空间中的表的定义创建一个空表。这种表的表空间定义包括一个TABLESPACE属性，定义了表所在的表空间，而上述表空间不支持临时表。要基于这种表的定义创建一个临时表，使用以下语法：

CREATE TEMPORARY TABLE *new_tbl* SELECT * FROM *orig_tbl* LIMIT 0;

注意

自 MySQL 8.0.13 起，使用CREATE TEMPORARY TABLE中的TABLESPACE = innodb_file_per_table和TABLESPACE = innodb_temporary子句已被弃用；预计在未来的 MySQL 版本中将被移除。

原文：dev.mysql.com/doc/refman/8.0/en/create-table-like.html

15.1.20.3 CREATE TABLE ... LIKE 语句

使用CREATE TABLE ... LIKE根据另一个表的定义创建一个空表，包括原始表中定义的任何列属性和索引：

CREATE TABLE *new_tbl* LIKE *orig_tbl*;

复制是使用与原始表相同版本的表存储格式创建的。需要在原始表上具有SELECT权限。

LIKE仅适用于基本表，不适用于视图。

重要提示

在执行LOCK TABLES语句时，不能执行CREATE TABLE或CREATE TABLE ... LIKE。

CREATE TABLE ... LIKE执行与CREATE TABLE相同的检查。这意味着如果当前的 SQL 模式与创建原始表时生效的模式不同，表定义可能被认为对新模式无效并导致语句失败。

对于CREATE TABLE ... LIKE，目标表保留原始表中的生成列信息。

对于CREATE TABLE ... LIKE，目标表保留原始表中的表达式默认值。

对于CREATE TABLE ... LIKE，目标表保留原始表中的CHECK约束，只是所有约束名称都是自动生成的。

CREATE TABLE ... LIKE不保留为原始表指定的任何DATA DIRECTORY或INDEX DIRECTORY表选项，也不保留任何外键定义。

如果原始表是临时表，则CREATE TABLE ... LIKE不保留TEMPORARY。要创建一个TEMPORARY目标表，请使用CREATE TEMPORARY TABLE ... LIKE。

在mysql表空间、InnoDB系统表空间（innodb_system）或通用表空间中创建的表包括表定义中的TABLESPACE属性，该属性定义了表所在的表空间。由于临时回归，CREATE TABLE ... LIKE保留了TABLESPACE属性，并在定义的表空间中创建表，而不管innodb_file_per_table设置如何。为了在基于此类表的定义创建空表时避免TABLESPACE属性，请改用以下语法：

CREATE TABLE *new_tbl* SELECT * FROM *orig_tbl* LIMIT 0;

CREATE TABLE ... LIKE操作将所有ENGINE_ATTRIBUTE和SECONDARY_ENGINE_ATTRIBUTE值应用于新表。

原文：dev.mysql.com/doc/refman/8.0/en/create-table-select.html

15.1.20.4 创建表...选择语句

您可以通过在CREATE TABLE语句末尾添加一个SELECT语句来从另一个表创建一个表：

CREATE TABLE *new_tbl* [AS] SELECT * FROM *orig_tbl*;

MySQL 为SELECT中的所有元素创建新列。例如：

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
 ->        PRIMARY KEY (a), KEY(b))
 ->        ENGINE=InnoDB SELECT b,c FROM test2;

这将创建一个带有三列a、b和c的InnoDB表。ENGINE选项是CREATE TABLE语句的一部分，不应在SELECT之后使用；否则会导致语法错误。其他CREATE TABLE选项如CHARSET也是如此。

注意，SELECT语句中的列附加在表的右侧，而不是重叠在其上。看下面的例子：

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

对于表foo中的每一行，在bar中插入一行，其值来自foo并且新列使用默认值。

在由CREATE TABLE ... SELECT生成的表中，仅在CREATE TABLE部分命名的列首先出现。在两个部分或仅在SELECT部分命名的列在其后出现。SELECT列的数据类型可以通过在CREATE TABLE部分中指定该列来覆盖。

如果在将数据复制到表时发生错误，则表会自动删除而不会创建。然而，在 MySQL 8.0.21 之前，当使用基于行的复制时，CREATE TABLE ... SELECT语句会在二进制日志中记录为两个事务，一个用于创建表，另一个用于插入数据。当从二进制日志应用语句时，在两个事务之间或在复制数据时发生故障可能导致复制空表。这个限制在 MySQL 8.0.21 中被移除。在支持原子 DDL 的存储引擎上，当使用基于行的复制时，CREATE TABLE ... SELECT现在被记录并应用为一个事务。更多信息，请参见第 15.1.1 节，“原子数据定义语句支持”。

截至 MySQL 8.0.21，在支持原子 DDL 和外键约束的存储引擎上，在使用基于行的复制时，不允许在CREATE TABLE ... SELECT语句中创建外键。可以稍后使用ALTER TABLE添加外键约束。

您可以在SELECT之前加上IGNORE或REPLACE来指示如何处理重复唯一键值的行。使用IGNORE，重复唯一键值的行将被丢弃。使用REPLACE，新行将替换具有相同唯一键值的行。如果未指定IGNORE或REPLACE，重复的唯一键值将导致错误。有关更多信息，请参见 IGNORE 对语句执行的影响。

在 MySQL 8.0.19 及更高版本中，您还可以在CREATE TABLE ... SELECT语句的SELECT部分使用VALUES语句；VALUES语句的部分必须包含使用AS子句的表别名。为了命名来自VALUES的列，使用表别名提供列别名；否则，默认列名column_0、column_1、column_2等将被使用。

否则，在创建的表中列的命名遵循本节中先前描述的相同规则。示例：

mysql> CREATE TABLE tv1
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v;
mysql> TABLE tv1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+

mysql> CREATE TABLE tv2
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv2;
+---+---+---+
| x | y | z |
+---+---+---+
| 1 | 3 | 5 |
| 2 | 4 | 6 |
+---+---+---+

mysql> CREATE TABLE tv3 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv3;
+------+------+------+----------+----------+----------+
| a    | b    | c    |        x |        y |        z |
+------+------+------+----------+----------+----------+
| NULL | NULL | NULL |        1 |        3 |        5 |
| NULL | NULL | NULL |        2 |        4 |        6 |
+------+------+------+----------+----------+----------+

mysql> CREATE TABLE tv4 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv4;
+------+------+------+---+---+---+
| a    | b    | c    | x | y | z |
+------+------+------+---+---+---+
| NULL | NULL | NULL | 1 | 3 | 5 |
| NULL | NULL | NULL | 2 | 4 | 6 |
+------+------+------+---+---+---+

mysql> CREATE TABLE tv5 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(a,b,c);
mysql> TABLE tv5;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    3 |    5 |
|    2 |    4 |    6 |
+------+------+------+

当选择所有列并使用默认列名时，可以省略SELECT *，因此刚刚用于创建表tv1的语句也可以写成如下所示：

mysql> CREATE TABLE tv1 VALUES ROW(1,3,5), ROW(2,4,6);
mysql> TABLE tv1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+

当使用VALUES作为SELECT的源时，所有列始终被选入新表中，无法像从命名表中选择时那样选择单独的列；以下每个语句都会产生错误(ER_OPERAND_COLUMNS)：

CREATE TABLE tvx
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

CREATE TABLE tvx (a INT, c INT)
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

类似地，您可以在SELECT语句的位置使用TABLE语句。这遵循与VALUES相同的规则；源表的所有列及其在源表中的名称始终被插入到新表中。示例：

mysql> TABLE t1;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
| 10 | -4 |
| 14 |  6 |
+----+----+

mysql> CREATE TABLE tt1 TABLE t1;
mysql> TABLE tt1;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
| 10 | -4 |
| 14 |  6 |
+----+----+

mysql> CREATE TABLE tt2 (x INT) TABLE t1;
mysql> TABLE tt2;
+------+----+----+
| x    | a  | b  |
+------+----+----+
| NULL |  1 |  2 |
| NULL |  6 |  7 |
| NULL | 10 | -4 |
| NULL | 14 |  6 |
+------+----+----+

由于底层SELECT语句中行的排序不能总是确定，CREATE TABLE ... IGNORE SELECT和CREATE TABLE ... REPLACE SELECT语句被标记为不安全的基于语句的复制。在使用基于语句的模式时，这些语句在错误日志中产生警告，并在使用MIXED模式时以基于行的格式写入二进制日志。另请参阅第 19.2.1.1 节，“基于语句和基于行的复制的优缺点”。

CREATE TABLE ... SELECT不会为您自动创建任何索引。这是有意为之，以使语句尽可能灵活。如果您希望在创建的表中有索引，您应该在SELECT语句之前指定这些索引：

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

对于CREATE TABLE ... SELECT，目标表不会保留所选自表中列是否为生成列的信息。语句的SELECT部分不能为目标表中的生成列分配值。

对于CREATE TABLE ... SELECT，目标表会保留原始表中的表达式默认值。

可能会发生一些数据类型的转换。例如，AUTO_INCREMENT属性不会被保留，VARCHAR列可能会变成CHAR列。保留的属性包括NULL（或NOT NULL）以及对于那些具有的列，CHARACTER SET，COLLATION，COMMENT和DEFAULT子句。

在使用CREATE TABLE ... SELECT创建表时，请确保为查询中的任何函数调用或表达式设置别名。如果不这样做，CREATE语句可能会失败或导致不良的列名称。

CREATE TABLE artists_and_works
  SELECT artist.name, COUNT(work.artist_id) AS number_of_works
  FROM artist LEFT JOIN work ON artist.id = work.artist_id
  GROUP BY artist.id;

您还可以明确指定创建表中列的数据类型：

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

对于CREATE TABLE ... SELECT，如果指定了IF NOT EXISTS并且目标表存在，则不会将任何内容插入目标表，并且该语句不会被记录。

为了确保二进制日志可以用于重新创建原始表，MySQL 不允许在 CREATE TABLE ... SELECT 过程中进行并发插入。然而，在 MySQL 8.0.21 之前，当使用基于行的复制时，从二进制日志应用 CREATE TABLE ... SELECT 操作时，允许在复制表上进行并发插入。这个限制在支持原子 DDL 的存储引擎上在 MySQL 8.0.21 中被移除。更多信息，请参见 Section 15.1.1, “Atomic Data Definition Statement Support”。

你不能在类似 CREATE TABLE *new_table* SELECT ... FROM *old_table* ... 的语句中将 FOR UPDATE 作为 SELECT 的一部分。如果尝试这样做，该语句将失败。

CREATE TABLE ... SELECT 操作仅对列应用 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值。除非明确指定，否则表和索引的 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值不会应用于新表。

原文：dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

15.1.20.5 外键约束

MySQL 支持外键，允许在表之间进行相关数据的交叉引用，并支持外键约束，有助于保持相关数据的一致性。

外键关系涉及一个包含初始列值的父表，以及一个包含引用父列值的列值的子表。外键约束定义在子表上。

在CREATE TABLE或ALTER TABLE语句中定义外键约束的基本语法包括以下内容：

[CONSTRAINT [*symbol*]] FOREIGN KEY
    [*index_name*] (*col_name*, ...)
    REFERENCES *tbl_name* (*col_name*,...)
    [ON DELETE *reference_option*]
    [ON UPDATE *reference_option*]

*reference_option*:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

外键约束的使用方法在本节的以下主题中描述：

  • 标识符

  • 条件和限制

  • 参考动作

  • 外键约束示例

  • 添加外键约束

  • 删除外键约束

  • 外键检查

  • 锁定

  • 外键定义和元数据

  • 外键错误

标识符

外键约束命名受以下规则约束：

  • 如果未定义CONSTRAINT *symbol*子句，或在CONSTRAINT关键字后未包含符号，则会自动生成约束名。

  • 如果未定义CONSTRAINT *symbol*子句，或在CONSTRAINT关键字后未包含符号，则会自动生成约束名。

    在 MySQL 8.0.16 之前，如果未定义CONSTRAINT *symbol*子句，或在CONSTRAINT关键字后未包含符号，则InnoDB和NDB存储引擎将使用FOREIGN_KEY *index_name*（如果已定义）。在 MySQL 8.0.16 及更高版本中，将忽略FOREIGN_KEY *index_name*。

  • 如果定义了CONSTRAINT *symbol*值，则必须在数据库中是唯一的。重复的*symbol*会导致类似以下错误：ERROR 1005 (HY000): 无法创建表'test.fk1'（错误号：121）。

  • NDB 集群使用创建时的相同大小写存储外键名称。在版本 8.0.20 之前，当处理 SELECT 和其他 SQL 语句时，NDB 将这些语句中的外键名称与存储的名称进行比较，当 lower_case_table_names 等于 0 时，以区分大小写的方式存储。在 NDB 8.0.20 及更高版本中，此值不再影响这些比较是如何进行的，它们总是不考虑大小写地进行。 (Bug #30512043)

在 FOREIGN KEY ... REFERENCES 子句中的表格和列标识符可以在反引号内引用 (```sql). Alternatively, double quotation marks (") can be used if the ANSI_QUOTES SQL mode is enabled. The lower_case_table_names system variable setting is also taken into account.

Conditions and Restrictions

Foreign key constraints are subject to the following conditions and restrictions:

  • Parent and child tables must use the same storage engine, and they cannot be defined as temporary tables.

  • Creating a foreign key constraint requires the REFERENCES privilege on the parent table.

  • Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as INTEGER - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT") and DECIMAL - DECIMAL, NUMERIC") must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

  • MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a “child table record” refers to a dependent record within the same table.

  • MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

  • InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 17.6.2.1, “Clustered and Secondary Indexes”).

    NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL.

  • Index prefixes on foreign key columns are not supported. Consequently, BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

  • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.

    This restriction does not apply for NDB tables that are partitioned by KEY or LINEAR KEY (the only user partitioning types supported by the NDB storage engine); these may have foreign key references or be the targets of such references.

  • A table in a foreign key relationship cannot be altered to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

  • A foreign key constraint cannot reference a virtual generated column.

For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 1.6.2.3, “FOREIGN KEY Constraint Differences”.

Referential Actions

When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. Referential actions include:

  • CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

    If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error.

    Note

    Cascaded foreign key actions do not activate triggers.

  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

    If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

  • RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

  • NO ACTION: A keyword from standard SQL. For InnoDB, this is equivalent to RESTRICT; the delete or update operation for the parent table is immediately rejected if there is a related foreign key value in the referenced table. NDB supports deferred checks, and NO ACTION specifies a deferred check; when this is used, constraint checks are not performed until commit time. Note that for NDB tables, this causes all foreign key checks made for both parent and child tables to be deferred.

  • SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

For storage engines that support foreign keys, MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table.

For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION.

As the default, an ON DELETE NO ACTION or ON UPDATE NO ACTION clause that is specified explicitly does not appear in SHOW CREATE TABLE output or in tables dumped with mysqldump. RESTRICT, which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped with mysqldump.

For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key.

As of NDB 8.0.16: For NDB tables, ON DELETE CASCADE is not supported where the child table contains one or more columns of any of the TEXT or BLOB types. (Bug #89511, Bug #27484882)

InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint.

A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.

A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

Foreign Key Constraint Examples

This simple example relates parent and child tables through a single-column foreign key:


创建表格父 (

    id INT 不为空,

    主键 (id)

) 引擎=INNODB;

创建表格子表 (

    id INT,

    parent_id INT,

    索引 par_ind (parent_id),

    外键 (parent_id)

        REFERENCES 父(id)

        在删除时级联

) 引擎=INNODB;

```sql

This is a more complex example in which a `product_order` table has foreign keys for two other tables. One foreign key references a two-column index in the `product` table. The other references a single-column index in the `customer` table:

创建表格产品 (

category INT NOT NULL, id INT NOT NULL,

价格 DECIMAL,

主键(category, id)

) 引擎=INNODB;

创建表格顾客 (

id INT 不为空,

主键 (id)

) 引擎=INNODB;

创建表格产品订单 (

no INT 不为空 自动增量,

产品类别 INT 不为空,

product_id INT NOT NULL,

顾客 id INT 不为空,

主键(no),

索引 (产品类别, 产品 id),

索引 (顾客 id),

外键 (产品类别, 产品 id)

REFERENCES 产品(类别, id)

在更新时级联在删除时限制,

外键 (customer_id)

REFERENCES 顾客(id)

) 引擎=INNODB;


##### Adding Foreign Key Constraints

You can add a foreign key constraint to an existing table using the following `ALTER TABLE` syntax:

ALTER TABLE tbl_name

添加 [约束 [*symbol*]] 外键

[*index_name*] (*col_name*, ...)

REFERENCES *tbl_name* (*col_name*,...)

[ON DELETE *reference_option*]

[在更新 *reference_option*]


The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using `ALTER TABLE`, *remember to first create an index on the column(s) referenced by the foreign key.*

##### Dropping Foreign Key Constraints

You can drop a foreign key constraint using the following `ALTER TABLE` syntax:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;


If the `FOREIGN KEY` clause defined a `CONSTRAINT` name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use `SHOW CREATE TABLE`:

mysql> SHOW CREATE TABLE child\G

*************************** 1. 行 ***************************

表格：子表

创建表格：CREATE TABLE child (

id int DEFAULT NULL,

parent_id int DEFAULT NULL,

键 par_ind (parent_id),

CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id)

REFERENCES parent (id) ON DELETE CASCADE

) 引擎=InnoDB 默认字符集=utf8mb4 校对=utf8mb4_0900_ai_ci

mysql> ALTER TABLE child DROP FOREIGN KEY child_ibfk_1;


Adding and dropping a foreign key in the same `ALTER TABLE` statement is supported for `ALTER TABLE ... ALGORITHM=INPLACE`. It is not supported for `ALTER TABLE ... ALGORITHM=COPY`.

##### Foreign Key Checks

In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the `foreign_key_checks` variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity. The `foreign_key_checks` variable has the same effect on `NDB` tables as it does for `InnoDB` tables.

The `foreign_key_checks` variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 7.1.9, “Using System Variables”.

Disabling foreign key checking is useful when:

*   Dropping a table that is referenced by a foreign key constraint. A referenced table can only be dropped after `foreign_key_checks` is disabled. When you drop a table, constraints defined on the table are also dropped.

*   Reloading tables in different order than required by their foreign key relationships. For example, **mysqldump** produces correct definitions of tables in the dump file, including foreign key constraints for child tables. To make it easier to reload dump files for tables with foreign key relationships, **mysqldump** automatically includes a statement in the dump output that disables `foreign_key_checks`. This enables you to import the tables in any order in case the dump file contains tables that are not correctly ordered for foreign keys. Disabling `foreign_key_checks` also speeds up the import operation by avoiding foreign key checks.

*   Executing `LOAD DATA` operations, to avoid foreign key checking.

*   Performing an `ALTER TABLE` operation on a table that has a foreign key relationship.

When `foreign_key_checks` is disabled, foreign key constraints are ignored, with the following exceptions:

*   Recreating a table that was previously dropped returns an error if the table definition does not conform to the foreign key constraints that reference the table. The table must have the correct column names and types. It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error 1005 that refers to errno: 150 in the error message, which means that a foreign key constraint was not correctly formed.

*   Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table.

*   Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.

*   Creating a foreign key constraint where a column references a nonmatching column type.

Disabling `foreign_key_checks` has these additional implications:

*   It is permitted to drop a database that contains tables with foreign keys that are referenced by tables outside the database.

*   It is permitted to drop a table with foreign keys referenced by other tables.

*   Enabling `foreign_key_checks` does not trigger a scan of table data, which means that rows added to a table while `foreign_key_checks` is disabled are not checked for consistency when `foreign_key_checks` is re-enabled.

##### Locking

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.

If a table is locked explicitly with `LOCK TABLES`, any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock (`LOCK TABLES READ`) is taken on related tables. For cascading updates, a shared-nothing write lock (`LOCK TABLES WRITE`) is taken on related tables that are involved in the operation.

##### Foreign Key Definitions and Metadata

To view a foreign key definition, use `SHOW CREATE TABLE`:

mysql> SHOW CREATE TABLE child\G

*************************** 1. 行 ***************************

表格：子表

创建表格：CREATE TABLE child (

id int DEFAULT NULL,

parent_id int DEFAULT NULL,

键 par_ind (parent_id),

CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id)

REFERENCES parent (id) ON DELETE CASCADE

) 引擎=InnoDB 默认字符集=utf8mb4 校对=utf8mb4_0900_ai_ci


You can obtain information about foreign keys from the Information Schema `KEY_COLUMN_USAGE` table. An example of a query against this table is shown here:

mysql> 选择 表格模式, 表格名称, 列名, 约束名称

来自 INFORMATION_SCHEMA.KEY_COLUMN_USAGE

当引用表格模式不为空时;

+--------------+------------+-------------+-----------------+

表格模式表格名称列名约束名称

+--------------+------------+-------------+-----------------+

testchildparent_idchild_ibfk_1

+--------------+------------+-------------+-----------------+


You can obtain information specific to `InnoDB` foreign keys from the `INNODB_FOREIGN` and `INNODB_FOREIGN_COLS` tables. Example queries are show here:

mysql> 从 INFORMATION_SCHEMA.INNODB_FOREIGN 表中选择 * \G

*************************** 1. 行 ***************************

ID: test/child_ibfk_1

FOR_NAME: test/child

REF_NAME: test/parent

N_COLS: 1

类型: 1

mysql> 从 INFORMATION_SCHEMA.INNODB_FOREIGN_COLS 表中选择 * \G

*************************** 1. 行 ***************************

    ID: test/child_ibfk_1

FOR_COL_NAME: parent_id

REF_COL_NAME: id

    位置: 0


##### Foreign Key Errors

In the event of a foreign key error involving `InnoDB` tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checking `SHOW ENGINE INNODB STATUS` output.

mysql> 显示引擎 INNODB 状态\G

...

最新的外键错误

2018-04-12 14:57:24 0x7f97a9c91700 事务:

事务 7717，活跃 0 秒插入

使用中的 mysql 表 1，已锁定 1

4 个锁结构，堆大小 1136，3 个行锁，撤销日志条目 3

MySQL 线程 id 8，OS 线程句柄 140289365317376，查询 id 14 localhost root 更新

插入到 child 表中的值 (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)

对于表 test.child 的外键约束失败：

,

约束 child_ibfk_1 外键 (parent_id) 引用 parent 表 (id) 在删除时

级联更新级联

尝试在子表中添加，索引 par_ind 元组：

数据元组: 2 个字段;

0: 长度 4; 十六进制 80000003; ASCII ;;

1: 长度 4; 十六进制 80000003; ASCII ;;

但在父表 test.parent 中，在主键 PRIMARY 中，

我们能找到的最接近的匹配是记录：

物理记录: 字段数 3; 紧凑格式; 信息位 0

0: 长度 4; 十六进制 80000004; ASCII ;;

1: 长度 6; 十六进制 000000001e19; ASCII ;;

2: 长度 7; 十六进制 81000001110137; ASCII 7;;

...


警告

如果用户对所有父表具有表级别权限，则外键操作的 `ER_NO_REFERENCED_ROW_2` 和 `ER_ROW_IS_REFERENCED_2` 错误消息会暴露有关父表的信息。如果用户对所有父表没有表级别权限，则显示更通用的错误消息 (`ER_NO_REFERENCED_ROW` 和 `ER_ROW_IS_REFERENCED`)。

一个例外是，对于定义为使用 `DEFINER` 权限执行的存储程序，权限评估的用户是程序中 `DEFINER` 子句中的用户，而不是调用用户。如果该用户具有表级别父表权限，父表信息仍然会显示。在这种情况下，存储程序创建者有责任通过包含适当的条件处理程序来隐藏信息。


> 原文：[`dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html`](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html)

#### 15.1.20.6 CHECK 约束

在 MySQL 8.0.16 之前，`CREATE TABLE`仅允许以下有限版本的表`CHECK`约束语法，该语法被解析并忽略：

```sql
CHECK (*expr*)

截至 MySQL 8.0.16，CREATE TABLE允许所有存储引擎的表和列CHECK约束的核心特性。CREATE TABLE允许以下CHECK约束语法，适用于表约束和列约束：

[CONSTRAINT [*symbol*]] CHECK (*expr*) [[NOT] ENFORCED]

可选的*symbol*指定了约束的名称。如果省略，MySQL 会从表名、字面量_chk_和一个序号（1、2、3、...）生成一个名称。约束名称最长为 64 个字符。它们区分大小写，但不区分重音符号。

*expr*指定约束条件为一个布尔表达式，每行必须评估为TRUE或UNKNOWN（对于NULL值）。如果条件评估为FALSE，则失败并发生约束违反。违反的效果取决于正在执行的语句，如本节后面所述。

可选的强制执行子句指示约束是否被执行：

  • 如果省略或指定为ENFORCED，则创建并执行约束。

  • 如果指定为NOT ENFORCED，则创建约束但不执行。

CHECK约束可以指定为表约束或列约束：

  • 表约束不出现在列定义中，可以引用任何表列或列。允许对稍后出现在表定义中的列进行前向引用。

  • 列约束出现在列定义中，只能引用该列。

考虑这个表定义：

CREATE TABLE t1
(
  CHECK (c1 <> c2),
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);

定义包括命名和未命名格式的表约束和列约束：

  • 第一个约束��表约束：它出现在任何列定义之外，因此可以（并且确实）引用多个表列。此约束包含对尚未定义的列的前向引用。未指定约束名称，因此 MySQL 生成一个名称。

  • 接下来的三个约束是列约束：每个出现在列定义中，因此只能引用正在定义的列。其中一个约束明确命名。MySQL 为另外两个生成名称。

  • 最后两个约束是表约束。其中一个明确命名。MySQL 为另一个生成一个名称。

如前所述，MySQL 为未指定名称的任何CHECK约束生成一个名称。要查看前述表定义生成的名称，请使用SHOW CREATE TABLE：

mysql> SHOW CREATE TABLE t1\G
*************************** 1\. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
  CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),
  CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),
  CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SQL 标准规定所有类型的约束（主键，唯一索引，外键，检查）属于同一命名空间。在 MySQL 中，每种约束类型在每个模式（数据库）中都有自己的命名空间。因此，CHECK约束名称必须在每个模式中是唯一的；同一模式中的两个表不能共享CHECK约束名称。（例外情况：TEMPORARY表隐藏了同名的非TEMPORARY表，因此它也可以具有相同的CHECK约束名称。）

以表名开头生成的约束名称有助于确保模式的唯一性，因为表名在模式内也必须是唯一的。

CHECK条件表达式必须遵守以下规则。如果表达式包含不允许的结构，则会发生错误。

  • 允许非生成和生成列，除了具有AUTO_INCREMENT属性的列和其他表中的列。

  • 允许使用文字，确定性内置函数和运算符。如果给定表中的数据相同，则函数是确定性的，多次调用会产生相同的结果，与连接的用户无关。不符合此定义的函数的示例包括：CONNECTION_ID()，CURRENT_USER()，NOW()。

  • 不允许存储函数和可加载函数。

  • 不允许存储过程和函数参数。

  • 变量（系统变量，用户定义变量和存储程序本地变量）是不允许的。

  • 不允许子查询。

外键参照操作（ON UPDATE，ON DELETE）在用于CHECK约束的列上是被禁止的。同样，CHECK约束在用于外键参照操作的列上也是被禁止的。

CHECK约束会在INSERT，UPDATE，REPLACE，LOAD DATA和LOAD XML语句中进行评估，如果约束评估为FALSE，则会发生错误。如果发生错误，已应用更改的处理方式对于事务性和非事务性存储引擎有所不同，并且还取决于是否启用了严格的 SQL 模式，如严格的 SQL 模式中所述。

CHECK约束会在INSERT IGNORE，UPDATE IGNORE，LOAD DATA ... IGNORE和LOAD XML ... IGNORE语句中进行评估，如果约束评估为FALSE，则会发出警告。对于任何违反约束的行，插入或更新将被跳过。

如果约束表达式评估为与声明的列类型不同的数据类型，则根据通常的 MySQL 类型转换规则发生对声明类型的隐式强制转换。请参阅第 14.3 节，“表达式评估中的类型转换”。如果类型转换失败或导致精度丢失，则会发生错误。

注意

约束表达式评估在评估时使用当前的 SQL 模式。如果表达式的任何组件依赖于 SQL 模式，则除非在所有使用期间 SQL 模式相同，否则对表的不同使用可能导致不同的结果。

信息模式CHECK_CONSTRAINTS表提供有关在表上定义的 CHECK 约束的信息。请参阅第 28.3.5 节，“INFORMATION_SCHEMA CHECK_CONSTRAINTS 表”。

原文：dev.mysql.com/doc/refman/8.0/en/silent-column-changes.html

15.1.20.7 悄悄更改列规范

在某些情况下，MySQL 会悄悄地更改列规范，使其与 CREATE TABLE 或 ALTER TABLE 语句中给出的规范不同。这些更改可能是对数据类型的更改，对与数据类型相关联的属性的更改，或对索引规范的更改。

所有更改都受到 65,535 字节的内部行大小限制的影响，这可能导致某些数据类型更改尝试失败。参见 第 10.4.7 节，“表列数和行大小限制”。

  • PRIMARY KEY 的一部分列即使没有声明为 NOT NULL 也会被设置为 NOT NULL。

  • 在创建表时，ENUM 和 SET 成员值的尾随空格会被自动删除。

  • MySQL 将其他 SQL 数据库供应商使用的某些数据类型映射到 MySQL 类型。参见 第 13.9 节，“使用其他数据库引擎的数据类型”。

  • 如果您包含一个 USING 子句来指定对于给定存储引擎不允许的索引类型，但是有另一种可用的索引类型，该引擎可以在不影响查询结果的情况下使用该索引类型��

  • 如果未启用严格的 SQL 模式，则长度大于 65535 的 VARCHAR 列会转换为 TEXT，长度大于 65535 的 VARBINARY 列会转换为 BLOB。否则，在这两种情况下都会发生错误。

  • 为字符数据类型指定 CHARACTER SET binary 属性会导致列被创建为相应的二进制数据类型：CHAR 变为 BINARY，VARCHAR 变为 VARBINARY，TEXT 变为 BLOB。对于 ENUM 和 SET 数据类型，不会发生这种情况；它们会按照声明创建。假设您使用以下定义指定表：

    CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET binary,
  c2 TEXT CHARACTER SET binary,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

    结果表的定义如下：

    CREATE TABLE t
(
  c1 VARBINARY(10),
  c2 BLOB,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

若要查看 MySQL 是否使用了您指定之外的数据类型，请在创建或更改表后发出DESCRIBE或SHOW CREATE TABLE语句。

如果使用myisampack对表进行压缩，可能会发生某些其他数据类型的更改。请参阅第 18.2.3.3 节，“压缩表特性”。

avatar
文章
阅读
粉丝