文档主页
MySQL 9.0 参考手册
相关文档 下载此手册
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
手册页 (TGZ) - 258.2Kb
手册页 (Zip) - 365.3Kb
信息 (Gzip) - 4.0Mb
信息 (Zip) - 4.0Mb


MySQL 9.0 参考手册  /  ...  /  CREATE TABLE 语句

15.1.20 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_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 语句包含几个方面,在本节的以下主题中进行了描述。

表名

  • 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 语句”

表克隆和复制

列数据类型和属性

每个表最多可以有 4096 列,但对于给定表,实际最大值可能更小,并且取决于 第 10.4.7 节“表列数和行大小限制” 中讨论的因素。

  • data_type

    data_type 代表列定义中的数据类型。有关指定列数据类型可用的语法的完整描述,以及有关每种类型属性的信息,请参阅 第 13 章“数据类型”

    • AUTO_INCREMENT 仅适用于整数类型。

    • 字符数据类型(CHARVARCHARTEXT 类型、ENUMSET 以及任何同义词)可以包含 CHARACTER SET 来指定列的字符集。 CHARSETCHARACTER SET 的同义词。可以使用 COLLATE 属性为字符集指定排序规则,以及任何其他属性。有关详细信息,请参阅 第 12 章“字符集、排序规则、Unicode”。示例

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

      MySQL 9.0 以字符形式解释字符列定义中的长度说明。 BINARYVARBINARY 的长度以字节为单位。

    • 对于 CHARVARCHARBINARYVARBINARY 列,可以使用 col_name(length) 语法来指定索引前缀长度,从而创建仅使用列值的前一部分的索引。 BLOBTEXT 列也可以被索引,但必须给出前缀长度。非二进制字符串类型的长度以字符为单位,二进制字符串类型的长度以字节为单位。也就是说,索引条目由每个列值的前 length 个字符组成,用于 CHARVARCHARTEXT 列,每个列值的前 length 个字节组成,用于 BINARYVARBINARYBLOB 列。这样只索引列值的前缀可以使索引文件更小。有关索引前缀的更多信息,请参阅 第 15.1.15 节“CREATE INDEX 语句”

      只有 InnoDBMyISAM 存储引擎支持对 BLOBTEXT 列进行索引。例如

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

      如果指定的索引前缀超过最大列数据类型大小,CREATE TABLE 会按如下方式处理索引

      • 对于非唯一索引,如果启用了严格 SQL 模式,则会发生错误;如果未启用严格 SQL 模式,则索引长度会减少以保持在最大列数据类型大小内,并会生成警告。

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

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

  • NOT NULL | NULL

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

    在 MySQL 9.0 中,只有 InnoDBMyISAMMEMORY 存储引擎支持对可以具有 NULL 值的列进行索引。在其他情况下,您必须将索引列声明为 NOT NULL,否则会导致错误。

  • DEFAULT

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

    如果启用了 NO_ZERO_DATENO_ZERO_IN_DATE SQL 模式,并且日期值的默认值不符合该模式,则如果未启用严格 SQL 模式,CREATE TABLE 会生成警告;如果启用了严格模式,则会生成错误。例如,在启用了 NO_ZERO_IN_DATE 的情况下,c1 DATE DEFAULT '2010-00-00' 会生成警告。

  • VISIBLEINVISIBLE

    指定列的可见性。如果未出现任何关键字,则默认值为 VISIBLE。表必须至少有一个可见列。尝试使所有列不可见会导致错误。有关详细信息,请参阅 第 15.1.20.10 节“不可见列”

  • 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 模式,则您可以在 AUTO_INCREMENT 列中将 0 存储为 0,而不会生成新的序列值。请参阅 第 7.1.11 节“服务器 SQL 模式”

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

    对于 MyISAM 表,您可以在多列键中指定一个 AUTO_INCREMENT 次要列。请参阅 第 5.6.9 节“使用 AUTO_INCREMENT”

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

    SELECT * FROM tbl_name WHERE auto_col IS NULL

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

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

  • COMMENT

    可以使用 COMMENT 选项为列指定注释,长度最多为 1024 个字符。注释将由 SHOW CREATE TABLESHOW FULL COLUMNS 语句显示。它还会在信息模式的 COLUMNS 表的 COLUMN_COMMENT 列中显示。

  • COLUMN_FORMAT

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

    对于 NDB 表,COLUMN_FORMAT 的默认值为 FIXED

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

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

  • ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE 选项用于指定主存储引擎和辅助存储引擎的列属性。这些选项为将来使用保留。

    分配给此选项的值是一个包含有效 JSON 文档或空字符串('')的字符串文字。无效 JSON 会被拒绝。

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

    ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE 值可以重复,不会出错。在这种情况下,使用最后指定的值。

    ENGINE_ATTRIBUTESECONDARY_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 集群一起提供的 mysqld 的构建中受支持;它在 MySQL 的任何其他版本中都无法识别,尝试使用 STORAGE 关键字会导致语法错误。

  • GENERATED ALWAYS

    用于指定生成的列表达式。有关 生成列 的信息,请参见 第 15.1.20.8 节,“CREATE TABLE 和生成列”.

    存储的生成列 可以被索引。 InnoDB 支持 虚拟生成列 的辅助索引。请参见 第 15.1.20.9 节,“辅助索引和生成列”.

