表名Table Name

临时表Temporary Tables

表克隆和复制Table Cloning and Copying

列数据类型和属性Column Data Types and Attributes

索引、外键和 CHECK 约束Indexes, Foreign Keys, and CHECK Constraints

表选项Table Options

表分区Table Partitioning

tbl_name

可以将表名指定为 db_name.tbl_name，以在特定数据库中创建表。无论是否存在默认数据库，这都有效，前提是该数据库存在。如果使用引用标识符，请分别引用数据库和表名。例如，写作 `mydb`.`mytbl`，而不是 `mydb.mytbl`。The table name can be specified as db_name.tbl_name to create the table in a specific database. This works regardless of whether there is a default database, assuming that the database exists. If you use quoted identifiers, quote the database and table names separately. For example, write `mydb`.`mytbl`, not `mydb.mytbl`.

允许的表名规则在 第 11.2 节，“模式对象名称” 中给出。Rules for permissible table names are given in Section 11.2, “Schema Object Names”.

IF NOT EXISTS

如果表已存在，可防止发生错误。然而，并不会验证现有表的结构是否与 CREATE TABLE 语句中指定的结构相同。Prevents an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

LIKE

使用 CREATE TABLE ... LIKE 基于另一个表的定义创建空表，包括原表中定义的任何列属性和索引：Use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

欲了解更多信息，请参见 第 15.1.20.3 节，“CREATE TABLE ... LIKE 语句”。For more information, see Section 15.1.20.3, “CREATE TABLE ... LIKE Statement”.

[AS] query_expression

要从另一个表创建表，可在 CREATE TABLE 语句末尾添加 SELECT 语句：To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

欲了解更多信息，请参见 第 15.1.20.4 节，“CREATE TABLE ... SELECT 语句”。For more information, see Section 15.1.20.4, “CREATE TABLE ... SELECT Statement”.

IGNORE | REPLACE

IGNORE 和 REPLACE 选项指示在使用 SELECT 语句复制表时如何处理重复唯一键值的行。The IGNORE and REPLACE options indicate how to handle rows that duplicate unique key values when copying a table using a SELECT statement.

欲了解更多信息，请参见 第 15.1.20.4 节，“CREATE TABLE ... SELECT 语句”。For more information, see Section 15.1.20.4, “CREATE TABLE ... SELECT Statement”.

data_type

data_type 表示列定义中的数据类型。有关指定列数据类型的语法的完整描述以及每种类型的属性信息，请参见 第 13 章，数据类型。data_type represents the data type in a column definition. For a full description of the syntax available for specifying column data types, as well as information about the properties of each type, see Chapter 13, Data Types.

NOT NULL | NULLNOT NULL | NULL

如果没有指定 NULL，也没有指定 NOT NULL，那么该列将被视为 NULL。If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

在 MySQL 8.4 中，只有 InnoDB，MyISAM以及 MEMORY 存储引擎支持对可以是 NULL。在其他情况下，你必须将索引列声明为 NOT NULL，否则会出现错误。In MySQL 8.4, only the InnoDB, MyISAM, and MEMORY storage engines support indexes on columns that can have NULL values. In other cases, you must declare indexed columns as NOT NULL or an error results.

DEFAULTDEFAULT

的默认值。关于默认值处理的更多信息，包括列定义中没有显式 DEFAULT 值的情况，请参见 第 13.6 节，“Data type Default Values”。Specifies a default value for a column. For more information about default value handling, including the case that a column definition includes no explicit DEFAULT value, see Section 13.6, “Data Type Default Values”.

如果 NO_ZERO_DATE 或 NO_ZERO_IN_DATE SQL 模式启用且该模式中日期值默认不正确，CREATE TABLE 如果未启用严格 SQL 模式会发出警告，启用严格模式则会报错。例如，启用 NO_ZERO_IN_DATE，c1 DATE DEFAULT '2010-00-00' 会发出警告。If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled and a date-valued default is not correct according to that mode, CREATE TABLE produces a warning if strict SQL mode is not enabled and an error if strict mode is enabled. For example, with NO_ZERO_IN_DATE enabled, c1 DATE DEFAULT '2010-00-00' produces a warning.

VISIBLE，INVISIBLEVISIBLE, INVISIBLE

指定列可见性。默认为 VISIBLE，如果这两个关键词都不存在。表必须至少有一个可见列。尝试让所有列都不可见会产生错误。更多信息请参见 第 15.1.20.10 节，“Invisible Columns”。Specify column visibility. The default is VISIBLE if neither keyword is present. A table must have at least one visible column. Attempting to make all columns invisible produces an error. For more information, see Section 15.1.20.10, “Invisible Columns”.

AUTO_INCREMENT

整数列可以具有附加属性AUTO_INCREMENT。当你插入一个值为NULL(推荐) 或者0到一个索引AUTO_INCREMENT列时，该列被设置为下一个序列值。通常这是value+1，其中value当前表中该列的最大值。AUTO_INCREMENT序列从1.An integer column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

插入行后要检索 AUTO_INCREMENT 值，可以使用 LAST_INSERT_ID() SQL 函数，或 mysql_insert_id() C API 函数。参见 第 14.15 节，“信息函数”，以及 mysql_insert_id（）。To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Section 14.15, “Information Functions”, and mysql_insert_id().

如果启用了 NO_AUTO_VALUE_ON_ZERO SQL 模式，则可以在 AUTO_INCREMENT 列中存储 0 作为 0，而不会生成新的序列值。参见 第 7.1.11 节，“服务器 SQL 模式”。If the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 7.1.11, “Server SQL Modes”.

开始。每个表只能有一个AUTO_INCREMENT列，它必须被索引，并且不能有一个DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

对于 MyISAM 表，你可以在多列键中指定一个 AUTO_INCREMENT 的辅助列。参见 第 5.6.9 节，“使用 AUTO_INCREMENT”。For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 5.6.9, “Using AUTO_INCREMENT”.

为了使 MySQL 与某些 ODBC 应用程序兼容，可以使用以下查询找到最后插入行的 AUTO_INCREMENT 值：To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

SELECT * FROM tbl_name WHERE auto_col IS NULL

此方法要求 sql_auto_is_null 变量未设置为 0。参见 第 7.1.8 节，“服务器系统变量”。This method requires that sql_auto_is_null variable is not set to 0. See Section 7.1.8, “Server System Variables”.

有关 InnoDB 和 AUTO_INCREMENT 的信息，请参见 第 17.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。有关 AUTO_INCREMENT 和 MySQL 复制的信息，请参见 第 19.5.1.1 节，“复制与 AUTO_INCREMENT”。For information about InnoDB and AUTO_INCREMENT, see Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”. For information about AUTO_INCREMENT and MySQL Replication, see Section 19.5.1.1, “Replication and AUTO_INCREMENT”.

COMMENTCOMMENT

可以使用 COMMENT 选项为列指定注释，最长可达 1024 个字符。该注释可以通过 SHOW CREATE TABLE 和 SHOW FULL COLUMNS 语句显示。它还会显示在信息模式 COLUMNS 表的 COLUMN_COMMENT 列中。A comment for a column can be specified with the COMMENT option, up to 1024 characters long. The comment is displayed by the SHOW CREATE TABLE and SHOW FULL COLUMNS statements. It is also shown in the COLUMN_COMMENT column of the Information Schema COLUMNS table.

COLUMN_FORMATCOLUMN_FORMAT

在 NDB Cluster，还可以为 NDB，使用 COLUMN_FORMAT。允许的列格式包括 FIXEDDYNAMIC以及 DEFAULTFIXED 用于指定固定宽度存储，DYNAMIC 允许列为可变宽度，DEFAULT使列使用固定宽度或可变宽度存储，具体取决于列的数据类型（可能被 ROW_FORMAT 指定符覆盖）。In NDB Cluster, it is also possible to specify a data storage format for individual columns of NDB tables using COLUMN_FORMAT. Permissible column formats are FIXED, DYNAMIC, and DEFAULT. FIXED is used to specify fixed-width storage, DYNAMIC permits the column to be variable-width, and DEFAULT causes the column to use fixed-width or variable-width storage as determined by the column's data type (possibly overridden by a ROW_FORMAT specifier).

对于 NDB 表，COLUMN_FORMAT为 FIXED。For NDB tables, the default value for COLUMN_FORMAT is FIXED.

