13.1.18 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 示 table 的文件数量有所限制。各个存储引擎可能会强加特定于引擎的约束。 InnoDB允许多达 40 亿张桌子。

有关 table 的物理 table 示的信息,请参见第 13.1.18.1 节“由 CREATE TABLE 创建的文件”

CREATE TABLE语句有多个方面,本节以下主题对此进行了介绍:

Table Name

  • tbl_name

可以将 table 名指定为* db_name.tbl_name *,以在特定数据库中创建 table。不管是否存在默认数据库(假定数据库存在),此方法都有效。如果使用带引号的标识符,请分别为数据库和 table 名加上引号。例如,Importing“ mydb.mytbl”,而不是“ mydb.mytbl”。

第 9.2 节“架构对象名称”中给出了允许的 table 名的规则。

  • IF NOT EXISTS

如果 table 存在,则防止发生错误。但是,没有验证现有 table 具有与CREATE TABLE语句指示的结构相同的结构。

Temporary Tables

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

table 克隆和复制

  • LIKE

使用CREATE TABLE ... LIKE根据另一个 table 的定义创建一个空 table,包括在原始 table 中定义的所有列属性和索引:

CREATE TABLE new_tbl LIKE orig_tbl;

有关更多信息,请参见第 13.1.18.3 节“ CREATE TABLE ... LIKE 语句”

  • [AS] query_expression

要从另一个 table 创建一个 table,请在CREATE TABLE语句的末尾添加一个SELECT语句:

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

有关更多信息,请参见第 13.1.18.4 节“ CREATE TABLE ... SELECT 语句”

  • IGNORE | REPLACE

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

有关更多信息,请参见第 13.1.18.4 节“ CREATE TABLE ... SELECT 语句”

列数据类型和属性

每个 table 有 4096 列的硬限制,但是对于给定的 table,有效最大值可能更少,并且取决于第 8.4.7 节“table 列数和行大小的限制”中讨论的因素。

  • data_type
  • data_type *table 示列定义中的数据类型。有关可用于指定列数据类型的语法的完整说明,以及有关每种类型的属性的信息,请参见第 11 章,数据类型
  • 某些属性并不适用于所有数据类型。 AUTO_INCREMENT仅适用于整数和浮点类型。 DEFAULT不适用于BLOBTEXTGEOMETRYJSON类型。

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

MySQL 5.7 在字符的字符列定义中解释长度规范。 BINARYVARBINARY的长度以字节为单位。

  • 对于CHARVARCHARBINARYVARBINARY列,可以使用col_name(length)语法指定索引前缀长度,以创建仅使用列值开头的索引。 BLOBTEXT列也可以构建索引,但必须提供前缀长度*。对于非二进制字符串类型,前缀长度以字符形式给出;对于二进制字符串类型,前缀长度以字节形式给出。也就是说,索引条目由CHARVARCHARTEXT列的每个列值的前* length 个字符以及BINARYVARBINARYBLOB列的每个列值的前 length *个字节组成。像这样仅索引列值的前缀可以使索引文件小得多。有关索引前缀的其他信息,请参见第 13.1.14 节“ CREATE INDEX 语句”

InnoDBMyISAM存储引擎支持BLOBTEXT列上的索引。例如:

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

从 MySQL 5.7.17 开始,如果指定的索引前缀超过了最大列数据类型的大小,则CREATE TABLE如下处理索引:

  • 对于非唯一索引,将发生错误(如果启用了严格的 SQL 模式),或者将索引长度减小到最大列数据类型大小之内,并且会产生警告(如果未启用严格的 SQL 模式)。

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

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

  • NOT NULL | NULL

如果未指定NULLNOT NULL,则该列将被视为已指定NULL

在 MySQL 5.7 中,只有InnoDBMyISAMMEMORY存储引擎支持可以具有NULL值的列上的索引。在其他情况下,必须将索引列声明为NOT NULL或错误结果。

  • DEFAULT

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

如果启用了NO_ZERO_DATENO_ZERO_IN_DATE SQL 模式,并且根据该模式设置的日期值默认值不正确,则如果未启用严格 SQL 模式,则CREATE TABLE会产生警告,而如果启用严格模式,则会产生错误。例如,启用NO_ZERO_IN_DATE时,c1 DATE DEFAULT '2010-00-00'会产生警告。

  • AUTO_INCREMENT

整数或浮点列可以具有附加属性AUTO_INCREMENT。在索引AUTO_INCREMENT列中插入NULL(推荐)或0的值时,该列将设置为下一个序列值。通常,这是value+1,其中* value *是 table 中当前列的最大值。 AUTO_INCREMENT序列以1开头。

要在插入行后检索AUTO_INCREMENT值,请使用LAST_INSERT_ID() SQL 函数或mysql_insert_id() C API 函数。参见第 12.15 节“信息功能”第 27.7.6.38 节“ mysql_insert_id()”

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

每个 table 只能有一个AUTO_INCREMENT列,必须对其进行索引,并且不能具有DEFAULT值。 AUTO_INCREMENT列仅包含正值时才能正常工作。插入负数被视为插入非常大的正数。这样做是为了避免数字从正数“换”为负数时出现精度问题,并确保您不会偶然得到包含0AUTO_INCREMENT列。

对于MyISAMtable,您可以在多列键中指定AUTO_INCREMENT辅助列。参见第 3.6.9 节“使用 AUTO_INCREMENT”

为了使 MySQL 与某些 ODBC 应用程序兼容,可以使用以下查询为最后插入的行找到AUTO_INCREMENT值:

SELECT * FROM tbl_name WHERE auto_col IS NULL

此方法要求sql_auto_is_null变量未设置为 0.请参见第 5.1.7 节“服务器系统变量”

有关InnoDBAUTO_INCREMENT的信息,请参见第 14.6.1.6 节“ InnoDB 中的 AUTO_INCREMENT 处理”。有关AUTO_INCREMENT和 MySQL 复制的信息,请参见第 16.4.1.1 节“复制和 AUTO_INCREMENT”

  • COMMENT