索引、外键和 CHECK 约束

几个关键字适用于索引、外键和 CHECK 约束的创建。除了以下描述之外,有关一般背景信息,请参见 第 15.1.15 节,“CREATE INDEX 语句”第 15.1.20.5 节,“FOREIGN KEY 约束”第 15.1.20.6 节,“CHECK 约束”.

  • CONSTRAINT symbol

    CONSTRAINT symbol 子句可用于命名约束。如果未给出该子句或未在 CONSTRAINT 关键字后包含 symbol,MySQL 会自动生成一个约束名称,但有以下例外情况。如果使用 symbol 值,它必须在每个架构(数据库)中针对每种约束类型是唯一的。重复的 symbol 会导致错误。另请参见有关在 第 11.2.1 节,“标识符长度限制” 中生成的约束标识符的长度限制的讨论。

    注意

    如果在外国关键定义中没有给出 CONSTRAINT symbol 子句,或者在 CONSTRAINT 关键字后没有包含 symbol,MySQL 会自动生成一个约束名称。

    SQL 标准规定所有类型的约束(主键、唯一索引、外键、检查)都属于同一个命名空间。在 MySQL 中,每种约束类型在每个架构中都有自己的命名空间。因此,每种约束类型的名称必须在每个架构中是唯一的,但不同类型的约束可以具有相同的名称。

  • PRIMARY KEY

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

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

    InnoDB 表中,请将 PRIMARY KEY 保持简短,以最大程度地减少辅助索引的存储开销。每个辅助索引条目包含对应行的主键列的副本。(请参见 第 17.6.2.1 节,“聚簇索引和辅助索引”。)

    在创建的表中,PRIMARY KEY 位于第一位,其次是所有 UNIQUE 索引,然后是非唯一索引。这有助于 MySQL 优化器确定优先使用哪个索引,以及更快地检测到重复的 UNIQUE 键。

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

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

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

  • KEY | INDEX

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

  • UNIQUE

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

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

  • FULLTEXT

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

  • SPATIAL

    可以在空间数据类型上创建 SPATIAL 索引。空间类型仅适用于 InnoDBMyISAM 表,并且索引列必须声明为 NOT NULL。请参见 第 13.4 节,“空间数据类型”.

  • FOREIGN KEY

    MySQL 支持外键,它允许您跨表交叉引用相关数据,以及外键约束,它有助于保持这些分散数据的完整性。有关定义和选项信息,请参阅 reference_definitionreference_option

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

  • CHECK

    CHECK 子句允许创建约束,以检查表行中的数据值。请参阅 第 15.1.20.6 节,“CHECK 约束”

  • key_part

    • key_part 规范可以用 ASCDESC 结尾,以指定索引值是按升序还是降序存储。如果未指定顺序说明符,则默认为升序。

    • 前缀由 length 属性定义,对于使用 REDUNDANTCOMPACT 行格式的 InnoDB 表,前缀长度限制为 767 字节。对于使用 DYNAMICCOMPRESSED 行格式的 InnoDB 表,前缀长度限制为 3072 字节。对于 MyISAM 表,前缀长度限制为 1000 字节。

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

    • 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 索引一起使用。如果全文索引和搜索操作需要特殊处理,它会将解析器插件与索引关联。 InnoDBMyISAM 支持全文解析器插件。如果您有一个关联了全文解析器插件的 MyISAM 表,则可以使用 ALTER TABLE 将该表转换为 InnoDB

    • COMMENT

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

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

    • VISIBLEINVISIBLE

      指定索引可见性。索引默认可见。不可见索引不被优化器使用。索引可见性的指定适用于除主键(显式或隐式)以外的索引。有关更多信息,请参阅 第 10.3.12 节,“不可见索引”

    • ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE 选项用于指定主存储引擎和辅助存储引擎的索引属性。这些选项保留供将来使用。

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

  • reference_definition

    有关 reference_definition 语法详细信息和示例,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”

    InnoDBNDB 表支持检查外键约束。引用的表列必须始终显式命名。ON DELETEON UPDATE 操作都支持外键。有关更详细的信息和示例,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”

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

    重要

    对于熟悉 ANSI/ISO SQL 标准的用户,请注意,包括 InnoDB 在内的任何存储引擎都不识别或强制执行引用完整性约束定义中使用的 MATCH 子句。使用显式 MATCH 子句不会产生指定的效果,还会导致 ON DELETEON UPDATE 子句被忽略。出于这些原因,应避免指定 MATCH

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

    此外,MySQL 要求引用的列被索引以提高性能。但是,InnoDB 不强制执行任何要求引用的列被声明为 UNIQUENOT NULL 的要求。对于 UPDATEDELETE CASCADE 等操作,处理对非唯一键或包含 NULL 值的键的外键引用没有明确定义。建议使用仅引用既是 UNIQUE(或 PRIMARY)又是 NOT NULL 的键的外键。

    MySQL 接受 内联 REFERENCES 规范(如 SQL 标准中定义的那样),其中引用定义为列规范的一部分。MySQL 还接受对父表主键的隐式引用。有关更多信息,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”,以及 第 1.7.2.3 节,“FOREIGN KEY 约束差异”

  • reference_option

    有关 RESTRICTCASCADESET NULLNO ACTIONSET DEFAULT 选项的信息,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”