在 NDB 集群中，定义为 COLUMN_FORMAT=FIXED量为 8188 字节。欲了解更多信息及可能的变通方法，请参见 第 25.2.7.5 节，“与 NDB 集群中数据库对象相关的限制”。In NDB Cluster, the maximum possible offset for a column defined with COLUMN_FORMAT=FIXED is 8188 bytes. For more information and possible workarounds, see Section 25.2.7.5, “Limits Associated with Database Objects in NDB Cluster”.

COLUMN_FORMAT 目前对使用存储引擎的表列没有影响，除了 NDBMySQL 8.4 默然忽略了 COLUMN_FORMAT。COLUMN_FORMAT currently has no effect on columns of tables using storage engines other than NDB. MySQL 8.4 silently ignores COLUMN_FORMAT.

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE选项用于指定主存储和次级存储引擎的列属性。这些选项保留以供未来使用。ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options are used to specify column attributes for primary and secondary storage engines. The options are reserved for future use.

分配给此选项的值是包含有效 JSON 文档的字符串字面量或空字符串（''）。无效的 JSON 会被拒绝。The value assigned to this option is a string literal containing a valid JSON document or an empty string (''). Invalid JSON is rejected.

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

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 的值可以重复而不会出错。在这种情况下，将使用最后指定的值。ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 的值不会被服务器检查，也不会在更改表的存储引擎时清除。ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

STORAGESTORAGE

对于 NDB 表，可以通过 STORAGE中。STORAGE DISK 使列存储在磁盘上，STORAGE MEMORY内存存储被使用。CREATE TABLE 语句仍必须包含 TABLESPACE 子句：For NDB tables, it is possible to specify whether the column is stored on disk or in memory by using a STORAGE clause. STORAGE DISK causes the column to be stored on disk, and STORAGE MEMORY causes in-memory storage to be used. The CREATE TABLE statement used must still include a TABLESPACE clause:

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)

For NDB 表，STORAGE DEFAULT 等价于 STORAGE MEMORY。For NDB tables, STORAGE DEFAULT is equivalent to STORAGE MEMORY.

STORAGE 子句对使用存储引擎的表无影响，除了 NDB。STORAGE 关键字仅在构建 mysqld;在其他版本的 MySQL 中无法识别该 mod，任何尝试使用 STORAGE 关键字都会引起语法错误。The STORAGE clause has no effect on tables using storage engines other than NDB. The STORAGE keyword is supported only in the build of mysqld that is supplied with NDB Cluster; it is not recognized in any other version of MySQL, where any attempt to use the STORAGE keyword causes a syntax error.

GENERATED ALWAYSGENERATED ALWAYS

用于指定生成的列表达式。关于 generated columns 的信息，请参见 第 15.1.20.8 节，“创建 TABLE 和生成列”。Used to specify a generated column expression. For information about generated columns, see Section 15.1.20.8, “CREATE TABLE and Generated Columns”.

存储的生成列可以被索引。InnoDB 支持在 virtual generated columns.参见 第 15.1.20.9 节，”次级索引和生成列“。Stored generated columns can be indexed. InnoDB supports secondary indexes on virtual generated columns. See Section 15.1.20.9, “Secondary Indexes and Generated Columns”.

CONSTRAINT symbolCONSTRAINT symbol

CONSTRAINT symbol 子句可用于命名约束。如果未给出该子句，或在 symbol 会自动生成约束名称，但下文提到的例外。symbol 值（如果使用），必须在每个模式（数据库）和约束类型中唯一。重复的 symbol 会导致错误。另见关于生成约束标识符长度限制的讨论，见第 11.2.1 节，“标识符长度限制”。The CONSTRAINT symbol clause may be given to name a constraint. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, MySQL automatically generates a constraint name, with the exception noted below. The symbol value, if used, must be unique per schema (database), per constraint type. A duplicate symbol results in an error. See also the discussion about length limits of generated constraint identifiers at Section 11.2.1, “Identifier Length Limits”.

SQL 标准规定，所有类型的约束（主键、唯一索引、外键、检查）属于同一个命名空间。在 MySQL 中，每种约束类型在每个模式下都有自己的命名空间。因此，每种类型的约束名称在每个模式下必须唯一，但不同类型的约束可以有相同的名称。The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema. Consequently, names for each type of constraint must be unique per schema, but constraints of different types can have the same name.

PRIMARY KEYPRIMARY KEY

唯一索引，所有键列必须定义为 NOT NULL如果未明确声明为 NOT NULL 会隐式（且无声地）声明。一个表只能有一个 PRIMARY KEY。PRIMARY KEY始终是 PRIMARY因此不能用作任何其他类型的索引名称。A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

如果你没有 PRIMARY KEY，且应用程序在表中要求 PRIMARY KEY 会返回第一个 UNIQUE 索引，且没有 NULL 列，作为 PRIMARY KEY。If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

在 InnoDB 表中，保持 PRIMARY KEY 简短，以减少次级索引的存储开销。每个次级索引条目包含对应行的主键列副本。（参见 第 17.6.2.1 节，“聚类和次级索引”）In InnoDB tables, keep the PRIMARY KEY short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (See Section 17.6.2.1, “Clustered and Secondary Indexes”.)

在创建的表格中，PRIMARY KEY放在首位，然后是所有UNIQUE索引，最后是非唯一索引。这有助于 MySQL 优化器优先选择使用哪个索引，并更快地检测重复的UNIQUE密钥。In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys.

PRIMARY KEY可以是多列索引。然而，你不能在列规范中使用 PRIMARY KEY key 属性来创建多列索引。这样做只会将该单列标记为主列。你必须使用单独的 PRIMARY KEY(key_part, ...)子句。A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(key_part, ...) clause.

如果一个表的 PRIMARY KEY UNIQUE NOT NULL 索引，且仅由一个整数类型的列组成，你可以使用 _rowid 来指代索引列 SELECT 语句，如 Unique Indexes。If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as described in Unique Indexes.

在 MySQL 中，PRIMARY KEY是 PRIMARY。对于其他索引，如果你不分配名称，索引将与第一个索引列相同，并可选择添加后缀（_2，_3，...）以实现唯一。你可以使用 SHOW INDEX FROM tbl_name。参见 第 15.7.7.23 节，“SHOW INDEX Statement”。In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See Section 15.7.7.23, “SHOW INDEX Statement”.

KEY | INDEXKEY | INDEX

KEY 通常是 INDEX。密钥属性 PRIMARY KEY，也可以在列定义中指定为 KEY。这是为了与其他数据库系统的兼容性而实现的。KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

UNIQUEUNIQUE

UNIQUE 索引创建了一个约束，使得索引中的所有值必须不同。如果你尝试添加一个键值与现有行匹配的新行，就会发生错误。对于所有引擎来说，UNIQUE 索引允许对可包含 NULLA UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length.

如果一个表的 PRIMARY KEY UNIQUE NOT NULL 索引，且仅由一个整数类型的列组成，你可以使用 _rowid 来指代索引列 SELECT 语句，如 Unique Indexes。If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as described in Unique Indexes.

FULLTEXTFULLTEXT

A FULLTEXT index is a special type of index used for full-text searches. Only the InnoDB以及MyISAM storage engines support FULLTEXT indexes. They can be created only from CHAR, VARCHAR，以及TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 14.9, “Full-Text Search Functions”, for details of operation. A WITH PARSER clause can be specified as an index_option value to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is valid only for FULLTEXT索引的表。InnoDB以及MyISAM support full-text parser plugins. See Full-Text Parser Plugins以及Writing Full-Text Parser Plugins for more information.A FULLTEXT index is a special type of index used for full-text searches. Only the InnoDB and MyISAM storage engines support FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 14.9, “Full-Text Search Functions”, for details of operation. A WITH PARSER clause can be specified as an index_option value to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is valid only for FULLTEXT indexes. InnoDB and MyISAM support full-text parser plugins. See Full-Text Parser Plugins and Writing Full-Text Parser Plugins for more information.

SPATIALSPATIAL

你可以创建 SPATIAL 索引，用于空间数据类型。空间类型仅支持InnoDB 和 MyISAM 表，索引列必须声明为 NOT NULL参见 第 13.4 节，“空间数据类型”。You can create SPATIAL indexes on spatial data types. Spatial types are supported only for InnoDB and MyISAM tables, and indexed columns must be declared as NOT NULL. See Section 13.4, “Spatial Data Types”.