列的 Comments 可以使用COMMENT选项指定,最长为 1024 个字符。该 Comments 由显示创建 table显示全栏语句显示。

  • COLUMN_FORMAT

在 NDB 群集中,也可以使用COLUMN_FORMATNDBtable 的各个列指定数据存储格式。允许的列格式为FIXEDDYNAMICDEFAULTFIXED用于指定固定宽度存储,DYNAMIC允许列为可变宽度,DEFAULT导致列使用固定宽度或可变宽度存储,具体取决于列的数据类型(可能被ROW_FORMAT指定符覆盖) 。

从 MySQL NDB Cluster 7.5.4 开始,对于NDB个 table,COLUMN_FORMAT的默认值为FIXED。 (在 MySQL NDB Cluster 7.5.1 中,默认设置已切换为DYNAMIC,但此更改已恢复,以保持与现有 GA 版本系列的向后兼容性.)(错误#24487363)

在 NDB 群集中,用COLUMN_FORMAT=FIXED定义的列的最大可能偏移为 8188 字节。有关更多信息和可能的解决方法,请参阅第 21.1.7.5 节“与 NDB 群集中的数据库对象相关联的限制”

COLUMN_FORMAT当前对使用NDB以外的存储引擎的 table 的列无效。在 MySQL 5.7 和更高版本中,COLUMN_FORMAT被静默忽略。

  • STORAGE

对于NDBtable,可以使用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个 table,STORAGE DEFAULT等效于STORAGE MEMORY

STORAGE子句对使用除NDB以外的存储引擎的 table 不起作用。 NDB Cluster 随附的mysqld版本仅支持STORAGE关键字;在任何其他版本的 MySQL 中都无法识别它,其中使用STORAGE关键字的任何尝试都会导致语法错误。

  • GENERATED ALWAYS

用于指定生成的列 table 达式。有关generated columns的信息,请参见第 13.1.18.7 节“创建 table 和生成的列”

存储的生成列可以被索引。 InnoDB支持虚拟生成的列上的二级索引。参见第 13.1.18.8 节“辅助索引和生成的列”

索引和外键

几个关键字适用于创建索引和外键。有关以下描述的一般背景,请参见第 13.1.14 节“ CREATE INDEX 语句”第 13.1.18.5 节“外键约束”

  • CONSTRAINT symbol

可以提供CONSTRAINT symbol子句以命名约束。如果未提供该子句,或者在CONSTRAINT关键字后不包含* symbol *,则 MySQL 将自动生成约束名称,但以下情况除外。 * symbol 值(如果使用)对于每种模式(数据库)和每种约束类型必须是唯一的。重复 symbol *导致错误。另请参阅有关在第 9.2.1 节“标识符长度限制”处生成的约束标识符的长度限制的讨论。

Note

如果在外键定义中未提供CONSTRAINT symbol子句,或者在CONSTRAINT关键字后未包含* symbol *,则NDB使用外键索引名。

SQL 标准指定所有类型的约束(主键,唯一索引,外键,检查)都属于同一名称空间。在 MySQL 中,每个约束类型每个模式都有其自己的名称空间。因此,每种约束类型的名称在每个架构中必须唯一。

  • PRIMARY KEY

唯一索引,其中所有键列必须定义为NOT NULL。如果未将它们显式声明为NOT NULL,则 MySQL 会如此隐式(无声地)声明它们。一个 table 只能有一个PRIMARY KEYPRIMARY KEY的名称始终为PRIMARY,因此不能用作任何其他种类的索引的名称。

如果您没有PRIMARY KEY并且应用程序在 table 中要求PRIMARY KEY,则 MySQL 返回的第一个UNIQUE索引将没有NULL列作为PRIMARY KEY

InnoDBtable 中,请将PRIMARY KEY保持较短,以最大程度地减少辅助索引的存储开销。每个辅助索引条目都包含对应行的主键列的副本。 (请参阅第 14.6.2.1 节“群集索引和二级索引”。)

在创建的 table 中,首先放置PRIMARY KEY,然后放置所有UNIQUE索引,然后放置非唯一索引。这有助于 MySQL 优化器确定使用哪个索引的优先级,还可以更快地检测到重复的UNIQUE键。

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

如果 table 的PRIMARY KEYUNIQUE NOT NULL索引由具有整数类型的单个列组成,则可以使用_rowid来引用SELECT语句中的索引列,如Unique Indexes中所述。

在 MySQL 中,PRIMARY KEY的名称为PRIMARY。对于其他索引,如果未分配名称,则为索引分配与第一个索引列相同的名称,并带有可选的后缀(_2_3...)以使其唯一。您可以使用SHOW INDEX FROM tbl_name查看 table 的索引名称。参见第 13.7.5.22 节,“ SHOW INDEX 语句”

  • KEY | INDEX

KEY通常是INDEX的同义词。在列定义中给出键属性PRIMARY KEY时,也可以仅将其指定为KEY。这样做是为了与其他数据库系统兼容。

  • UNIQUE

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

如果 table 的PRIMARY KEYUNIQUE NOT NULL索引由具有整数类型的单个列组成,则可以使用_rowid来引用SELECT语句中的索引列,如Unique Indexes中所述。

  • FULLTEXT

FULLTEXT索引是用于全文搜索的特殊索引类型。仅InnoDBMyISAM存储引擎支持FULLTEXT索引。只能从CHARVARCHARTEXT列创建它们。索引总是在整个列上进行;不支持列前缀索引,并且如果指定则忽略任何前缀长度。有关操作的详细信息,请参见第 12.9 节“全文搜索功能”。如果全文索引和搜索操作需要特殊处理,则可以将WITH PARSER子句指定为* index_option *值,以将解析器插件与索引关联。此子句仅对FULLTEXT索引有效。 InnoDBMyISAM都支持全文分析器插件。有关更多信息,请参见全文解析器插件第 28.2.4.4 节“编写全文分析器插件”

  • SPATIAL

您可以在空间数据类型上创建SPATIAL索引。仅MyISAMInnoDBtable 支持空间类型,并且索引列必须声明为NOT NULL。参见第 11.4 节“空间数据类型”

  • FOREIGN KEY

MySQL 支持外键和外键约束,这些外键使您可以跨 table 交叉引用相关数据,外键约束则有助于使这些扩展数据保持一致。有关定义和选项的信息,请参见reference_definitionreference_option

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

  • CHECK

CHECK子句已解析,但被所有存储引擎忽略。

  • key_part

    • key_part *规范可以以ASCDESC结尾。这些关键字允许将来用于指定升序或降序索引值存储的扩展。目前,它们已被解析但被忽略;索引值始终按升序存储。
    • 由* length *属性定义的前缀对于InnoDBtable 最长为 767 个字节,如果启用了innodb_large_prefix选项,则最长为 3072 字节。对于MyISAMtable,前缀长度限制为 1000 个字节。

前缀限制以字节为单位。但是,对于CREATE TABLEALTER TABLECREATE INDEX语句中的索引规范,前缀* length *被解释为非二进制字符串类型(CHARVARCHARTEXT)的字符数和二进制字符串类型(BINARYVARBINARYBLOB)的字节数。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。

  • index_type

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

Example:

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

USING的首选位置在索引列列 table 之后。可以在列列 table 之前给出它,但是不赞成在该位置使用该选项,并且在以后的 MySQL 版本中将删除该支持。

  • index_option
  • index_option *值指定索引的其他选项。
  • KEY_BLOCK_SIZE

对于MyISAM个 table,KEY_BLOCK_SIZE(可选)以字节为单位指定用于索引键块的大小。该值被视为提示;如有必要,可以使用其他大小。为单个索引定义指定的KEY_BLOCK_SIZE值将覆盖 table 级KEY_BLOCK_SIZE的值。

有关 table 级KEY_BLOCK_SIZE属性的信息,请参见Table Options

  • WITH PARSER

WITH PARSER选项只能与FULLTEXT索引一起使用。如果全文索引和搜索操作需要特殊处理,它将解析器插件与索引关联。 InnoDBMyISAM都支持全文分析器插件。如果您具有MyISAMtable 以及关联的全文分析器插件,则可以使用ALTER TABLE将 table 转换为InnoDB

  • COMMENT

在 MySQL 5.7 中,索引定义可以包含最多 1024 个字符的可选 Comments。

您可以使用index_option COMMENT子句为单个索引设置InnoDB MERGE_THRESHOLD的值。参见第 14.8.12 节“为索引页面配置合并阈值”

有关允许的index_option值的更多信息,请参见第 13.1.14 节“ CREATE INDEX 语句”。有关索引的更多信息,请参见第 8.3.1 节“ MySQL 如何使用索引”

  • reference_definition

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

InnoDBNDBtable 支持检查外键约束。被引用 table 的列必须始终明确命名。支持外键上的ON DELETEON UPDATE操作。有关更多详细信息和示例,请参见第 13.1.18.5 节“外键约束”

对于其他存储引擎,MySQL Server 会解析并忽略CREATE TABLE语句中的FOREIGN KEYREFERENCES语法。参见第 1.8.2.3 节“外键约束差异”

Important

对于熟悉 ANSI/ISO SQL 标准的用户,请注意,没有存储引擎(包括InnoDB)可以识别或强制执行引用完整性约束定义中使用的MATCH子句。使用显式的MATCH子句将不会具有指定的效果,并且还会导致ON DELETEON UPDATE子句被忽略。由于这些原因,应避免指定MATCH

SQL 标准中的MATCH子句控制与主键进行比较时如何处理复合(多列)外键中的NULL值。 InnoDB本质上实现了MATCH SIMPLE定义的语义,该语义允许外键全部或部分为NULL。在这种情况下,允许插入包含此类外键的(子 table)行,并且该行与引用的(父)table 中的任何行都不匹配。使用触发器可以实现其他语义。

另外,MySQL 要求对引用的列进行索引以提高性能。但是,InnoDB并不强制要求将引用的列声明为UNIQUENOT NULL。对于诸如UPDATEDELETE CASCADE之类的操作,未很好地定义对非唯一键或包含NULL值的键的外键引用的处理。建议您使用仅引用UNIQUE(或PRIMARY)和NOT NULL的外键。

MySQL 解析但忽略“内联REFERENCES规范”(如 SQL 标准所定义),其中引用被定义为列规范的一部分。 MySQL 仅在被指定为单独的FOREIGN KEY规范的一部分时才接受REFERENCES子句。

  • reference_option

有关RESTRICTCASCADESET NULLNO ACTIONSET DEFAULT选项的信息,请参见第 13.1.18.5 节“外键约束”

Table Options

table 选项用于优化 table 的行为。在大多数情况下,您无需指定任何一个。除非另有说明,否则这些选项适用于所有存储引擎。不适用于给定存储引擎的选项可以被接受并记住作为 table 定义的一部分。如果您以后使用ALTER TABLE将 table 转换为使用其他存储引擎,则将应用这些选项。

  • ENGINE

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

Storage EngineDescription
InnoDB具有行锁定和外键的事务安全 table。新 table 的默认存储引擎。如果您有 MySQL 经验,但不熟悉InnoDB,请参见第 14 章,InnoDB 存储引擎,尤其是第 14.1 节“ InnoDB 简介”
MyISAM二进制便携式存储引擎,主要用于只读或以只读为主的工作负载。参见第 15.2 节“ MyISAM 存储引擎”
MEMORY该存储引擎的数据仅存储在内存中。参见第 15.3 节“ MEMORY 存储引擎”
CSV以逗号分隔值格式存储行的 table。参见第 15.4 节“ CSV 存储引擎”
ARCHIVE归档存储引擎。参见第 15.5 节“ ARCHIVE 存储引擎”
EXAMPLE示例引擎。参见第 15.9 节“示例存储引擎”
FEDERATED访问远程 table 的存储引擎。参见第 15.8 节“联邦存储引擎”
HEAP这是MEMORY的同义词。
MERGEMyISAM个 table 的集合用作一个 table。也称为MRG_MyISAM。参见第 15.7 节“ MERGE 存储引擎”
NDB群集的,基于内存的容错 table,支持事务和外键。也称为NDBCLUSTER。参见第 21 章,MySQL NDB 群集 7.5 和 NDB 群集 7.6

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

  • AUTO_INCREMENT

table 格的初始AUTO_INCREMENT值。在 MySQL 5.7 中,这适用于MyISAMMEMORYInnoDBARCHIVEtable。要为不支持AUTO_INCREMENTtable 选项的引擎设置第一个自动增量值,请在创建 table 后插入“虚拟”行,其值比所需值小一,然后删除虚拟行。

对于在CREATE TABLE语句中支持AUTO_INCREMENTtable 选项的引擎,您还可以使用ALTER TABLE tbl_name AUTO_INCREMENT = N重置AUTO_INCREMENT值。该值不能设置为低于该列中当前的最大值。

  • AVG_ROW_LENGTH

table 的平均行长的近似值。您仅需要为具有可变大小行的大型 table 设置此选项。

创建MyISAMtable 时,MySQL 使用MAX_ROWSAVG_ROW_LENGTH选项的乘积来确定结果 table 的大小。如果您未指定任何选项,则MyISAM数据和索引文件的最大大小默认为 256TB。 (如果 os 不支持那么大的文件,则 table 的大小受文件大小限制的约束.)如果要减小指针大小以使索引变小和变快,而实际上并不需要大文件,则可以可以通过设置myisam_data_pointer_size系统变量来减小默认指针大小。 (请参阅第 5.1.7 节“服务器系统变量”。)如果希望所有 table 都可以超过默认限制,并且希望 table 的速度稍慢一些且比必要的要大,则可以通过设置此变量来增加默认指针的大小。将该值设置为 7 将允许 table 最大为 65,536TB。

  • [DEFAULT] CHARACTER SET

指定 table 的默认字符集。 CHARSETCHARACTER SET的同义词。如果字符集名称为DEFAULT,则使用数据库字符集。

  • CHECKSUM

如果您希望 MySQL 维护所有行的实时校验和(即,当 table 更改时 MySQL 自动更新的校验和),请将其设置为 1.这使 table 的更新速度稍慢,但也更容易找到损坏的 table。 CHECKSUM TABLE语句报告校验和。 (仅MyISAM.)

  • [DEFAULT] COLLATE

指定 table 的默认排序规则。

  • COMMENT

table 格的 Comments,最多 2048 个字符。

您可以使用table_option COMMENT子句为 table 设置InnoDB MERGE_THRESHOLD的值。参见第 14.8.12 节“为索引页面配置合并阈值”

设置 NDB_TABLE 选项. 在 MySQL NDB Cluster 7.5.2 和更高版本中,CREATE TABLEALTER TABLE语句中的 tableComments 还可用于指定NDB_TABLE选项NOLOGGINGREAD_BACKUPPARTITION_BALANCEFULLY_REPLICATED中的一到四个。作为一组名称/值对,如果需要,用逗号分隔,紧跟在引号 Comments 文本开头的字符串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";

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

Comments 将显示为显示创建 table的输出的一部分。Comments 的文本也可以作为 MySQL Information Schema TABLEStable 的 TABLE_COMMENT 列使用。

NDB个 table 的ALTER TABLE语句也支持此 Comments 语法。请记住,与ALTER TABLE一起使用的 tableComments 将替换该 table 以前可能具有的所有现有 Comments。

NDBtable 不支持在 tableComments 中设置MERGE_THRESHOLD选项(将被忽略)。

有关完整的语法信息和示例,请参见第 13.1.18.9 节“设置 NDB_TABLE 选项”

  • COMPRESSION

用于InnoDB个 table 的页面级压缩的压缩算法。支持的值包括ZlibLZ4None。透明页面压缩功能引入了COMPRESSION属性。仅位于file-per-tabletable 空间中的InnoDBtable 支持页面压缩,并且仅在支持稀疏文件和打孔的 Linux 和 Windows 平台上可用。有关更多信息,请参见第 14.9.2 节“ InnoDB 页面压缩”

  • CONNECTION

FEDERATEDtable 的连接字符串。

Note

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

  • DATA DIRECTORY , INDEX DIRECTORY

对于InnoDBDATA DIRECTORY='directory'子句允许在数据目录之外创建 table。必须启用innodb_file_per_table变量才能使用DATA DIRECTORY子句。必须指定完整目录路径。有关更多信息,请参见第 14.6.1.2 节“在外部创建 table”

创建MyISAMtable 时,可以使用DATA DIRECTORY='directory'子句,INDEX DIRECTORY='directory'子句或同时使用这两个子句。它们指定分别放置MyISAMtable 的数据文件和索引文件的位置。与InnoDBtable 不同,在创建带有DATA DIRECTORYINDEX DIRECTORY选项的MyISAMtable 时,MySQL 不会创建与数据库名称相对应的子目录。在指定的目录中创建文件。

从 MySQL 5.7.17 开始,您必须具有FILE特权才能使用DATA DIRECTORYINDEX DIRECTORY table 选项。

Important

对于分区 table,将忽略 table 级别的DATA DIRECTORYINDEX DIRECTORY选项。错误 32091)

