Documentation Home
MySQL 8.0 参考手册  / 第 13 章 SQL 语句  / 13.1 数据定义语句  /  13.1.18 CREATE TABLE 语句

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 Chapter 11, 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创建具有给定名称的表。您必须拥有该 CREATE表的权限。

默认情况下,表是使用 InnoDB存储引擎在默认数据库中创建的。如果表存在、没有默认数据库或数据库不存在,则会发生错误。

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

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

该声明有几个方面,CREATE TABLE在本节的以下主题下进行了描述:

表名

  • tbl_name

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

    第 9.2 节“模式对象名称” 给出了允许的表名规则 。

  • IF NOT EXISTS

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

临时表

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

表克隆和复制

列数据类型和属性

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

  • data_type

    data_type表示列定义中的数据类型。有关可用于指定列数据类型的语法的完整说明,以及有关每种类型的属性的信息,请参阅 第 11 章,数据类型

    • 一些属性并不适用于所有数据类型。 AUTO_INCREMENT仅适用于整数和浮点类型。DEFAULT不适用于BLOBTEXTGEOMETRYJSON类型。

    • 字符数据类型(CHARVARCHARTEXT类型、 ENUMSET和任何同义词)可以包含CHARACTER SET以指定列的字符集。CHARSET 是的同义词CHARACTER SET。字符集的排序规则可以与 COLLATE属性以及任何其他属性一起指定。有关详细信息,请参阅第 10 章,字符集、排序规则、Unicode。例子:

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

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

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

      只有InnoDB和 存储引擎支持在和 列MyISAM上建立索引。例如: 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

    如果既未指定NULL也未NOT NULL指定,则该列被视为 NULL已指定。

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

  • DEFAULT

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

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

  • 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 函数。请参阅第 12.16 节,“信息函数”mysql_insert_id()

    如果NO_AUTO_VALUE_ON_ZERO 启用了 SQL 模式,则可以在不生成新序列值的情况下存储0AUTO_INCREMENT列中 。0请参阅第 5.1.10 节,“服务器 SQL 模式”

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

    对于表,您可以在多列键中MyISAM指定 辅助列。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 节,“服务器系统变量”

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

  • COMMENT

    可以使用 COMMENT选项指定列的注释,最长为 1024 个字符。注释由SHOW CREATE TABLEand SHOW FULL COLUMNS语句显示。它也显示在表的 COLUMN_COMMENT列中 INFORMATION_SCHEMA.COLUMNS

  • COLUMN_FORMAT

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

    从 MySQL NDB Cluster 7.5.4 开始,对于 表NDB,默认值为 . (在 MySQL NDB Cluster 7.5.1 中已切换为默认值 ,但此更改已恢复以保持与现有 GA 版本系列的向后兼容性。)(缺陷 #24487363) COLUMN_FORMATFIXEDDYNAMIC

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

    COLUMN_FORMAT目前对使用除 . 以外的存储引擎的表的列没有影响 NDB。在 MySQL 5.7 及更高版本中,COLUMN_FORMAT被静默忽略。

  • STORAGE

    对于表,可以使用子句NDB指定列是存储在磁盘上还是内存中。导致列存储在磁盘上,并 导致使用内存存储。使用的 语句仍必须包含一个 子句: STORAGESTORAGE DISKSTORAGE MEMORYCREATE TABLETABLESPACE

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

  • GENERATED ALWAYS

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

    存储的生成列可以被索引。InnoDB 支持 虚拟生成列的二级索引。请参阅 第 13.1.18.8 节,“二级索引和生成的列”

索引和外键

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

  • CONSTRAINT symbol

    可以给出 该子句来命名约束。如果没有给出子句,或者 关键字后面没有包含a ,MySQL 会自动生成一个约束名称,但以下情况除外。该值(如果使用)对于每个模式(数据库)、每个约束类型必须是唯一的。重复 会导致错误。另请参阅第 9.2.1 节“标识符长度限制”中关于生成的约束标识符的长度限制的讨论。 CONSTRAINT symbolsymbolCONSTRAINTsymbolsymbol

    笔记

    如果外键定义中没有给出该子句,或者 关键字 后面没有包含a,则使用外键索引名称。 CONSTRAINT symbolsymbolCONSTRAINTNDB

    SQL 标准规定所有类型的约束(主键、唯一索引、外键、校验)都属于同一个命名空间。在 MySQL 中,每个约束类型在每个模式中都有自己的名称空间。因此,每个模式的每种约束类型的名称必须是唯一的。

  • PRIMARY KEY

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

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

    InnoDB表中,保持PRIMARY KEY简短以最小化二级索引的存储开销。每个二级索引条目都包含对应行的主键列的副本。(请参阅 第 14.6.2.1 节,“聚簇索引和二级索引”。)

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

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

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

    在 MySQL 中,a 的名称PRIMARY KEYPRIMARY. 对于其他索引,如果您不指定名称,则该索引将被指定为与第一个索引列相同的名称,并带有一个可选的后缀 ( _2, _3, ...) 以使其唯一。您可以使用 来查看表的索引名称。请参阅 第 13.7.5.22 节,“SHOW INDEX 语句”SHOW INDEX FROM tbl_name

  • KEY | INDEX

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

  • UNIQUE

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

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

  • FULLTEXT

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

  • SPATIAL

    SPATIAL您可以在空间数据类型上 创建索引。MyISAM只有和表支持空间类型 InnoDB ,索引列必须声明为NOT NULL. 请参阅第 11.4 节,“空间数据类型”

  • FOREIGN KEY

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

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

  • CHECK

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

  • key_part

    • key_part规范可以以 或ASC结尾 DESC。这些关键字允许用于未来的扩展,以指定升序或降序索引值存储。目前,它们被解析但被忽略;索引值始终按升序存储。

    • 由属性定义的前缀length 对于表最长可达 767 字节, InnoDB如果 innodb_large_prefix 启用该选项则最长可达 3072 字节。对于MyISAM表,前缀长度限制为 1000 字节。

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

  • 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带有关联的全文解析器插件的表,您可以将该表转换为InnoDB使用ALTER TABLE.

    • COMMENT

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

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

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

  • reference_definition

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

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

    对于其他存储引擎,MySQL Server 会解析并忽略 语句FOREIGN KEY中的语法 CREATE TABLE

    重要的

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

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

    此外,MySQL 要求对引用的列进行索引以提高性能。但是,InnoDB 不强制要求声明引用的列UNIQUENOT NULL. 对非唯一键或包含值的键的外键引用的处理对于 or等NULL ​​操作没有明确定义 。建议您使用仅引用既是 (or ) 又是. UPDATEDELETE CASCADEUNIQUEPRIMARYNOT NULL

    MySQL 解析但忽略内联 REFERENCES规范(如 SQL 标准中所定义),其中引用被定义为列规范的一部分。MySQL REFERENCES仅在指定为单独FOREIGN KEY 规范的一部分时才接受子句。有关详细信息,请参阅 第 1.7.2.3 节,“外键约束差异”

  • reference_option

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

表格选项

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

  • ENGINE

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

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

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

  • AUTO_INCREMENT

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

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

  • AVG_ROW_LENGTH

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

    创建MyISAM表时,MySQL 使用MAX_ROWSAVG_ROW_LENGTH选项的乘积来决定结果表的大小。如果您未指定任一选项,则MyISAM数据和索引文件的最大大小默认为 256TB。(如果您的操作系统不支持那么大的文件,表大小受文件大小限制的限制。)如果您想减小指针大小以使索引更小更快并且您真的不需要大文件,您可以通过设置myisam_data_pointer_size 系统变量来减小默认指针大小 。(参见 第 5.1.7 节,“服务器系统变量”.) 如果您希望所有表都能够增长到超过默认限制并且愿意让您的表稍微慢一些并且比需要的大一些,您可以通过设置此变量来增加默认指针大小。将值设置为 7 允许表大小达到 65,536TB。

  • [DEFAULT] CHARACTER SET

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

  • CHECKSUM

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

  • [DEFAULT] COLLATE

    指定表的默认排序规则。

  • COMMENT

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

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

    设置 NDB_TABLE 选项。  在 MySQL NDB Cluster 7.5.2 及更高版本中, CREATE TABLEor 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 列。

    表格ALTER TABLE语句 也支持此注释语法 。NDB请记住,与一起使用的表格注释ALTER TABLE会替换表格之前可能具有的任何现有注释。

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

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

  • COMPRESSION

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

  • CONNECTION

    表的连接字符串FEDERATED

    笔记

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

  • DATA DIRECTORY,INDEX DIRECTORY

    对于InnoDB,该 子句允许在数据目录之外创建一个表。 必须启用该变量才能使用该子句。必须指定完整的目录路径。有关详细信息,请参阅 第 14.6.1.2 节,“在外部创建表”DATA DIRECTORY='directory'innodb_file_per_tableDATA DIRECTORY

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

    从 MySQL 5.7.17 开始,您必须有权 FILE使用 DATA DIRECTORYorINDEX DIRECTORY表选项。

    重要的

    分区表忽略 表级别DATA DIRECTORY和 选项。INDEX DIRECTORY(漏洞 #32091)

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

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

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

    重要的

    不能将包含 MySQL 数据目录的路径名与DATA DIRECTORY或 一起使用INDEX DIRECTORY。这包括分区表和单独的表分区。(参见缺陷 #32167。)

  • DELAY_KEY_WRITE

    如果要延迟表的键更新直到表关闭,请将此设置为 1。请参阅 第 5.1.7 节“服务器系统变量”delay_key_write中系统变量的说明。(仅。) MyISAM

  • ENCRYPTION

    ENCRYPTION选项 设置为为在file-per-table 表空间中创建 'Y'的表启用页面级数据加密 。选项值不区分大小写。该 选项是通过 表空间加密功能引入的;参见 第 14.14 节,“InnoDB 静态数据加密”。在 启用加密之前必须安装和配置插件。 InnoDBENCRYPTIONInnoDBkeyring

  • INSERT_METHOD

    如果要将数据插入MERGE 表中,则必须指定INSERT_METHOD 要插入行的表。 是一个仅对表格INSERT_METHOD有用的选项 。MERGE使用 FIRSTor的值LAST使插入转到第一个或最后一个表,或者使用 的值 NO防止插入。请参阅 第 15.7 节,“MERGE 存储引擎”

  • KEY_BLOCK_SIZE

    对于MyISAM表, KEY_BLOCK_SIZE可选择指定用于索引键块的大小(以字节为单位)。该值被视为提示;如有必要,可以使用不同的尺寸。为单个索引定义指定的KEY_BLOCK_SIZE值会覆盖表级 KEY_BLOCK_SIZE值。

    对于InnoDB表, KEY_BLOCK_SIZE指定 用于压缩表的页面大小(以千字节为单位) 。该 值被视为提示;如有必要,可以使用不同的尺寸。只能小于或等于该 值。值 0 表示默认压缩页面大小,是该 值的一半。根据 ,可能的 值包括 0、1、2、4、8 和 16。有关更多信息,请参阅第 14.9.1 节,“InnoDB 表压缩” InnoDBKEY_BLOCK_SIZEInnoDBKEY_BLOCK_SIZEinnodb_page_sizeinnodb_page_sizeinnodb_page_sizeKEY_BLOCK_SIZE

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

    Create_options响应 的列SHOW TABLE STATUS报告了最初指定的KEY_BLOCK_SIZE选项, 也是如此SHOW CREATE TABLE

    InnoDB仅支持 KEY_BLOCK_SIZE表级别。

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

  • MAX_ROWS

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

    重要的

    从 NDB Cluster 7.5.4 开始,不推荐使用MAX_ROWSwith NDBtables 来控制 table 分区的数量。它在以后的版本中仍然受支持以实现向后兼容性,但在未来的版本中可能会被删除。改为使用 PARTITION_BALANCE;请参阅 设置 NDB_TABLE 选项

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

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

  • MIN_ROWS

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

  • PACK_KEYS

    仅对MyISAM表格有效。如果您想要较小的索引,请将此选项设置为 1。这通常会使更新速度变慢而读取速度变快。将选项设置为 0 将禁用所有密钥打包。将其设置为 DEFAULT告诉存储引擎仅打包 long CHARVARCHARBINARYVARBINARY列。

    如果你不使用PACK_KEYS,默认是打包字符串,而不是数字。如果您使用 PACK_KEYS=1,数字也会被打包。

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

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

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

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

  • PASSWORD

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

  • ROW_FORMAT

    定义存储行的物理格式。

    在禁用严格模式的情况下 创建表时 ,如果不支持指定的行格式,则使用存储引擎的默认行格式。表的实际行格式在Row_format 列中报告以响应SHOW TABLE STATUS。该Create_options列显示语句中指定的行格式 CREATE TABLE,与 一样 SHOW CREATE TABLE

    行格式选择因用于表的存储引擎而异。

    对于InnoDB表:

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

      如果ROW_FORMAT未定义或ROW_FORMAT=DEFAULT使用该选项,则重建表的操作也会悄悄地将表的行格式更改为 定义的默认值 innodb_default_row_format。有关详细信息,请参阅 定义表的行格式

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

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

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

    • 指定非默认 ROW_FORMAT子句时,还要考虑启用 innodb_strict_mode 配置选项。

    • ROW_FORMAT=FIXED不支持。如果 在禁用时ROW_FORMAT=FIXED指定 ,发出警告并假设. 如果 在启用时指定 ,这是默认设置,则 返回错误。 innodb_strict_modeInnoDBROW_FORMAT=DYNAMICROW_FORMAT=FIXEDinnodb_strict_modeInnoDB

    • 有关InnoDB 行格式的其他信息,请参阅第 14.11 节,“InnoDB 行格式”

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

    对于表, MySQL NDB Cluster 7.5.1 及更高版本中NDB的默认 值为. (以前是 。) ROW_FORMATDYNAMICFIXED

  • STATS_AUTO_RECALC

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

  • STATS_PERSISTENT

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

  • STATS_SAMPLE_PAGES

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

  • TABLESPACE

    TABLESPACE子句可用于在现有的通用表空间、file-per-table 表空间或系统表空间中创建表。

    CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name

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

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

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

    CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system

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

    要在 file-per-table 表空间中创建表,请指定 innodb_file_per_table为表空间名称。

    CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table
    笔记

    如果innodb_file_per_table启用,则无需指定 TABLESPACE=innodb_file_per_table创建InnoDBfile-per-table 表空间。InnoDB默认情况下,表是在每个表的文件表空间中创建的 innodb_file_per_table

    笔记

    InnoDBMySQL 5.7.24 中不再 支持在共享表空间中创建表分区 ;希望在未来的 MySQL 版本中将其删除。共享表空间包括 InnoDB系统表空间和通用表空间。

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

    笔记

    从 MySQL 5.7.24 开始不推荐使用 和TABLESPACE = innodb_file_per_table子句TABLESPACE = innodb_temporaryCREATE TEMPORARY TABLE希望在未来的 MySQL 版本中将其删除。

    STORAGE表格选项仅用于NDB表格 。STORAGE确定使用的存储类型(磁盘或内存),可以是DISKMEMORY

    TABLESPACE ... STORAGE DISK将表分配给 NDB Cluster 磁盘数据表空间。表空间必须已经使用创建CREATE TABLESPACE。有关更多信息,请参阅 第 21.6.11 节,“NDB Cluster 磁盘数据表”

    重要的

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

  • UNION

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

    对于映射到表的表, 您必须具有SELECTUPDATE和 权限。 DELETEMERGE

    笔记

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

表分区

partition_options可用于控制用 . 创建的表的分区 CREATE TABLE

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

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

  • PARTITION BY

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

    笔记

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

  • HASH(expr)

    散列一个或多个列以创建用于放置和定位行的键。expr是一个使用一个或多个表列的表达式。这可以是产生单个整数值的任何有效的 MySQL 表达式(包括 MySQL 函数)。例如,这些都是有效的 CREATE TABLE语句使用 PARTITION BY HASH

    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 THANor VALUES IN子句与PARTITION BY HASH.

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

    LINEAR关键字需要一种稍微不同的算法 。在这种情况下,存储一行的分区的编号被计算为一个或多个逻辑AND运算的结果。有关线性散列的讨论和示例,请参阅 第 22.2.4.1 节,“线性散列分区”

  • 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。也就是说,分区号是使用 & 运算符而不是模数找到的(有关详细信息,请参阅 第 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;

    支持该ALGORITHM={1 | 2}选项[SUB]PARTITION BY [LINEAR] KEYALGORITHM=1使服务器使用与 MySQL 5.1 相同的密钥哈希函数; ALGORITHM=2意味着服务器采用 key-hashing 函数,默认情况下用于KEYMySQL 5.5 及更高版本中的新分区表。(使用 MySQL 5.5 及更高版本中采用的键散列函数创建的分区表不能由 MySQL 5.1 服务器使用。)不指定该选项与使用 . 具有相同的效果ALGORITHM=2。此选项主要用于 [LINEAR] KEY在 MySQL 5.1 和更高版本的 MySQL 之间升级或降级分区表,或创建分区表KEY或者LINEAR KEY在可以在 MySQL 5.1 服务器上使用的 MySQL 5.5 或更高版本的服务器上。有关详细信息,请参阅 第 13.1.8.1 节,“ALTER TABLE 分区操作”

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

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

    这会导致 MySQL 5.6.10 和更早版本的服务器忽略该选项,否则会在这些版本中导致语法错误。如果您计划将在 MySQL 5.7 服务器上创建的转储加载到 MySQL 5.6 服务器中,在 MySQL 5.7 服务器上使用分区或子分区的表,KEY版本低于 5.6.11,请务必 在继续之前查阅MySQL 5.6 中的更改。KEY (如果您正在将包含从 MySQL 5.7(实际上是 5.6.11 或更高版本)服务器创建的分区表或子分区表 的转储加载到 MySQL 5.5.30 或更早版本的服务器中,则此处找到的信息也适用。)

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

    您不能将VALUES LESS THANor VALUES IN子句与PARTITION BY KEY.

  • RANGE(expr)

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

    笔记

    对于按 分区的表RANGEVALUES LESS THAN必须与整数文字值或计算结果为单个整数值的表达式一起使用。在 MySQL 5.7 中,您可以在使用 定义的表中克服此限制 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子句以类似于块的各个case 部分的方式按顺序工作switch ... case(在许多编程语言中都可以找到,例如 C、Java 和 PHP)。也就是说,子句的排列方式必须使每个连续的子句的上限VALUES LESS THAN都大于前一个子句的上限,引用的子句MAXVALUE在列表中排在最后。

  • RANGE COLUMNS(column_list)

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

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

    分区定义中使用的VALUES LESS THAN子句必须为子句中出现的每一列指定一个文字值COLUMNS() ;也就是说,用于每个 VALUES LESS THAN子句的值列表必须包含与子句中列出的列相同数量的值 COLUMNS。尝试在VALUES LESS THAN子句中使用比子句中更多或更少的值COLUMNS会导致语句失败,并显示错误不一致使用列列表进行分区...。您不能使用NULLfor 中出现的任何值 VALUES LESS THAN。可以使用 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'

    有关详细信息,请参阅 第 22.2.1 节,“RANGE 分区”第 22.4 节,“分区修剪”

  • LIST(expr)

    这在根据具有一组受限制的可能值(例如州或国家/地区代码)的表列分配分区时很有用。在这种情况下,属于某个州或国家的所有行都可以分配给单个分区,或者可以为一组特定的州或国家保留一个分区。它与 类似 RANGE,只是 onlyVALUES 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 THANwith PARTITION BY LIST

    笔记

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

  • LIST COLUMNS(column_list)

    此变体LIST有助于对使用多列比较条件(即具有WHERE a = 5 AND b = 5or等​​条件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 INMAXVALUENULL

    用于 with 的值列表与用于 VALUES INwith 的值列表之间存在一个重要区别。与 一起使用时 ,子句中的每个元素都必须是一 列值;每个集合中值的数量必须与子句中使用的列数相同,并且这些值的数据类型必须与列的数据类型匹配(并且以相同的顺序出现)。在最简单的情况下,集合由一列组成。 中和 组成 的元素 中可以使用的最大列数是 16。 PARTITION BY LIST COLUMNSPARTITION BY LISTPARTITION BY LIST COLUMNSVALUES INCOLUMNScolumn_listvalue_list

    以下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 numnumPARTITIONnumPARTITION

    笔记

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

  • SUBPARTITION BY

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

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

  • PARTITIONS对or 子句中 使用的值进行严格检查, SUBPARTITIONS该值必须遵守以下规则:

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

    • 不允许有前导零。

    • 该值必须是整数文字,不能是表达式。例如,PARTITIONS 0.2E+01不允许,即使 0.2E+01计算结果为 2. (漏洞 #15890)

  • partition_definition

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

    • PARTITION partition_name

      指定分区的逻辑名称。

    • VALUES

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

    • [STORAGE] ENGINE

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

    • COMMENT

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

      COMMENT = 'Data for the years previous to 1999'

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

    • DATA DIRECTORYINDEX DIRECTORY

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

      从 MySQL 5.7.17 开始,您必须有权 FILE使用 DATA DIRECTORYorINDEX 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中相同的方式运行 。CREATE TABLEtable_optionMyISAM

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

      在 Windows 上,单个分区或表的子分区不支持 DATA DIRECTORY和 选项,并且表 的单个分区或子分区不支持该选项 。这些选项在 Windows 上被忽略,除非生成警告。(缺陷号 30459) INDEX DIRECTORYMyISAMINDEX DIRECTORYInnoDB

      笔记

      如果有效,创建分区表时将忽略 和DATA DIRECTORY选项。(漏洞 #24633) INDEX DIRECTORYNO_DIR_IN_CREATE

    • MAX_ROWSMIN_ROWS

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

    • TABLESPACE

      可用于为分区指定表空间。由 NDB Cluster 支持。对于InnoDB 表,它可用于通过指定为分区指定每个表的文件表空间 TABLESPACE `innodb_file_per_table`。所有分区必须属于同一个存储引擎。

      笔记

      在 MySQL 5.7.24 中弃用了 对将InnoDB表分区放置在共享表空间中的支持;InnoDB希望在未来的 MySQL 版本中将其删除。共享表空间包括InnoDB系统表空间和通用表空间。

  • subpartition_definition

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

    子分区必须由HASHor 完成KEY,并且只能在 RANGEorLIST 分区上完成。请参阅第 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()