FOREIGN KEYFOREIGN KEY

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. For definition and option information, see reference_definition，以及reference_option.MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. For definition and option information, see reference_definition, and reference_option.

采用 InnoDB不支持外键。更多信息请参见 第 26.6 节，“分区的限制与限制”。Partitioned tables employing the InnoDB storage engine do not support foreign keys. See Section 26.6, “Restrictions and Limitations on Partitioning”, for more information.

CHECKCHECK

CHECK 子句允许在表行中检查约束的创建。参见 第 15.1.20.6 节，“CHECK Constraints”。The CHECK clause enables the creation of constraints to be checked for data values in table rows. See Section 15.1.20.6, “CHECK Constraints”.

key_part

index_typeindex_type

一些存储引擎允许您在创建索引时指定索引类型。语法为index_type specifier is USING type_name.Some storage engines permit you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name.

示例：Example:

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

USING位于索引列列表之后。它可以放在列列表之前，但该位置对该选项的支持已被弃用，预计未来 MySQL 版本会移除。The preferred position for USING is after the index column list. It can be given before the column list, but support for use of the option in that position is deprecated and you should expect it to be removed in a future MySQL release.

index_option

index_optionvalues 为索引指定附加选项。index_option values specify additional options for an index.

有关允许的index_option值的更多信息，请参见第 15.1.15 节，“CREATE INDEX 语句”。有关索引的更多信息，请参见第 10.3.1 节，“MySQL 如何使用索引”.For more information about permissible index_option values, see Section 15.1.15, “CREATE INDEX Statement”. For more information about indexes, see Section 10.3.1, “How MySQL Uses Indexes”.

reference_definition

表，如果没有任何可变长度列（reference_definition syntax details and examples, see Section 15.1.20.5, “FOREIGN KEY Constraints”.For reference_definition syntax details and examples, see Section 15.1.20.5, “FOREIGN KEY Constraints”.

InnoDB 和 NDB 表支持检查外键约束。被引用表的列必须始终明确命名。支持ON DELETE 和 ON UPDATE对外键的操作。如需更详细的信息和示例，请参见 第 15.1.20.5 节，“FOREIGN KEY Constraints”。InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE actions on foreign keys are supported. For more detailed information and examples, see Section 15.1.20.5, “FOREIGN KEY Constraints”.

对于其他存储引擎，MySQL Server 解析并忽略FOREIGN KEY语法 CREATE TABLE 语句。For other storage engines, MySQL Server parses and ignores the FOREIGN KEY syntax in CREATE TABLE statements.

reference_option

有关 RESTRICT、CASCADE、SET NULLNO ACTION，以及 SET DEFAULT OPTIONS，请参见 第 15.1.20.5 节，“FOREIGN KEY Constraints”。For information about the RESTRICT, CASCADE, SET NULL, NO ACTION, and SET DEFAULT options, see Section 15.1.20.5, “FOREIGN KEY Constraints”.

ENGINEENGINE

指定表的存储引擎，使用下表中所示名称之一。引擎名称可为无引号或引号。引号名称 'DEFAULT' 会被识别但被忽略。Specifies the storage engine for the table, using one of the names shown in the following table. The engine name can be unquoted or quoted. The quoted name 'DEFAULT' is recognized but ignored.

默认情况下，如果指定了不可用的存储引擎，则语句会失败并报错。可以通过从服务器 SQL 模式中删除NO_ENGINE_SUBSTITUTION（参见第 7.1.11 节，“服务器 SQL 模式”）来覆盖此行为，使 MySQL 允许用默认存储引擎替代指定的存储引擎。通常在这种情况下，默认存储引擎是InnoDB，这是default_storage_engine系统变量的默认值。当NO_ENGINE_SUBSTITUTION被禁用时，如果未遵循存储引擎规范，将出现警告。By default, if a storage engine is specified that is not available, the statement fails with an error. You can override this behavior by removing NO_ENGINE_SUBSTITUTION from the server SQL mode (see Section 7.1.11, “Server SQL Modes”) so that MySQL allows substitution of the specified engine with the default storage engine instead. Normally in such cases, this is InnoDB, which is the default value for the default_storage_engine system variable. When NO_ENGINE_SUBSTITUTION is disabled, a warning occurs if the storage engine specification is not honored.

AUTOEXTEND_SIZE

定义当InnoDB表空间满时扩展表空间大小的量。该设置必须是 4MB 的倍数。默认设置为 0，这会根据隐式默认行为扩展表空间。有关更多信息，请参见第 17.6.3.9 节，“表空间 AUTOEXTEND_SIZE 配置”。Defines the amount by which InnoDB extends the size of the tablespace when it becomes full. The setting must be a multiple of 4MB. The default setting is 0, which causes the tablespace to be extended according to the implicit default behavior. For more information, see Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.

AUTO_INCREMENT

表的初始 AUTO_INCREMENT 值。在 MySQL 8.4 中，这适用于 MyISAM、MEMORY、InnoDB 和 ARCHIVE 表。对于不支持 AUTO_INCREMENT 表选项的存储引擎，在创建表后，可插入一个值比所需值小一的 “dummy” 行，然后删除该 dummy 行，以设置第一个自增值。The initial AUTO_INCREMENT value for the table. In MySQL 8.4, this works for MyISAM, MEMORY, InnoDB, and ARCHIVE tables. To set the first auto-increment value for engines that do not support the AUTO_INCREMENT table option, insert a “dummy” row with a value one less than the desired value after creating the table, and then delete the dummy row.

对于支持在 CREATE TABLE 语句中使用 AUTO_INCREMENT 表选项的引擎，您也可以使用 ALTER TABLE tbl_name AUTO_INCREMENT = N 来重置 AUTO_INCREMENT 值。该值不能设置为低于当前列中的最大值。For engines that support the AUTO_INCREMENT table option in CREATE TABLE statements, you can also use ALTER TABLE tbl_name AUTO_INCREMENT = N to reset the AUTO_INCREMENT value. The value cannot be set lower than the maximum value currently in the column.

AVG_ROW_LENGTH

表中平均行长度的近似值。仅当表为具有可变大小行的大表时，才需要设置此值。An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows.

当你创建一个 MyISAM 表时，MySQL 会使用 MAX_ROWS 和 AVG_ROW_LENGTH 选项的乘积来决定生成表的大小。如果你没有指定任一选项，MyISAM 数据和索引文件的最大默认大小为 256TB。（如果你的操作系统不支持如此大的文件，表的大小将受文件大小限制约束。）如果你想减少指针大小以使索引更小更快，并且你并不真正需要大文件，你可以通过设置 myisam_data_pointer_size 系统变量来降低默认指针大小。（参见 第 7.1.8 节“服务器系统变量”。）如果你希望所有表能够超过默认限制增长，并且愿意让表稍微变慢和占用更多空间，你可以通过设置该变量来增加默认指针大小。将值设置为 7 允许表大小最多达到 65,536TB。When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table is. If you don't specify either option, the maximum size for MyISAM data and index files is 256TB by default. (If your operating system does not support files that large, table sizes are constrained by the file size limit.) If you want to keep down the pointer sizes to make the index smaller and faster and you don't really need big files, you can decrease the default pointer size by setting the myisam_data_pointer_size system variable. (See Section 7.1.8, “Server System Variables”.) If you want all your tables to be able to grow above the default limit and are willing to have your tables slightly slower and larger than necessary, you can increase the default pointer size by setting this variable. Setting the value to 7 permits table sizes up to 65,536TB.

[DEFAULT] CHARACTER SET

指定表的默认字符集。CHARSET 是 CHARACTER SET 的同义词。如果字符集名称为 DEFAULT，则使用数据库字符集。Specifies a default character set for the table. CHARSET is a synonym for CHARACTER SET. If the character set name is DEFAULT, the database character set is used.

CHECKSUMCHECKSUM

如果你希望 MySQL 为所有行维护实时校验和（即表发生变化时 MySQL 自动更新的校验和），请将其设置为 1。这会使表的更新稍慢，但也使查找损坏表更容易。CHECKSUM TABLE 语句报告校验和。（仅适用于 MyISAM。）Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)

[DEFAULT] COLLATE

为表指定默认排序规则。Specifies a default collation for the table.

COMMENTCOMMENT

表的注释，最长可达 2048 个字符。A comment for the table, up to 2048 characters long.