这些选项仅在不使用--skip-symbolic-links选项时有效。您的 os 还必须有一个有效的线程安全realpath()调用。有关更多完整信息,请参见第 8.12.3.2 节,“在 Unix 上为 MyISAMtable 使用符号链接”

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

如果使用DATA DIRECTORYINDEX DIRECTORY选项创建了MyISAMtable,并且找到了现有的.MYD.MYI文件,则 MyISAM 总是返回错误。它不会覆盖指定目录中的文件。

Important

您不能使用包含DATA DIRECTORYINDEX DIRECTORY的 MySQL 数据目录的路径名。这包括分区 table 和单个 table 分区。 (请参阅 Bug#32167.)

  • DELAY_KEY_WRITE

如果要在关闭 table 之前延迟 table 的键更新,请将其设置为 1.请参阅第 5.1.7 节“服务器系统变量”中对delay_key_write系统变量的描述。 (仅MyISAM.)

  • ENCRYPTION

ENCRYPTION选项设置为'Y'以对在file-per-tabletable 空间中创建的InnoDBtable 启用页面级数据加密。选项值不区分大小写。 InnoDBtable 空间加密功能引入了ENCRYPTION选项;参见第 14.14 节“ InnoDB 静态数据加密”。必须先安装和配置keyring插件,然后才能启用加密。

  • INSERT_METHOD

如果要将数据插入MERGEtable,则必须用INSERT_METHOD指定应将行插入到的 table。 INSERT_METHOD是仅对MERGEtable 有用的选项。使用FIRSTLAST的值可将插入内容移到第一个或最后一个 table,或使用NO的值可防止插入。参见第 15.7 节“ MERGE 存储引擎”

  • KEY_BLOCK_SIZE

对于MyISAM个 table,KEY_BLOCK_SIZE(可选)以字节为单位指定用于索引键块的大小。该值被视为提示;如有必要,可以使用其他大小。为单个索引定义指定的KEY_BLOCK_SIZE值将覆盖 table 级KEY_BLOCK_SIZE的值。

对于InnoDB个 table,KEY_BLOCK_SIZE指定要用于compressed InnoDB个 table 的page大小(以千字节为单位)。 KEY_BLOCK_SIZE值被视为提示;如有必要,InnoDB可以使用其他大小。 KEY_BLOCK_SIZE只能小于或等于innodb_page_size值。值 0table 示默认的压缩页面大小,是innodb_page_size值的一半。取决于innodb_page_size,可能的KEY_BLOCK_SIZE值包括 0、1、2、4、8 和 16.有关更多信息,请参见第 14.9.1 节“ InnoDBtable 压缩”

Oracle 建议在为InnoDBtable 指定KEY_BLOCK_SIZE时启用innodb_strict_mode。启用innodb_strict_mode时,指定无效的KEY_BLOCK_SIZE值将返回错误。如果禁用innodb_strict_mode,则无效的KEY_BLOCK_SIZE值将导致警告,并且KEY_BLOCK_SIZE选项将被忽略。

响应显示 table 格状态Create_options列报告了最初指定的KEY_BLOCK_SIZE选项,而显示创建 table也是如此。

InnoDB仅在 table 级别支持KEY_BLOCK_SIZE

KEY_BLOCK_SIZE不支持 32KB 和 64KB innodb_page_size值。 InnoDBtable 压缩不支持这些页面大小。

  • MAX_ROWS

您计划在 table 中存储的最大行数。这不是硬性限制,而是向存储引擎提示 table 必须至少能够存储这么多行。

Important

从 NDB Cluster 7.5.4 开始,不建议使用MAX_ROWSNDBtable 来控制 table 分区数。为了向后兼容,它在更高版本中仍受支持,但将来的版本中可能会删除它。改用 PARTITION_BALANCE;参见设置 NDB_TABLE 选项

NDB存储引擎将此值视为最大值。如果计划创建非常大的 NDB 群集 table(包含数百万行),则应使用此选项来确保NDB通过设置MAX_ROWS = 2 * rows在散列 table 中分配足够数量的索引槽用于存储 table 主键的哈希,其中* rows *是您希望插入 table 中的行数。

MAX_ROWS最大值为 4294967295;较大的值将被截断到此限制。

  • MIN_ROWS

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

  • PACK_KEYS

仅对MyISAM个 table 生效。如果要使用较小的索引,请将此选项设置为 1.这通常会使更新速度变慢,读取速度也会加快。将选项设置为 0 将禁用所有键打包。将其设置为DEFAULT会告诉存储引擎仅打包长CHARVARCHARBINARYVARBINARY列。

如果不使用PACK_KEYS,则默认值为打包字符串,但不打包数字。如果您使用PACK_KEYS=1,数字也会被打包。

在打包二进制数字键时,MySQL 使用前缀压缩:

  • 每个密钥都需要一个额外的字节,以指示上一个密钥的多少个字节与下一个密钥相同。

    • 指向行的指针直接在键之后以高字节优先 Sequences 存储,以提高压缩率。

这意味着,如果在连续的两行上有许多相等的键,则所有后面的“相同”键通常仅占用两个字节(包括指向该行的指针)。将其与以下键取storage_size_for_key + pointer_size(指针大小通常为 4)的普通情况进行比较。相反,仅当您具有许多相同的数字时,您才能从前缀压缩中获得显着的好处。如果所有键完全不同,则如果该键不是可以具有NULL值的键,则每个键要多使用一个字节。 (在这种情况下,打包的密钥长度存储在用于标记密钥是否为NULL的同一字节中.)

  • PASSWORD

此选项未使用。如果您需要加密.frm文件并使它们无法用于任何其他 MySQL 服务器,请与我们的销售部门联系。

  • ROW_FORMAT

定义存储行的物理格式。

创建禁用了strict mode的 table 时,如果不支持指定的行格式,则使用存储引擎的默认行格式。响应显示 table 格状态,table 的实际行格式在Row_format列中报告。 Create_options列显示CREATE TABLE语句中指定的行格式,显示创建 table也是如此。

行格式选择取决于 table 所使用的存储引擎。

对于InnoDB个 table:

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

如果未定义ROW_FORMAT选项,或者如果使用ROW_FORMAT=DEFAULT,则重建 table 的操作还将无提示地将 table 的行格式更改为innodb_default_row_format定义的默认格式。有关更多信息,请参见定义 table 格的行格式

  • 为了更有效地InnoDB存储数据类型,尤其是BLOB类型,请使用DYNAMIC。有关DYNAMIC行格式的要求,请参见动态行格式

  • 要为InnoDB个 table 启用压缩,请指定ROW_FORMAT=COMPRESSED。有关与COMPRESSED行格式相关的要求,请参见第 14.9 节“ InnoDBtable 和页面压缩”

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

  • 当您指定非默认的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行格式的其他信息,请参见第 14.11 节“ InnoDB 行格式”

对于MyISAMtable,对于静态或可变长度的行格式,选项值可以为FIXEDDYNAMICmyisampack将类型设置为COMPRESSED。参见第 15.2.3 节“ MyISAMtable 存储格式”

对于NDBtable,MySQL NDB Cluster 7.5.1 和更高版本中的默认ROW_FORMATDYNAMIC。 (以前是FIXED.)

  • STATS_AUTO_RECALC

指定是否自动为InnoDBtable 重新计算persistent statistics。值DEFAULT使 table 的持久统计信息设置由innodb_stats_auto_recalc配置选项确定。值1导致 table 中 10%的数据已更改时重新计算统计信息。值0可防止对该 table 进行自动重新计算;使用此设置,对 table 进行实质性更改后,发出ANALYZE TABLE语句以重新计算统计信息。有关持久统计信息功能的更多信息,请参见第 14.8.11.1 节“配置持久性优化器统计参数”

  • STATS_PERSISTENT

指定是否为InnoDBtable 启用persistent statistics。值DEFAULT使 table 的持久统计信息设置由innodb_stats_persistent配置选项确定。值1启用 table 的持久统计信息,而值0则关闭此功能。通过CREATE TABLEALTER TABLE语句启用持久统计信息后,在将代 table 性数据加载到 table 中之后,发出ANALYZE TABLE语句以计算统计信息。有关持久统计信息功能的更多信息,请参见第 14.8.11.1 节“配置持久性优化器统计参数”

  • STATS_SAMPLE_PAGES

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

  • TABLESPACE

TABLESPACE子句可用于在现有的常规 table 空间,每 table 文件 table 空间或系统 table 空间中创建 table。

CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name

您指定的常规 table 空间在使用TABLESPACE子句之前必须存在。有关常规 table 空间的信息,请参见第 14.6.3.3 节“常规 table 空间”

tablespace_name是区分大小写的标识符。它可以被引用或不被引用。不允许使用正斜杠字符(“ /”)。以“ innodb_”开头的名称保留作特殊用途。

要在系统 table 空间中创建 table,请指定innodb_system作为 table 空间名称。

CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system

使用TABLESPACE [=] innodb_system,无论innodb_file_per_table设置如何,都可以将任何未压缩行格式的 table 放置在系统 table 空间中。例如,您可以使用TABLESPACE [=] innodb_system将具有ROW_FORMAT=DYNAMIC的 table 添加到系统 table 空间。

要在每个 table 文件 table 空间中创建一个 table,请指定innodb_file_per_table作为 table 空间名称。

CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table

Note

如果启用了innodb_file_per_table,则无需指定TABLESPACE=innodb_file_per_table即可创建InnoDB每 table 文件 table 空间。启用innodb_file_per_table时,默认情况下在每 table 文件 table 空间中创建InnoDB个 table。

Note

MySQL 5.7.24 不支持在共享的InnoDBtable 空间中创建 table 分区,并且在将来的 MySQL 版本中将不再支持该功能。共享 table 空间包括InnoDB系统 table 空间和常规 table 空间。

DATA DIRECTORY子句与CREATE TABLE ... TABLESPACE=innodb_file_per_table一起允许,但不支持与TABLESPACE选项结合使用。

Note

从 MySQL 5.7.24 开始,不支持对带有创建临时 tableTABLESPACE = innodb_file_per_tableTABLESPACE = innodb_temporary子句的支持,并将在将来的 MySQL 版本中删除。

STORAGEtable 选项仅与NDBtable 一起使用。 STORAGE确定使用的存储类型(磁盘或内存),可以是DISKMEMORY

TABLESPACE ... STORAGE DISK将 table 分配给 NDB 群集磁盘数据 table 空间。该 table 空间必须已经使用CREATE TABLESPACE创建。有关更多信息,请参见第 21.5.10 节“ NDB 群集磁盘数据 table”

Important

没有TABLESPACE子句,不能在CREATE TABLE语句中使用STORAGE子句。

用于访问一个相同的MyISAMtable 的集合。仅适用于MERGE个 table。参见第 15.7 节“ MERGE 存储引擎”

您 Map 到MERGEtable 的 table 必须具有SELECTUPDATEDELETE特权。

Note

以前,所有使用的 table 必须与MERGEtable 本身位于同一数据库中。此限制不再适用。

Table Partitioning

  • partition_options *可用于控制用CREATE TABLE创建的 table 的分区。

并非本节开头* partition_options *语法中显示的所有选项都不适用于所有分区类型。请参阅以下各个类型的 Lists 以获取每种类型的特定信息,并参阅第 22 章,分区以获得有关 MySQL 分区工作方式和用途的更完整信息,以及 table 创建的其他示例以及与 MySQL 分区有关的其他语句。

分区可以被修改,合并,添加到 table 中以及从 table 中删除。有关完成这些任务的 MySQL 语句的基本信息,请参见第 13.1.8 节“ ALTER TABLE 语句”。有关更多详细的描述和示例,请参见第 22.3 节“分区 Management”

  • PARTITION BY

如果使用,则* partition_options 子句以PARTITION BY开头。此子句包含用于确定分区的函数;该函数返回一个整数值,范围是 1 到 num ,其中 num *是分区数。 (一个 table 可能包含的用户定义的分区的最大数量为 1024;该分区的最大数量包括了子分区的数量(本节稍后讨论)。)

Note

PARTITION BY子句中使用的 table 达式(* expr *)不能引用不在正在创建的 table 中的任何列;明确不允许使用此类引用,这些引用会导致语句失败并出现错误。 (缺陷#29444)

  • HASH(expr)

散列一个或多个列以创建用于放置和定位行的键。 * expr *是使用一个或多个 table 列的 table 达式。这可以是产生单个整数值的任何有效 MySQLtable 达式(包括 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 THANVALUES IN子句与PARTITION BY HASH一起使用。

PARTITION BY HASH使用* expr *的余数除以分区数(即模数)。有关示例和其他信息,请参见第 22.2.4 节“ HASH 分区”

LINEAR关键字需要一些不同的算法。在这种情况下,作为一个或多个逻辑AND运算的结果,计算存储行的分区的数量。有关线性哈希的讨论和示例,请参见第 22.2.4.1 节“线性哈希分区”

  • KEY(column_list)

这类似于HASH,除了 MySQL 提供散列功能以保证均匀的数据分布。 * column_list *参数只是一个 1 或多个 table 列的列 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 具有相同的效果。也就是说,使用&运算符而不是模数来找到分区号(有关详细信息,请参见第 22.2.4.1 节“线性哈希分区”第 22.2.5 节“密钥分区”)。本示例按键使用线性分区在 5 个分区之间分配数据:

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

[SUB]PARTITION BY [LINEAR] KEY支持ALGORITHM={1 | 2}选项。 ALGORITHM=1使服务器使用与 MySQL 5.1 相同的键哈希函数; ALGORITHM=2table 示服务器采用默认在 MySQL 5.5 及更高版本中为新的KEY分区 table 实现和使用的键哈希函数。 (使用 MySQL 5.5 和更高版本中使用的键哈希函数创建的分区 table 不能由 MySQL 5.1 服务器使用.)不指定该选项与使用ALGORITHM=2具有相同的效果。此选项主要用于在 MySQL 5.1 和更高版本的 MySQL 之间升级或降级[LINEAR] KEY分区 table 时,或在 MySQL 5.5 或更高版本的服务器上创建可以由KEYLINEAR KEY分区的 table 时,该 table 可以在 MySQL 5.1 服务器上使用。有关更多信息,请参见第 13.1.8.1 节,“ ALTER TABLE 分区操作”

MySQL 5.7(及更高版本)中的mysqldump将此选项写在版本 Comments 中,如下所示:

CREATE TABLE t1 (a INT)
/*!50100 PARTITION BY KEY */ /*!50611 ALGORITHM = 1 */ /*!50100 ()
      PARTITIONS 3 */

这将导致 MySQL 5.6.10 和更早版本的服务器忽略该选项,否则将导致这些版本中的语法错误。如果您打算将使用KEY进行分区或子分区的 table 加载到 MySQL 5.7 服务器上进行的转储,请将该数据库转储到 5.6.11 之前的 MySQL 5.6 服务器中,请确保先查阅MySQL 5.6 中的更改,然后再 continue。 (如果将包含由 MySQL 5.7(实际上是 5.6.11 或更高版本)服务器制作的KEY分区 table 或子分区 table 的转储加载到 MySQL 5.5.30 或更早版本的服务器中,则在此找到的信息也适用。)

同样在 MySQL 5.6.11 和更高版本中,在必要时在显示创建 table的输出中以与mysqldump相同的方式使用版本化 Comments 显示ALGORITHM=1ALGORITHM=2始终从SHOW CREATE TABLE输出中省略,即使在创建原始 table 时已指定此选项。

您不得将VALUES LESS THANVALUES IN子句与PARTITION BY KEY一起使用。

  • RANGE(expr)

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

Note

对于由RANGE分区的 table,必须将VALUES LESS THAN与整数 Literals 值或计算结果为单个整数值的 table 达式一起使用。在 MySQL 5.7 中,您可以在使用PARTITION BY RANGE COLUMNS定义的 table 中克服此限制,如本节稍后所述。

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

Partition Number:Years Range:
01990 及更早版本
11991 至 1994
21995 至 1998 年
31999 至 2002
42003 至 2005 年
52006 及更高版本

可以通过此处显示的CREATE TABLE语句来实现实现这种分区方案的 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部分相似的方式 Sequences 工作(在许多编程语言(如 C,Java 和 PHP 中都可以找到))。也就是说,子句的排列方式必须使每个连续VALUES LESS THAN中指定的上限大于前一个上限,而引用MAXVALUE的上限在列 table 中排在最后。

  • RANGE COLUMNS(column_list)

RANGE上的此变体有助于使用多个列上的范围条件(即具有诸如WHERE a = 1 AND b < 10WHERE a = 1 AND b = 10 AND c < 10之类的条件)对查询进行分区修剪。它使您可以通过使用COLUMNS子句中的列列 table 和每个PARTITION ... VALUES LESS THAN (value_list)分区定义子句中的一组列值来指定多列中的值范围。 (在最简单的情况下,此集合由单个列组成.)* column_list value_list *中可以引用的最大列数为 16.

COLUMNS子句中使用的* column_list *只能包含列名;列 table 中的每一列必须是以下 MySQL 数据类型之一:整数类型;字符串类型;和时间或日期列类型。不允许使用BLOBTEXTSETENUMBIT或空间数据类型的列;也不允许使用浮点数类型的列。您也不得在COLUMNS子句中使用函数或算术 table 达式。

分区定义中使用的VALUES LESS THAN子句必须为出现在COLUMNS()子句中的每一列指定 Literals 值;也就是说,每个VALUES LESS THAN子句使用的值列 table 必须包含与COLUMNS子句中列出的列数量相同的值。尝试在VALUES LESS THAN子句中使用比COLUMNS子句中更多或更少的值会导致该语句失败,并显示错误:使用列列 table 进行分区...不一致。对于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值列 table 中使用的每个值都必须与相应列的类型完全匹配;没有进行任何转换。例如,不能将字符串'1'用于与使用整数类型的列匹配的值(必须使用数字1代替),也不能将数字1用于与使用字符串类型的列匹配的值(在这种情况下,必须使用带引号的字符串:'1')。

有关更多信息,请参见第 22.2.1 节“ RANGE 分区”第 22.4 节“分区修剪”

  • LIST(expr)

当基于具有有限的可能值集(例如 State 或国家/locale 代码)的 table 列分配分区时,此功能很有用。在这种情况下,可以将与某个 State 或国家/locale 有关的所有行分配给单个分区,或者可以为某个 State 或国家/locale 的某个组保留一个分区。它类似于RANGE,不同之处在于仅VALUES IN可以用于指定每个分区的允许值。

VALUES IN与要匹配的值列 table 一起使用。例如,您可以创建如下分区方案:

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

使用列 table 分区时,必须使用VALUES IN定义至少一个分区。您不能将VALUES LESS THANPARTITION BY LIST结合使用。

Note

对于被LIST分区的 table,与VALUES IN一起使用的值列 table 必须仅由整数值组成。在 MySQL 5.7 中,您可以使用LIST COLUMNS进行分区来克服此限制,本节稍后将对此进行介绍。

  • LIST COLUMNS(column_list)

LIST上的此变体使用多个列上的比较条件(即具有WHERE a = 5 AND b = 5WHERE a = 1 AND b = 10 AND c = 5之类的条件)来简化查询的分区修剪。它使您可以通过使用COLUMNS子句中的列列 table 和每个PARTITION ... VALUES IN (value_list)分区定义子句中的一组列值来指定多列中的值。

关于LIST COLUMNS(column_list)中使用的列列 table 和VALUES IN(value_list)中使用的值列 table 的数据类型的规则分别与RANGE COLUMNS(column_list)VALUES LESS THAN(value_list)中使用的列列 table 的数据类型相同,除了VALUES IN子句MAXVALUE不允许,您可以使用NULL

PARTITION BY LIST一起使用时,与PARTITION BY LIST COLUMNS一起用于VALUES IN的值列 table 之间有一个重要区别。与PARTITION BY LIST COLUMNS一起使用时,VALUES IN子句中的每个元素都必须是一组*列值;每个集合中的值数必须与COLUMNS子句中使用的列数相同,并且这些值的数据类型必须与列的数据类型匹配(并以相同的 Sequences 出现)。在最简单的情况下,集合由单个列组成。 * column_list 和组成 value_list *的元素中可以使用的最大列数为 16.

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

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子句声明的任何分区的总数。

Note

在创建被RANGELIST分区的 table 时,无论是否使用PARTITIONS子句,都必须在 table 定义中至少包含一个PARTITION VALUES子句(请参见下文)。

  • SUBPARTITION BY

分区可以可选地划分为多个子分区。这可以通过使用可选的SUBPARTITION BY子句来指示。可以通过HASHKEY进行分区。这些都可以是LINEAR。这些工作方式与先前针对等效分区类型所述的方式相同。 (无法通过LISTRANGE进行子分区.)

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

  • 严格检查PARTITIONSSUBPARTITIONS子句中使用的值,并且该值必须遵守以下规则:

  • 该值必须是一个非零的正整数。

    • 不允许前导零。

    • 该值必须是整数 Literals,并且不能为 table 达式。例如,即使0.2E+01计算为2,也不允许PARTITIONS 0.2E+01。错误 15890)

  • partition_definition

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

  • PARTITION partition_name

指定分区的逻辑名。

  • VALUES

对于范围分区,每个分区必须包含VALUES LESS THAN子句;对于列 table 分区,必须为每个分区指定一个VALUES IN子句。这用于确定哪些行要存储在此分区中。有关语法示例,请参见第 22 章,分区中有关分区类型的讨论。

  • [STORAGE] ENGINE

分区处理程序为PARTITIONSUBPARTITION接受[STORAGE] ENGINE选项。当前,唯一可以使用此方法的方法是将所有分区或所有子分区设置为同一存储引擎,并且尝试为同一 table 中的分区或子分区设置不同的存储引擎将产生错误 ERROR 1469( HY000):此版本的 MySQL 中不允许在分区中混合使用处理程序。我们希望在将来的 MySQL 版本中取消对分区的限制。

  • COMMENT

可选的COMMENT子句可用于指定描述分区的字符串。例:

COMMENT = 'Data for the years previous to 1999'

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

  • DATA DIRECTORYINDEX DIRECTORY

DATA DIRECTORYINDEX DIRECTORY可用于指示目录,该分区的数据和索引将分别存储在该目录中。 data_dirindex_dir都必须是绝对系统路径名。

从 MySQL 5.7.17 开始,您必须具有FILE特权才能使用DATA DIRECTORYINDEX DIRECTORY分区选项。

Example:

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 DIRECTORYINDEX DIRECTORY的行为与MyISAMtable 中使用的CREATE TABLE语句的* table_option *子句相同。

每个分区可以指定一个数据目录和一个索引目录。如果未指定,则默认情况下,数据和索引存储在 table 的数据库目录中。

在 Windows 上,MyISAMtable 的单个分区或子分区不支持DATA DIRECTORYINDEX DIRECTORY选项,而InnoDBtable 的单个分区或子分区不支持INDEX DIRECTORY选项。在 Windows 上将忽略这些选项,但会生成警告。错误 30459)

Note

如果NO_DIR_IN_CREATE有效,则忽略DATA DIRECTORYINDEX DIRECTORY选项以创建分区 table。错误 24633)

  • MAX_ROWSMIN_ROWS

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

  • TABLESPACE

可用于为分区指定 table 空间。受 NDB 群集支持。对于InnoDB个 table,可以通过指定 TABLESPACE``来为分区指定每 table 文件 table 空间。所有分区必须属于同一存储引擎。

Note

MySQL 5.7.24 中弃用了将InnoDBtable 分区放置在共享InnoDBtable 空间中的支持,并且在将来的 MySQL 版本中将不再支持。共享 table 空间包括InnoDB系统 table 空间和常规 table 空间。

  • subpartition_definition

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

子分区必须由HASHKEY完成,并且只能在RANGELIST分区上进行。参见第 22.2.6 节“子分区”

按生成的列进行分区

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

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

分区会将生成的列视为常规列,从而可以通过变通方法来限制分区所不允许的功能(请参见第 22.6.3 节“与功能有关的分区限制”)。前面的示例演示了此技术:EXP()不能直接在PARTITION BY子句中使用,但是允许使用EXP()定义生成的列。