表选项

表选项用于优化表的行为。在大多数情况下,您不必指定任何选项。这些选项适用于所有存储引擎,除非另有说明。不适用于特定存储引擎的选项可能会被接受并作为表定义的一部分被记住。如果稍后使用 ALTER TABLE 将表转换为使用其他存储引擎,这些选项就会适用。

  • ENGINE

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

    存储引擎 描述
    InnoDB 具有行级锁和外键的事务安全表。新表的默认存储引擎。请参阅 第 17 章,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。参见 第 25 章,MySQL NDB 集群 9.0

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

  • AUTOEXTEND_SIZE

    定义了当 InnoDB 表空间已满时,其大小增长的量。设置值必须是 4MB 的倍数。默认设置是 0,这会导致表空间根据隐式默认行为进行扩展。有关更多信息,请参见 第 17.6.3.9 节,“表空间 AUTOEXTEND_SIZE 配置”

  • AUTO_INCREMENT

    表的初始 AUTO_INCREMENT 值。在 MySQL 9.0 中,这适用于 MyISAMMEMORYInnoDBARCHIVE 表。要为不支持 AUTO_INCREMENT 表选项的引擎设置第一个自动递增值,请在创建表后插入一个 dummy 行,其值比所需值小 1,然后删除该虚拟行。

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

  • AVG_ROW_LENGTH

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

    当您创建一个 MyISAM 表时,MySQL 使用 MAX_ROWSAVG_ROW_LENGTH 选项的乘积来确定结果表的尺寸。如果未指定这两个选项,则 MyISAM 数据和索引文件的最大尺寸默认值为 256TB。(如果您的操作系统不支持如此大的文件,则表尺寸将受到文件尺寸限制。)如果您希望缩小指针尺寸以使索引更小更快,并且实际上并不需要大型文件,则可以通过设置 myisam_data_pointer_size 系统变量来降低默认指针尺寸。(参见 第 7.1.8 节,“服务器系统变量”。)如果您希望所有表都能增长到超过默认限制,并且愿意让您的表比必要时稍慢且更大,则可以通过设置此变量来增加默认指针尺寸。将该值设置为 7 允许表尺寸达到 65,536TB。

  • [DEFAULT] CHARACTER SET

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

  • CHECKSUM

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

  • [DEFAULT] COLLATE

    指定表的默认排序规则。

  • COMMENT

    表的注释,最多 2048 个字符。

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

    设置 NDB_TABLE 选项。  在创建 NDB 表的 CREATE TABLE 或更改 NDB 表的 ALTER TABLE 语句中,表的注释也可以用来指定最多四个 NDB_TABLE 选项 NOLOGGINGREAD_BACKUPPARTITION_BALANCEFULLY_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 Information Schema TABLES 表的 TABLE_COMMENT 列获得。

    此注释语法也适用于 NDB 表的 ALTER TABLE 语句。请记住,与 ALTER TABLE 一起使用的表注释将替换表可能以前具有的任何现有注释。

    在表注释中设置 MERGE_THRESHOLD 选项对于 NDB 表不受支持(将被忽略)。

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

  • COMPRESSION

    用于 InnoDB 表的页面级压缩的压缩算法。支持的值包括 ZlibLZ4NoneCOMPRESSION 属性是在透明页面压缩功能引入时引入的。页面压缩仅支持位于 每个表一个文件 表空间中的 InnoDB 表,并且仅在支持稀疏文件和打孔的 Linux 和 Windows 平台上可用。有关更多信息,请参见 第 17.9.2 节,“InnoDB 页面压缩”

  • CONNECTION

    FEDERATED 表的连接字符串。

    注意

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

  • DATA DIRECTORYINDEX DIRECTORY

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

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

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

    重要

    表级 DATA DIRECTORYINDEX DIRECTORY 选项对于分区表将被忽略。(错误号 #32091)

    这些选项仅在您未使用 --skip-symbolic-links 选项时有效。您的操作系统还必须具有可正常工作的线程安全的 realpath() 调用。参见 第 10.12.2.2 节,“在 Unix 上对 MyISAM 表使用符号链接”,以获取更多完整信息。

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

    如果 MyISAM 表是在使用 DATA DIRECTORYINDEX DIRECTORY 选项的情况下创建的,并且发现了一个现有的 .MYD.MYI 文件,则 MyISAM 始终返回错误,并且不会覆盖指定目录中的文件。

    重要

    您不能使用包含 MySQL 数据目录的路径名与 DATA DIRECTORYINDEX DIRECTORY。这包括分区表和单个表分区。(参见错误号 #32167。)

  • DELAY_KEY_WRITE

    如果您希望延迟到表关闭时才更新表的键,则将其设置为 1。参见 第 7.1.8 节,“服务器系统变量” 中对 delay_key_write 系统变量的描述。(仅限 MyISAM。)

  • ENCRYPTION

    ENCRYPTION 子句启用或禁用 InnoDB 表的页面级数据加密。在启用加密之前,必须安装和配置一个密钥环插件。ENCRYPTION 子句可以在在每个表一个文件表空间中创建表时指定,也可以在创建通用表空间中的表时指定。

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

    如果使用的存储引擎不支持加密,则不允许指定 ENCRYPTION 子句的值不为 'N'''

    有关更多信息,请参阅 第 17.13 节,“InnoDB 数据休眠加密”

  • ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE 选项用于为主要和次要存储引擎指定表属性。这些选项保留供将来使用。

    分配给这两个选项的值必须是包含有效 JSON 文档的字符串文字或空字符串 (''). 无效 JSON 将被拒绝。

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

    ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE 值可以重复,不会出错。在这种情况下,使用最后指定的值。

    ENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTE 值不会由服务器检查,也不会在表存储引擎更改时清除。

  • INSERT_METHOD

    如果要将数据插入 MERGE 表,则必须使用 INSERT_METHOD 指定应将行插入的表。 INSERT_METHOD 是一个仅对 MERGE 表有用的选项。使用 FIRSTLAST 值以使插入进入第一个或最后一个表,或使用 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。有关更多信息,请参阅 第 17.9.1 节,“InnoDB 表压缩”

    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 STATUSCreate_options 列报告表实际使用的 KEY_BLOCK_SIZESHOW CREATE TABLE 也是如此。

    InnoDB 仅在表级别支持 KEY_BLOCK_SIZE

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

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

  • MAX_ROWS

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

    重要

    使用 MAX_ROWSNDB 表来控制表分区数量已过时。在更高版本中,它仍受支持以实现向后兼容,但可能会在将来的版本中删除。请改用 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 将告诉存储引擎仅打包长的 CHARVARCHARBINARYVARBINARY 列。

    如果不使用 PACK_KEYS,默认情况下会打包字符串,但不会打包数字。如果使用 PACK_KEYS=1,也会打包数字。

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

    • 每个键都需要一个额外的字节来指示前一个键中有多少字节对于下一个键是相同的。

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

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

  • PASSWORD

    此选项未使用。

  • ROW_FORMAT

    定义存储行的物理格式。

    在创建 严格模式 禁用的表时,如果指定的行格式不受支持,则使用存储引擎的默认行格式。表的实际行格式在响应 SHOW TABLE STATUSRow_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 定义的默认值。有关更多信息,请参阅 定义表的行格式

    • 为了更有效地 InnoDB 存储数据类型,尤其是 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=FIXEDInnoDB 会发出警告并假定为 ROW_FORMAT=DYNAMIC。如果在 innodb_strict_mode 启用的情况下指定 ROW_FORMAT=FIXED(这是默认设置), InnoDB 将返回错误。

    • 有关 InnoDB 行格式的更多信息,请参阅 第 17.10 节,“InnoDB 行格式”

    对于 MyISAM 表,选项值可以是 FIXEDDYNAMIC,用于静态或可变长度行格式。 myisampack 将类型设置为 COMPRESSED。请参阅 第 18.2.3 节,“MyISAM 表存储格式”

    对于 NDB 表,默认的 ROW_FORMATDYNAMIC

  • START TRANSACTION

    这是一个内部使用表选项,用于允许在使用基于行的复制以及支持原子 DDL 的存储引擎时,将 CREATE TABLE ... SELECT 记录为二进制日志中的单个原子事务。仅允许 BINLOGCOMMITROLLBACK 语句在 CREATE TABLE ... START TRANSACTION 之后。有关相关信息,请参阅 第 15.1.1 节,“原子数据定义语句支持”

  • STATS_AUTO_RECALC

    指定是否自动重新计算 持久性统计信息 用于 InnoDB 表。值 DEFAULT 导致表的持久性统计信息设置由 innodb_stats_auto_recalc 配置选项决定。值 1 导致当表中 10% 的数据发生变化时重新计算统计信息。值 0 阻止自动重新计算此表;使用此设置,在对表进行大量更改后,发出 ANALYZE TABLE 语句以重新计算统计信息。有关持久性统计信息功能的更多信息,请参见 第 17.8.10.1 节,“配置持久性优化器统计信息参数”

  • STATS_PERSISTENT

    指定是否为 InnoDB 表启用 持久性统计信息。值 DEFAULT 导致表的持久性统计信息设置由 innodb_stats_persistent 配置选项决定。值 1 为表启用持久性统计信息,而值 0 关闭此功能。在通过 CREATE TABLEALTER 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 子句结合使用。在 DATA DIRECTORY 子句中指定的目录必须为 InnoDB 所知。有关更多信息,请参见 使用 DATA DIRECTORY 子句

    注意

    CREATE TEMPORARY TABLE 中支持使用 TABLESPACE = innodb_file_per_tableTABLESPACE = innodb_temporary 子句已弃用;预计在 MySQL 的未来版本中将删除它。

    仅当使用 NDB 表时才使用 STORAGE 表选项。STORAGE 确定使用的存储类型,可以是 DISKMEMORY

    TABLESPACE ... STORAGE DISK 将表分配到 NDB Cluster 磁盘数据表空间。STORAGE DISK 不能在 CREATE TABLE 中使用,除非在前面有 TABLESPACE tablespace_name

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

    有关更多信息,请参见 第 25.6.11 节,“NDB Cluster 磁盘数据表”

  • UNION

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

    您必须对映射到 MERGE 表的表具有 SELECTUPDATEDELETE 权限。

    注意

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

表分区

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

本节开头 partition_options 语法中显示的并非所有选项都适用于所有分区类型。请参见以下各个类型的列表,了解特定于每种类型的 信息,并参见 第 26 章,分区,了解有关 MySQL 中分区的运作和使用的更完整信息,以及有关创建表和其他与 MySQL 分区相关的语句的更多示例。

可以修改分区、合并分区、将分区添加到表以及从表中删除分区。有关执行这些任务的 MySQL 语句的基本信息,请参见 第 15.1.9 节,“ALTER TABLE 语句”。有关更详细的描述和示例,请参见 第 26.3 节,“分区管理”

  • PARTITION BY

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

    注意

    PARTITION BY 子句中使用的表达式 (expr) 不能引用正在创建的表中不存在的任何列;此类引用明确不允许,并会导致语句因错误而失败。(错误 #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 THANVALUES IN 子句。

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

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

  • KEY(column_list)

    这类似于 HASH,只是 MySQL 提供了哈希函数,以保证数据均匀分布。column_list 参数只是一个或多个表列的列表(最大: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] KEYALGORITHM=1 会导致服务器使用与 MySQL 5.1 相同的键哈希函数;ALGORITHM=2 表示服务器使用在 MySQL 5.5 及更高版本中为新的 KEY 分区表默认实现和使用的键哈希函数。(使用在 MySQL 5.5 及更高版本中实现的键哈希函数创建的分区表不能被 MySQL 5.1 服务器使用。)不指定此选项与使用 ALGORITHM=2 的效果相同。此选项主要用于在 MySQL 5.1 和更高版本的 MySQL 之间升级或降级 [LINEAR] KEY 分区表时,或者在 MySQL 5.5 或更高版本的服务器上创建按 KEYLINEAR KEY 划分的表,这些表可以在 MySQL 5.1 服务器上使用。有关更多信息,请参见 第 15.1.9.1 节,“ALTER TABLE 分区操作”

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

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

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

  • RANGE(expr)

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

    注意

    对于按 RANGE 分区的表,VALUES LESS THAN 必须与整型字面量值或计算结果为单个整型值的表达式一起使用。在 MySQL 9.0 中,您可以克服使用 PARTITION BY RANGE COLUMNS 定义的表中的此限制,如本节后面所述。

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

    分区编号 年范围
    0 1990 年及之前
    1 1991 年至 1994 年
    2 1995 年至 1998 年
    3 1999 年至 2002 年
    4 2003 年至 2005 年
    5 2006 年及之后

    实现此类分区方案的表可以通过以下所示的 CREATE TABLE 语句来实现

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

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

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

  • RANGE COLUMNS(column_list)

    RANGE 变体便于对使用多个列的范围条件的查询进行分区修剪(即,具有 WHERE a = 1 AND b < 10WHERE a = 1 AND b = 10 AND c < 10 等条件)。它允许您通过在 COLUMNS 子句中使用列列表,并在每个 PARTITION ... VALUES LESS THAN (value_list) 分区定义子句中使用一组列值来指定多个列中的值范围。(在最简单的情况下,此集合包含单个列。)column_listvalue_list 中可以引用的最大列数为 16。

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

    分区定义中使用的 VALUES LESS THAN 子句必须为 COLUMNS() 子句中出现的每列指定一个字面量值;也就是说,用于每个 VALUES LESS THAN 子句的值列表必须包含与 COLUMNS 子句中列出的列数一样多的值。尝试在 VALUES LESS THAN 子句中使用比 COLUMNS 子句中多的值或少的值会导致语句失败,并出现错误 分区使用列列表不一致...。您不能对 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 定义至少一个分区。您不能将 VALUES LESS THANPARTITION BY LIST 一起使用。

    注意

    对于按 LIST 分区的表,与 VALUES IN 一起使用的值列表必须仅包含整型值。在 MySQL 9.0 中,您可以使用按 LIST COLUMNS 分区来克服此限制,这将在本节后面介绍。

  • LIST COLUMNS(column_list)

    LIST 变体便于对使用多个列的比较条件的查询进行分区修剪(即,具有 WHERE a = 5 AND b = 5WHERE 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 一样,VALUES INPARTITION BY LIST COLUMNS 一起使用的值列表之间存在一个重要区别。当与 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 子句声明的任何分区的总数。

    注意

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

  • SUBPARTITION BY

    可以选择将分区划分为多个子分区。这可以使用可选的 SUBPARTITION BY 子句来指示。子分区可以通过 HASHKEY 完成。这两个都可以是 LINEAR。这些工作方式与之前描述的等效分区类型相同。(不能按 LISTRANGE 进行子分区。)

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

  • PARTITIONSSUBPARTITIONS 子句中使用的值的严格检查将被应用,并且此值必须符合以下规则

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

    • 不允许有前导零。

    • 该值必须是整型字面量,不能是表达式。例如,PARTITIONS 0.2E+01 不允许,即使 0.2E+01 评估为 2。(错误 #15890)

  • partition_definition

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

    • PARTITION partition_name

      指定分区的逻辑名称。

    • VALUES

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

    • [STORAGE] ENGINE

      MySQL 接受 [STORAGE] ENGINE 选项,用于 PARTITIONSUBPARTITION。目前,此选项的唯一使用方式是将所有分区或所有子分区设置为相同的存储引擎,并且尝试为同一表中的分区或子分区设置不同的存储引擎会引发错误 ERROR 1469 (HY000): 此版本的 MySQL 不允许在分区中混合处理程序

    • COMMENT

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

      COMMENT = 'Data for the years previous to 1999'

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

    • DATA DIRECTORYINDEX DIRECTORY

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

      DATA DIRECTORY 子句中指定的目录必须为 InnoDB 所知。有关更多信息,请参阅 使用 DATA DIRECTORY 子句

      您必须具有 FILE 权限才能使用 DATA DIRECTORYINDEX 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 DIRECTORYINDEX DIRECTORY 的行为与 CREATE TABLE 语句的 table_option 子句中用于 MyISAM 表的方式相同。

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

      如果 NO_DIR_IN_CREATE 生效,则创建分区表时将忽略 DATA DIRECTORYINDEX DIRECTORY 选项。

    • MAX_ROWSMIN_ROWS

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

    • TABLESPACE

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

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

  • subpartition_definition

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

    子分区必须通过 HASHKEY 进行,并且只能在 RANGELIST 分区上进行。请参阅 第 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() 定义的生成列是允许的。