您可以使用 table_option COMMENT 子句为表设置 InnoDB MERGE_THRESHOLD 值。请参见 第 17.8.11 节，“配置索引页的合并阈值”。You can set the InnoDB MERGE_THRESHOLD value for a table using the table_option COMMENT clause. See Section 17.8.11, “Configuring the Merge Threshold for Index Pages”.

设置 NDB_TABLE 选项。 在创建 NDB 表的 CREATE TABLE 中，或者在修改表的 ALTER TABLE 语句中，表注释也可以用来指定一个到四个 NDB_TABLE 选项，即 NOLOGGING、READ_BACKUP、PARTITION_BALANCE 或 FULLY_REPLICATED，作为一组名称-值对，如有需要用逗号分隔，紧跟在以 NDB_TABLE= 开头的引号注释文本之后。下面显示了使用此语法的示例语句（加粗文本）：Setting NDB_TABLE options.  The table comment in a CREATE TABLE that creates an NDB table or an ALTER TABLE statement which alters one can also be used to specify one to four of the NDB_TABLE options NOLOGGING, READ_BACKUP, PARTITION_BALANCE, or FULLY_REPLICATED as a set of name-value pairs, separated by commas if need be, immediately following the string NDB_TABLE= that begins the quoted comment text. An example statement using this syntax is shown here (emphasized text):

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

引号内的字符串不允许有空格。字符串不区分大小写。Spaces are not permitted within the quoted string. The string is case-insensitive.

该注释作为 SHOW CREATE TABLE 输出的一部分显示。注释的文本也可以通过 MySQL 信息模式的 TABLES 表的 TABLE_COMMENT 列获得。The comment is displayed as part of the output of SHOW CREATE TABLE. The text of the comment is also available as the TABLE_COMMENT column of the MySQL Information Schema TABLES table.

此注释语法也支持 ALTER TABLE 语句用于 NDB 表。请注意，与 ALTER TABLE 一起使用的表注释会替换表先前可能已有的任何现有注释。This comment syntax is also supported with ALTER TABLE statements for NDB tables. Keep in mind that a table comment used with ALTER TABLE replaces any existing comment which the table might have had previously.

在表注释中设置 MERGE_THRESHOLD 选项不支持 NDB 表（该选项将被忽略）。Setting the MERGE_THRESHOLD option in table comments is not supported for NDB tables (it is ignored).

有关完整的语法信息和示例，请参见 第 15.1.20.12 节，“设置 NDB 注释选项”。For complete syntax information and examples, see Section 15.1.20.12, “Setting NDB Comment Options”.

COMPRESSIONCOMPRESSION

用于 InnoDB 表页面级压缩的压缩算法。支持的值包括 Zlib、LZ4 和 None。COMPRESSION 属性是随着透明页面压缩功能引入的。页面压缩仅支持位于 file-per-table 表空间的 InnoDB 表，并且仅在支持稀疏文件和孔打孔的 Linux 和 Windows 平台上可用。有关更多信息，请参见 第 17.9.2 节，“InnoDB 页面压缩”。The compression algorithm used for page level compression for InnoDB tables. Supported values include Zlib, LZ4, and None. The COMPRESSION attribute was introduced with the transparent page compression feature. Page compression is only supported with InnoDB tables that reside in file-per-table tablespaces, and is only available on Linux and Windows platforms that support sparse files and hole punching. For more information, see Section 17.9.2, “InnoDB Page Compression”.

CONNECTIONCONNECTION

FEDERATED 表的连接字符串。The connection string for a FEDERATED table.

DATA DIRECTORY, INDEX DIRECTORYDATA DIRECTORY, INDEX DIRECTORY

对于 InnoDB，DATA DIRECTORY='directory' 子句允许在数据目录之外创建表。必须启用 innodb_file_per_table 变量才能使用 DATA DIRECTORY 子句。必须指定完整的目录路径，并为 InnoDB 所知。有关更多信息，请参见 第 17.6.1.2 节，“外部创建表”。For InnoDB, the DATA DIRECTORY='directory' clause permits creating tables outside of the data directory. The innodb_file_per_table variable must be enabled to use the DATA DIRECTORY clause. The full directory path must be specified, and known to InnoDB. For more information, see Section 17.6.1.2, “Creating Tables Externally”.

在创建 MyISAM，你可以使用 DATA DIRECTORY='directory' 子句，INDEX DIRECTORY='directory' 子句，或者两者兼用。它们指定了 MyISAM 表的数据文件和索引文件。与 InnoDB，MySQL 在创建 MyISAM，同时使用 DATA DIRECTORY 或 INDEX DIRECTORY 选项。文件是在指定的目录中创建的。When creating MyISAM tables, you can use the DATA DIRECTORY='directory' clause, the INDEX DIRECTORY='directory' clause, or both. They specify where to put a MyISAM table's data file and index file, respectively. Unlike InnoDB tables, MySQL does not create subdirectories that correspond to the database name when creating a MyISAM table with a DATA DIRECTORY or INDEX DIRECTORY option. Files are created in the directory that is specified.

你必须拥有 FILE 权限，才能使用 DATA DIRECTORY 或 INDEX DIRECTORY 表格选项。You must have the FILE privilege to use the DATA DIRECTORY or INDEX DIRECTORY table option.

这些选项仅在你未使用 --skip-symbolic-links 选项时有效。你的操作系统还必须有一个可用的、线程安全的 realpath() 调用。详见 第 10.12.2.2 节，“使用 MyISAM 表的符号链接”，以获取更完整的信息。These options work only when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See Section 10.12.2.2, “Using Symbolic Links for MyISAM Tables on Unix”, for more complete information.

如果创建的 MyISAM 表没有 DATA DIRECTORY，则 .MYD 文件会在数据库目录中创建。默认情况下，如果 MyISAM 找到已有的 .MYD 文件会覆盖该文件。同样的情况也适用于 .MYI对于没有 INDEX DIRECTORY。为抑制此行为，服务器启动时设置 --keep_files_on_create 选项，此时 MyISAM 不会覆盖现有文件，而是返回错误。If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is created in the database directory. By default, if MyISAM finds an existing .MYD file in this case, it overwrites it. The same applies to .MYI files for tables created with no INDEX DIRECTORY option. To suppress this behavior, start the server with the --keep_files_on_create option, in which case MyISAM does not overwrite existing files and returns an error instead.

如果创建了 MyISAM 表，则使用 DATA DIRECTORY，或 INDEX DIRECTORY 选项，以及现有的 .MYD 或 .MYI 文件时，MyISAM 总是返回错误，且不会覆盖指定目录中的文件。If a MyISAM table is created with a DATA DIRECTORY or INDEX DIRECTORY option and an existing .MYD or .MYI file is found, MyISAM always returns an error, and does not overwrite a file in the specified directory.

DELAY_KEY_WRITEDELAY_KEY_WRITE

如果你想延迟表的密钥更新直到表关闭，请将其设为 1。请参见 delay_key_write系统变量的描述，第 7.1.8 节，“Server System Variables”。（MyISAM。）Set this to 1 if you want to delay key updates for the table until the table is closed. See the description of the delay_key_write system variable in Section 7.1.8, “Server System Variables”. (MyISAM only.)

ENCRYPTIONENCRYPTION

ENCRYPTION 子句用于启用或禁用 InnoDB 表的页面级数据加密。在启用加密之前，必须安装和配置 keyring 插件。ENCRYPTION 子句可以在创建文件表空间中的表时指定，也可以在创建通用表空间中的表时指定。The ENCRYPTION clause enables or disables page-level data encryption for an InnoDB table. A keyring plugin must be installed and configured before encryption can be enabled. The ENCRYPTION clause can be specified when creating a table in an a file-per-table tablespace, or when creating a table in a general tablespace.

ENCRYPTION 选项仅受 InnoDB 存储引擎支持；因此，它仅在默认存储引擎为 InnoDB 时，或者 CREATE TABLE 语句也指定 ENGINE=InnoDB 时有效。否则，该语句将被拒绝，并返回 ER_CHECK_NOT_IMPLEMENTED 错误。The ENCRYPTION option is supported only by the InnoDB storage engine; thus it works only if the default storage engine is InnoDB, or if the CREATE TABLE statement also specifies ENGINE=InnoDB. Otherwise the statement is rejected with ER_CHECK_NOT_IMPLEMENTED.

表继承默认模式加密，且ENCRYPTION未指定某子句。如果table_encryption_privilege_check该变量被启用，TABLE_ENCRYPTION_ADMIN则需要该权限创建与ENCRYPTION默认模式加密不同的子句设置表。在创建通用表空间的表时，表和表空间的加密必须匹配。A table inherits the default schema encryption if an ENCRYPTION clause is not specified. If the table_encryption_privilege_check variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to create a table with an ENCRYPTION clause setting that differs from the default schema encryption. When creating a table in a general tablespace, table and tablespace encryption must match.

在使用不支持加密的存储引擎时，不允许指定除了 'N' 或 '' 之外的 ENCRYPTION 子句值。Specifying an ENCRYPTION clause with a value other than 'N' or '' is not permitted when using a storage engine that does not support encryption.

有关更多信息，请参见第 17.13 节，“InnoDB 静态数据加密”。For more information, see Section 17.13, “InnoDB Data-at-Rest Encryption”.

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 选项用于指定主存储引擎和辅助存储引擎的表属性。这些选项为将来使用保留。The ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options are used to specify table attributes for primary and secondary storage engines. The options are reserved for future use.

分配给这两个选项中的任意一个的值必须是包含有效 JSON 文档的字符串字面量，或者是空字符串 ('')。无效的 JSON 将被拒绝。The value assigned to either of these options must be a string literal containing a valid JSON document or an empty string (''). Invalid JSON is rejected.

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

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 的值可以重复而不会出错。在这种情况下，将使用最后指定的值。ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 的值不会被服务器检查，也不会在更改表的存储引擎时清除。ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

INSERT_METHODINSERT_METHOD

如果你想将数据插入到 MERGE 表中，必须用 INSERT_METHOD该行应插入的表。INSERT_METHOD 是仅适用于 MERGE 表的选项。使用 FIRST 或 LAST 来让插入进入第一个或最后一个表，或者使用 NO 来防止插入。参见 第 18.7 节，“MERGE 存储引擎”。If you want to insert data into a MERGE table, you must specify with INSERT_METHOD the table into which the row should be inserted. INSERT_METHOD is an option useful for MERGE tables only. Use a value of FIRST or LAST to have inserts go to the first or last table, or a value of NO to prevent inserts. See Section 18.7, “The MERGE Storage Engine”.

KEY_BLOCK_SIZE

对于 MyISAM 表，KEY_BLOCK_SIZE可选地指定索引键块的字节大小。该值被视为提示;如有需要，可以使用不同的大小。KEY_BLOCK_SIZE 为单个索引定义指定值，覆盖表级 KEY_BLOCK_SIZE值。For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides the table-level KEY_BLOCK_SIZE value.

表，如果没有任何可变长度列（InnoDB tables, KEY_BLOCK_SIZE specifies the page size in kilobytes to use for compressed InnoDB tables. The KEY_BLOCK_SIZE value is treated as a hint; a different size could be used by InnoDB if necessary. KEY_BLOCK_SIZE can only be less than or equal to the innodb_page_size value. A value of 0 represents the default compressed page size, which is half of the innodb_page_size value. Depending on innodb_page_size, possible KEY_BLOCK_SIZE values include 0, 1, 2, 4, 8, and 16. See Section 17.9.1, “InnoDB Table Compression” for more information.For InnoDB tables, KEY_BLOCK_SIZE specifies the page size in kilobytes to use for compressed InnoDB tables. The KEY_BLOCK_SIZE value is treated as a hint; a different size could be used by InnoDB if necessary. KEY_BLOCK_SIZE can only be less than or equal to the innodb_page_size value. A value of 0 represents the default compressed page size, which is half of the innodb_page_size value. Depending on innodb_page_size, possible KEY_BLOCK_SIZE values include 0, 1, 2, 4, 8, and 16. See Section 17.9.1, “InnoDB Table Compression” for more information.

Oracle 建议在指定 innodb_strict_mode 用于 InnoDB。当启用 innodb_strict_mode 时，指定无效的 KEY_BLOCK_SIZE 值会返回错误。如果 innodb_strict_mode 被禁用，则无效的 KEY_BLOCK_SIZE 值会触发警告，KEY_BLOCK_SIZE选项将被忽略。Oracle recommends enabling innodb_strict_mode when specifying KEY_BLOCK_SIZE for InnoDB tables. When innodb_strict_mode is enabled, specifying an invalid KEY_BLOCK_SIZE value returns an error. If innodb_strict_mode is disabled, an invalid KEY_BLOCK_SIZE value results in a warning, and the KEY_BLOCK_SIZE option is ignored.

Create_options列，作为 SHOW TABLE STATUS 报告表实际使用的实际 KEY_BLOCK_SIZE，SHOW CREATE TABLE”。The Create_options column in response to SHOW TABLE STATUS reports the actual KEY_BLOCK_SIZE used by the table, as does SHOW CREATE TABLE.

InnoDB 仅支持 KEY_BLOCK_SIZE 在表层面。InnoDB only supports KEY_BLOCK_SIZE at the table level.

KEY_BLOCK_SIZE 不支持 32KB 和 64KB 的 innodb_page_size 值。InnoDB 表压缩不支持这些页面大小。KEY_BLOCK_SIZE is not supported with 32KB and 64KB innodb_page_size values. InnoDB table compression does not support these pages sizes.

InnoDB 在创建临时表时不支持 KEY_BLOCK_SIZE 选项。InnoDB does not support the KEY_BLOCK_SIZE option when creating temporary tables.

MAX_ROWSMAX_ROWS

你计划在表中存储的最大行数。这不是一个硬性限制，而是给存储引擎的一个提示，表必须能够至少存储这么多行。The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows.

NDB存储引擎将该值视为最大值。如果你计划创建非常大的 NDB 集群表（包含数百万行），你应该使用这个选项确保 NDB 在用于存储表主键哈希的哈希表中分配足够的索引槽，方法是设置 MAX_ROWS = 2 * rows其中 rows你预计插入到表中的行数。The NDB storage engine treats this value as a maximum. If you plan to create very large NDB Cluster tables (containing millions of rows), you should use this option to insure that NDB allocates sufficient number of index slots in the hash table used for storing hashes of the table's primary keys by setting MAX_ROWS = 2 * rows, where rows is the number of rows that you expect to insert into the table.

最大 MAX_ROWS值为 4294967295;更大的值会被截断到这个限制。The maximum MAX_ROWS value is 4294967295; larger values are truncated to this limit.

MIN_ROWSMIN_ROWS

你计划在表中存储的最小行数。MEMORY storage engine 使用此选项作为内存使用提示。The minimum number of rows you plan to store in the table. The MEMORY storage engine uses this option as a hint about memory use.

PACK_KEYSPACK_KEYS

仅在 MyISAM。如果你想让索引更小，将此选项设为 1。这通常会使更新变慢，读取更快。将选项设置为 0 会禁用所有密钥打包。将其设置为 DEFAULT 会让存储引擎只打包长 CHAR， VARCHAR BINARY或者VARBINARY 列。Takes effect only with MyISAM tables. Set this option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT tells the storage engine to pack only long CHAR, VARCHAR, BINARY, or VARBINARY columns.

如果你不使用 PACK_KEYS，默认是打包字符串，但不打包数字。如果你使用 PACK_KEYS=1，数字也会被打包。If you do not use PACK_KEYS, the default is to pack strings, but not numbers. If you use PACK_KEYS=1, numbers are packed as well.

在打包二进制数字键时，MySQL 使用前缀压缩：When packing binary number keys, MySQL uses prefix compression:

这意味着如果你在两行连续的行上有许多相同的键，则所有后续的“相同”键通常只占用两个字节（包括指向该行的指针）。与普通情况下后续键占用storage_size_for_key + pointer_size（其中指针大小通常为 4）相比。相反，只有当你有许多相同的数字时，从前缀压缩中才能获得显著的好处。如果所有键完全不同，则每个键会额外使用一个字节，如果该键不是可以有NULL值的键。（在这种情况下，打包键的长度存储在用于标记键是否为NULL的同一个字节中。）This means that if you have many equal keys on two consecutive rows, all following “same” keys usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys takes storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you get a significant benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you use one byte more per key, if the key is not a key that can have NULL values. (In this case, the packed key length is stored in the same byte that is used to mark if a key is NULL.)

PASSWORDPASSWORD

此选项未使用。This option is unused.

ROW_FORMATROW_FORMAT

定义行存储的物理格式。Defines the physical format in which the rows are stored.

在创建禁用严格模式的表时，如果指定的行格式不受支持，则使用存储引擎的默认行格式。表的实际行格式会在Row_format的SHOW TABLE STATUS列中显示。Create_options列显示在CREATE TABLE语句中指定的行格式，SHOW CREATE TABLE也是如此。When creating a table with strict mode disabled, the storage engine's default row format is used if the specified row format is not supported. The actual row format of the table is reported in the Row_format column in response to SHOW TABLE STATUS. The Create_options column shows the row format that was specified in the CREATE TABLE statement, as does SHOW CREATE TABLE.

行格式的选择取决于表使用的存储引擎。Row format choices differ depending on the storage engine used for the table.

对于InnoDB表：For InnoDB tables:

对于 MyISAM 表，选项值可以是 FIXED 或 DYNAMIC，适用于静态或可变长度的行格式。myisampack 将类型设置为 COMPRESSED。参见 第 18.2.3 节，“MyISAM 表存储格式”。For MyISAM tables, the option value can be FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See Section 18.2.3, “MyISAM Table Storage Formats”.

对于 NDB 表，默认的 ROW_FORMAT为 DYNAMICFor NDB tables, the default ROW_FORMAT is DYNAMIC.

START TRANSACTIONSTART TRANSACTION

这是一个内部使用表选项，用于允许 CREATE TABLE ... SELECT在使用支持原子 DDL 的基于行的复制时，SELECT 作为单一原子事务记录在二进制日志中。只有 BINLOG， COMMIT， and ROLLBACK 语句在 CREATE TABLE ... START TRANSACTION相关信息请参见 第 15.1.1 节，“原子数据定义语句支持”。This is an internal-use table option, used to permit CREATE TABLE ... SELECT to be logged as a single, atomic transaction in the binary log when using row-based replication with a storage engine that supports atomic DDL. Only BINLOG, COMMIT, and ROLLBACK statements are permitted after CREATE TABLE ... START TRANSACTION. For related information, see Section 15.1.1, “Atomic Data Definition Statement Support”.

STATS_AUTO_RECALC

指定是否自动重新计算 persistent stats，适用于InnoDB表。值 DEFAULT 使得表的持久统计设置由 innodb_stats_auto_recalc 配置选项决定。值 1 在表中 10% 数据发生变化时，统计数据会被重新计算。0 阻止该表的自动重新计算;在此设置下，执行 ANALYZE TABLE 语句，在对表做重大修改后重新计算统计数据。有关持久统计功能的更多信息，请参见 第 17.8.10.1 节，“配置持久优化器统计参数”。Specifies whether to automatically recalculate persistent statistics for an InnoDB table. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc configuration option. The value 1 causes statistics to be recalculated when 10% of the data in the table has changed. The value 0 prevents automatic recalculation for this table; with this setting, issue an ANALYZE TABLE statement to recalculate the statistics after making substantial changes to the table. For more information about the persistent statistics feature, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

STATS_PERSISTENT

指定是否启用 persistent stats，用于 InnoDB 表。值 DEFAULT 使得表的持久统计设置由 innodb_stats_persistent配置选项决定。值 1 使表的持久统计成为可能，而 0 则关闭此功能。在通过 CREATE TABLE 或 ALTER TABLE后，在将代表性数据加载到表中后，发布 ANALYZE TABLE 语句来计算统计数据，并加载代表性数据到表中。有关持久统计功能的更多信息，请参见 第 17.8.10.1 节，“配置持久优化器统计参数”。Specifies whether to enable persistent statistics for an InnoDB table. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_persistent configuration option. The value 1 enables persistent statistics for the table, while the value 0 turns off this feature. After enabling persistent statistics through a CREATE TABLE or ALTER TABLE statement, issue an ANALYZE TABLE statement to calculate the statistics, after loading representative data into the table. For more information about the persistent statistics feature, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

STATS_SAMPLE_PAGES

在估计索引列的基数和其他统计时，需要采样的索引页数量，例如由ANALYZE TABLE更多信息请参见 第 17.8.10.1 节，“配置持久优化器统计参数”。The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE. For more information, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

TABLESPACETABLESPACE

TABLESPACE 子句可用于创建 InnoDB 表，适用于现有的通用表空间、逐文件表空间或系统表空间。The TABLESPACE clause can be used to create an InnoDB table in an existing general tablespace, a file-per-table tablespace, or the system tablespace.

CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name

你指定的通用表空间必须在使用 TABLESPACE 子句之前存在。有关一般表空间的信息，请参见 第 17.6.3.3 节，“通用表空间”。The general tablespace that you specify must exist prior to using the TABLESPACE clause. For information about general tablespaces, see Section 17.6.3.3, “General Tablespaces”.

该tablespace_name is a case-sensitive identifier. It may be quoted or unquoted. The forward slash character (“/”) is not permitted. Names beginning with “innodb_” are reserved for special use.The tablespace_name is a case-sensitive identifier. It may be quoted or unquoted. The forward slash character (“/”) is not permitted. Names beginning with “innodb_” are reserved for special use.

要在系统表空间中创建表，请指定 innodb_system作为表空间名称。To create a table in the system tablespace, specify innodb_system as the tablespace name.

CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system

使用 TABLESPACE [=] innodb_system，你可以在系统表空间中放置任意未压缩行格式的表，而不考虑 innodb_file_per_table设置。例如，你可以使用 ROW_FORMAT=DYNAMIC。Using TABLESPACE [=] innodb_system, you can place a table of any uncompressed row format in the system tablespace regardless of the innodb_file_per_table setting. For example, you can add a table with ROW_FORMAT=DYNAMIC to the system tablespace using TABLESPACE [=] innodb_system.

要在逐表文件表空间创建表，表空间名称指定为 innodb_file_per_table。To create a table in a file-per-table tablespace, specify innodb_file_per_table as the tablespace name.

CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table

DATA DIRECTORY子句允许，CREATE TABLE ... TABLESPACE=innodb_file_per_table，但不支持与TABLESPACE。DATA DIRECTORY子句中指定的目录必须已知InnoDB。更多信息请参见 Using the DATA DIRECTORY Clause。The DATA DIRECTORY clause is permitted with CREATE TABLE ... TABLESPACE=innodb_file_per_table but is otherwise not supported for use in combination with the TABLESPACE clause. The directory specified in a DATA DIRECTORY clause must be known to InnoDB. For more information, see Using the DATA DIRECTORY Clause.

STORAGE table 选项仅用于 NDB 表。STORAGE 决定所使用的存储类型，可以是 DISK 或 MEMORY。The STORAGE table option is employed only with NDB tables. STORAGE determines the type of storage used, and can be either of DISK or MEMORY.

TABLESPACE ... STORAGE DISK将表分配到 NDB 集群磁盘表空间。STORAGE DISK前置于，CREATE TABLE否则不可使用TABLESPACE tablespace_name.TABLESPACE ... STORAGE DISK assigns a table to an NDB Cluster Disk Data tablespace. STORAGE DISK cannot be used in CREATE TABLE unless preceded by TABLESPACE tablespace_name.

表，如果没有任何可变长度列（STORAGE MEMORY, the tablespace name is optional, thus, you can use TABLESPACE tablespace_name STORAGE MEMORY or simply STORAGE MEMORY to specify explicitly that the table is in-memory.For STORAGE MEMORY, the tablespace name is optional, thus, you can use TABLESPACE tablespace_name STORAGE MEMORY or simply STORAGE MEMORY to specify explicitly that the table is in-memory.

更多信息请参见 第 25.6.11 节，“NDB 集群磁盘表”。See Section 25.6.11, “NDB Cluster Disk Data Tables”, for more information.

UNIONUNION

用于访问一组相同的 MyISAM 表集合。这仅适用于 MERGE 表。参见 第 18.7 节，“MERGE 存储引擎”。Used to access a collection of identical MyISAM tables as one. This works only with MERGE tables. See Section 18.7, “The MERGE Storage Engine”.

You must have SELECT, UPDATE，以及DELETE privileges for the tables you map to a MERGE权限。You must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table.

PARTITION BYPARTITION BY

If used, a partition_options clause begins with PARTITION BY. This clause contains the function that is used to determine the partition; the function returns an integer value ranging from 1 to num，其中num is the number of partitions. (The maximum number of user-defined partitions which a table may contain is 1024; the number of subpartitions—discussed later in this section—is included in this maximum.)If used, a partition_options clause begins with PARTITION BY. This clause contains the function that is used to determine the partition; the function returns an integer value ranging from 1 to num, where num is the number of partitions. (The maximum number of user-defined partitions which a table may contain is 1024; the number of subpartitions—discussed later in this section—is included in this maximum.)

HASH(expr)HASH(expr)

对一个或多个列进行哈希，生成用于放置和定位行的键。expr 是使用一个或多个表列的表达式。这可以是任何有效的 MySQL 表达式（包括 MySQL 函数），只要能得到一个整数值。例如，这两个都有效 CREATE TABLE 语句 使用 PARTITION BY HASHHashes one or more columns to create a key for placing and locating rows. expr is an expression using one or more table columns. This can be any valid MySQL expression (including MySQL functions) that yields a single integer value. For example, these are both valid CREATE TABLE statements using 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 THAN THAN 或 VALUES IN 子句，PARTITION BY HASHYou may not use either VALUES LESS THAN or VALUES IN clauses with PARTITION BY HASH.

PARTITION BY HASH 使用expr 除以分区数（即模数）的剩余部分。有关示例和更多信息，请参见 第 26.2.4 节，“HASH Partitioning”。PARTITION BY HASH uses the remainder of expr divided by the number of partitions (that is, the modulus). For examples and additional information, see Section 26.2.4, “HASH Partitioning”.

LINEAR 关键字涉及一种略有不同的算法。在这种情况下，存储某一行的分区号是通过一个或多个逻辑 AND 运算的结果计算得到的。有关线性哈希的讨论和示例，请参见 第 26.2.4.1 节，“LINEAR HASH 分区”。The LINEAR keyword entails a somewhat different algorithm. In this case, the number of the partition in which a row is stored is calculated as the result of one or more logical AND operations. For discussion and examples of linear hashing, see Section 26.2.4.1, “LINEAR HASH Partitioning”.

KEY(column_list)

这类似于 HASH，但 MySQL 提供哈希函数以保证数据均匀分布。column_list 参数只是一个或多个表列的列表（最多 16 列）。此示例显示一个通过键分区的简单表，共有 4 个分区：This is similar to HASH, except that MySQL supplies the hashing function so as to guarantee an even data distribution. The column_list argument is simply a list of 1 or more table columns (maximum: 16). This example shows a simple table partitioned by key, with 4 partitions:

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

对于通过键分区的表，可以使用 LINEAR 关键字采用线性分区。这对通过 HASH 分区的表具有相同效果。也就是说，分区号是使用 & 运算符，而不是模运算来找到（参见 第 26.2.4.1 节，“LINEAR HASH 分区” 和 第 26.2.5 节，“KEY 分区”，了解详情）。此示例使用键的线性分区将数据分布到 5 个分区：For tables that are partitioned by key, you can employ linear partitioning by using the LINEAR keyword. This has the same effect as with tables that are partitioned by HASH. That is, the partition number is found using the & operator rather than the modulus (see Section 26.2.4.1, “LINEAR HASH Partitioning”, and Section 26.2.5, “KEY Partitioning”, for details). This example uses linear partitioning by key to distribute data between 5 partitions:

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

ALGORITHM={1 | 2} 选项在 [SUB]PARTITION BY [LINEAR] KEY 中受支持。ALGORITHM=1 使服务器使用与 MySQL 5.1 相同的键哈希函数；ALGORITHM=2 表示服务器使用 MySQL 5.5 及以后的版本中新建 KEY 分区表默认实现并使用的键哈希函数。（使用 MySQL 5.5 及以后版本键哈希函数创建的分区表无法被 MySQL 5.1 服务器使用。）若未指定此选项，则效果与使用 ALGORITHM=2 相同。该选项主要用于在 MySQL 5.1 与之后的 MySQL 版本之间升级或降级 [LINEAR] KEY 分区表，或在 MySQL 5.5 或更高版本服务器上创建可被 MySQL 5.1 服务器使用的 KEY 或 LINEAR KEY 分区表。更多信息，请参见 第 15.1.9.1 节，“ALTER TABLE 分区操作”。The ALGORITHM={1 | 2} option is supported with [SUB]PARTITION BY [LINEAR] KEY. ALGORITHM=1 causes the server to use the same key-hashing functions as MySQL 5.1; ALGORITHM=2 means that the server employs the key-hashing functions implemented and used by default for new KEY partitioned tables in MySQL 5.5 and later. (Partitioned tables created with the key-hashing functions employed in MySQL 5.5 and later cannot be used by a MySQL 5.1 server.) Not specifying the option has the same effect as using ALGORITHM=2. This option is intended for use chiefly when upgrading or downgrading [LINEAR] KEY partitioned tables between MySQL 5.1 and later MySQL versions, or for creating tables partitioned by KEY or LINEAR KEY on a MySQL 5.5 or later server which can be used on a MySQL 5.1 server. For more information, see Section 15.1.9.1, “ALTER TABLE Partition Operations”.

mysqldump 将此选项写入带版本注释的内容中。mysqldump writes this option encased in versioned comments.

ALGORITHM=1 会在必要时显示在 SHOW CREATE TABLE 的输出中，使用版本注释的方式与 mysqldump 相同。即使在创建原始表时指定了此选项，ALGORITHM=2 总是会从 SHOW CREATE TABLE 输出中省略。ALGORITHM=1 is shown when necessary in the output of SHOW CREATE TABLE using versioned comments in the same manner as mysqldump. ALGORITHM=2 is always omitted from SHOW CREATE TABLE output, even if this option was specified when creating the original table.

您不能在 VALUES LESS THAN 中使用 VALUES IN 或 PARTITION BY KEY 子句。You may not use either VALUES LESS THAN or VALUES IN clauses with PARTITION BY KEY.

RANGE(expr)

在这种情况下，expr 使用一组 VALUES LESS THAN 运算符显示数值范围。使用范围分区时，必须至少使用 VALUES LESS THAN 定义一个分区。不能在范围分区中使用 VALUES IN。In this case, expr shows a range of values using a set of VALUES LESS THAN operators. When using range partitioning, you must define at least one partition using VALUES LESS THAN. You cannot use VALUES IN with range partitioning.

假设您有一个表，您希望根据包含年份值的列进行分区，按照以下方案。Suppose that you have a table that you wish to partition on a column containing year values, according to the following scheme.

实现这种分区方案的表可以通过如下所示的 CREATE TABLE 语句来实现：A table implementing such a partitioning scheme can be realized by the CREATE TABLE statement shown here:

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 用于指定那些大于其他指定最大值的“剩余”值。PARTITION ... VALUES LESS THAN ... statements work in a consecutive fashion. VALUES LESS THAN MAXVALUE works to specify “leftover” values that are greater than the maximum value otherwise specified.

VALUES LESS THAN 子句按顺序工作，方式类似于许多编程语言（如 C、Java 和 PHP）中 switch ... case 块的 case 部分。也就是说，这些子句必须按顺序排列，使每个连续的 VALUES LESS THAN 中指定的上限大于前一个的上限，并且引用 MAXVALUE 的子句必须最后出现。VALUES LESS THAN clauses work sequentially in a manner similar to that of the case portions of a switch ... case block (as found in many programming languages such as C, Java, and PHP). That is, the clauses must be arranged in such a way that the upper limit specified in each successive VALUES LESS THAN is greater than that of the previous one, with the one referencing MAXVALUE coming last of all in the list.

RANGE COLUMNS(column_list)

这种 RANGE 的变体便于对使用多列范围条件的查询进行分区裁剪（即具有类似 WHERE a = 1 AND b < 10 或 WHERE a = 1 AND b = 10 AND c < 10 的条件）。它允许你通过在 COLUMNS 子句中使用列列表，并在每个 PARTITION ... VALUES LESS THAN (value_list) 分区定义子句中使用列值集合，来指定多列的值范围。（在最简单的情况下，该集合仅由单列组成。）column_list 和 value_list 中可引用的最大列数为 16。This variant on RANGE facilitates partition pruning for queries using range conditions on multiple columns (that is, having conditions such as WHERE a = 1 AND b < 10 or WHERE a = 1 AND b = 10 AND c < 10). It enables you to specify value ranges in multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES LESS THAN (value_list) partition definition clause. (In the simplest case, this set consists of a single column.) The maximum number of columns that can be referenced in the column_list and value_list is 16.

column_list 中用于 COLUMNS 子句的列只能包含列名；列表中的每一列必须是以下 MySQL 数据类型之一：整数类型、字符串类型以及时间或日期列类型。使用 BLOB、TEXT、SET、ENUM、BIT 或空间数据类型的列不允许使用；使用浮点数类型的列也不允许使用。你也不能在 COLUMNS 子句中使用函数或算术表达式。The column_list used in the COLUMNS clause may contain only names of columns; each column in the list must be one of the following MySQL data types: the integer types; the string types; and time or date column types. Columns using BLOB, TEXT, SET, ENUM, BIT, or spatial data types are not permitted; columns that use floating-point number types are also not permitted. You also may not use functions or arithmetic expressions in the COLUMNS clause.

在分区定义中使用的 VALUES LESS THAN 子句必须为 COLUMNS() 子句中出现的每一列指定一个字面值；也就是说，每个 VALUES LESS THAN 子句使用的值列表必须包含与 COLUMNS 子句中列数相同的值。如果在 VALUES LESS THAN 子句中使用的值数量与 COLUMNS 子句中的列数不匹配，将导致语句失败，并返回错误 Inconsistency in usage of column lists for partitioning...。你不能在 VALUES LESS THAN 中的任何值中使用 NULL。除了第一列之外，可以在给定列中多次使用 MAXVALUE，如下例所示：The VALUES LESS THAN clause used in a partition definition must specify a literal value for each column that appears in the COLUMNS() clause; that is, the list of values used for each VALUES LESS THAN clause must contain the same number of values as there are columns listed in the COLUMNS clause. An attempt to use more or fewer values in a VALUES LESS THAN clause than there are in the COLUMNS clause causes the statement to fail with the error Inconsistency in usage of column lists for partitioning.... You cannot use NULL for any value appearing in VALUES LESS THAN. It is possible to use MAXVALUE more than once for a given column other than the first, as shown in this example:

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'）。Each value used in a VALUES LESS THAN value list must match the type of the corresponding column exactly; no conversion is made. For example, you cannot use the string '1' for a value that matches a column that uses an integer type (you must use the numeral 1 instead), nor can you use the numeral 1 for a value that matches a column that uses a string type (in such a case, you must use a quoted string: '1').

更多信息请参见 第 26.2.1 节，“RANGE Partitioning”，以及 第 26.4 节，“Partition Pruning”。For more information, see Section 26.2.1, “RANGE Partitioning”, and Section 26.4, “Partition Pruning”.

LIST(expr)LIST(expr)

这在基于表列分配分区时非常有用，该列的值有限，如州或国家代码。在这种情况下，所有与某一州或国家相关的行都可以被分配到一个分区，或者为特定一组州或国家保留一个分区。它类似于 RANGE但只能使用 VALUES IN 来指定每个分区允许的值。This is useful when assigning partitions based on a table column with a restricted set of possible values, such as a state or country code. In such a case, all rows pertaining to a certain state or country can be assigned to a single partition, or a partition can be reserved for a certain set of states or countries. It is similar to RANGE, except that only VALUES IN may be used to specify permissible values for each partition.

VALUES IN 用于匹配的值列表。例如，你可以创建如下的分区方案：VALUES IN is used with a list of values to be matched. For instance, you could create a partitioning scheme such as the following:

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 THAN 按列表进行 PARTITION BY LIST。When using list partitioning, you must define at least one partition using VALUES IN. You cannot use VALUES LESS THAN with PARTITION BY LIST.

LIST COLUMNS(column_list)LIST COLUMNS(column_list)

This variant on LIST facilitates partition pruning for queries using comparison conditions on multiple columns (that is, having conditions such as WHERE a = 5 AND b = 5或者WHERE a = 1 AND b = 10 AND c = 5). It enables you to specify values in multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES IN (value_list) partition definition clause.This variant on LIST facilitates partition pruning for queries using comparison conditions on multiple columns (that is, having conditions such as WHERE a = 5 AND b = 5 or WHERE a = 1 AND b = 10 AND c = 5). It enables you to specify values in multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES IN (value_list) partition definition clause.

用列列表LIST COLUMNS(column_list)值列表VALUES IN(value_list)与中所用列列表RANGE COLUMNS(column_list)值列表VALUES LESS THAN(value_list)，但VALUES IN子句，MAXVALUE不允许，并且您可以使用NULL.The rules governing regarding data types for the column list used in LIST COLUMNS(column_list) and the value list used in VALUES IN(value_list) are the same as those for the column list used in RANGE COLUMNS(column_list) and the value list used in VALUES LESS THAN(value_list), respectively, except that in the VALUES IN clause, MAXVALUE is not permitted, and you may use NULL.

在使用 PARTITION BY LIST COLUMNS 时的 VALUES IN 值列表与在使用 PARTITION BY LIST 时的值列表之间有一个重要区别。当与 PARTITION BY LIST COLUMNS 一起使用时，VALUES IN 子句中的每个元素必须是列值的 集合；每个集合中的值数量必须与 COLUMNS 子句中使用的列数量相同，并且这些值的数据类型必须与列的数据类型匹配（且顺序相同）。在最简单的情况下，这个集合由单个列组成。column_list 中可以使用的列数以及构成 value_list 元素的值的最大数量为 16。There is one important difference between the list of values used for VALUES IN with PARTITION BY LIST COLUMNS as opposed to when it is used with PARTITION BY LIST. When used with PARTITION BY LIST COLUMNS, each element in the VALUES IN clause must be a set of column values; the number of values in each set must be the same as the number of columns used in the COLUMNS clause, and the data types of these values must match those of the columns (and occur in the same order). In the simplest case, the set consists of a single column. The maximum number of columns that can be used in the column_list and in the elements making up the value_list is 16.

下面的 CREATE TABLE 语句定义的表提供了使用 LIST COLUMNS 分区的示例：The table defined by the following CREATE TABLE statement provides an example of a table using LIST COLUMNS partitioning:

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 子句声明的所有分区的总数。The number of partitions may optionally be specified with a PARTITIONS num clause, where num is the number of partitions. If both this clause and any PARTITION clauses are used, num must be equal to the total number of any partitions that are declared using PARTITION clauses.

SUBPARTITION BY

分区可以可选地划分为多个子分区。这可以通过使用可选的 SUBPARTITION BY 子句来指示。子分区可以通过 HASH 或 KEY 进行。两者都可以是 LINEAR。这些与前述的等效分区类型的工作方式相同。（无法按 LIST 或 RANGE 进行子分区。）A partition may optionally be divided into a number of subpartitions. This can be indicated by using the optional SUBPARTITION BY clause. Subpartitioning may be done by HASH or KEY. Either of these may be LINEAR. These work in the same way as previously described for the equivalent partitioning types. (It is not possible to subpartition by LIST or RANGE.)

子分区的数量可以使用 SUBPARTITIONS 关键字后跟整数值来指示。The number of subpartitions can be indicated using the SUBPARTITIONS keyword followed by an integer value.

严格检查 PARTITIONS 或 SUBPARTITIONS 子句中使用的值，并且该值必须遵循以下规则：Rigorous checking of the value used in PARTITIONS or SUBPARTITIONS clauses is applied and this value must adhere to the following rules:

partition_definition

每个分区可以使用partition_definition子句单独定义。构成该子句的各个部分如下：Each partition may be individually defined using a partition_definition clause. The individual parts making up this clause are as follows:

subpartition_definitionsubpartition_definition

The partition definition may optionally contain one or more subpartition_definition clauses. Each of these consists at a minimum of the SUBPARTITION name，其中name is an identifier for the subpartition. Except for the replacement of the PARTITION keyword with SUBPARTITION, the syntax for a subpartition definition is identical to that for a partition definition.The partition definition may optionally contain one or more subpartition_definition clauses. Each of these consists at a minimum of the SUBPARTITION name, where name is an identifier for the subpartition. Except for the replacement of the PARTITION keyword with SUBPARTITION, the syntax for a subpartition definition is identical to that for a partition definition.

子分区必须通过HASH或KEY，且只能在RANGE或LIST分区上进行。参见 第 26.2.6 节，“子分区”。Subpartitioning must be done by HASH or KEY, and can be done only on RANGE or LIST partitions. See Section 26.2.6, “Subpartitioning”.