13.1.18 CREATE TABLE 语句
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| CHECK (expr)
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
}
data_type:
(see Chapter11, Data Types)
key_part:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
}
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE用给定名称创建一个 table。您必须具有该 table 的CREATE特权。
默认情况下,table 是使用InnoDB存储引擎在默认数据库中创建的。如果该 table 存在,没有默认数据库或该数据库不存在,则会发生错误。
MySQL 对 table 的数量没有限制。基础文件系统可能会对 table 示 table 的文件数量有所限制。各个存储引擎可能会强加特定于引擎的约束。 InnoDB
允许多达 40 亿张桌子。
有关 table 的物理 table 示的信息,请参见第 13.1.18.1 节“由 CREATE TABLE 创建的文件”。
CREATE TABLE语句有多个方面,本节以下主题对此进行了介绍:
Table Name
tbl_name
可以将 table 名指定为* db_name.tbl_name
*,以在特定数据库中创建 table。不管是否存在默认数据库(假定数据库存在),此方法都有效。如果使用带引号的标识符,请分别为数据库和 table 名加上引号。例如,Importing“ mydb
.mytbl
”,而不是“ mydb.mytbl
”。
第 9.2 节“架构对象名称”中给出了允许的 table 名的规则。
IF NOT EXISTS
如果 table 存在,则防止发生错误。但是,没有验证现有 table 具有与CREATE TABLE语句指示的结构相同的结构。
Temporary Tables
创建 table 时,可以使用TEMPORARY
关键字。 TEMPORARY
table 仅在当前会话中可见,并且在关闭会话时会自动删除。有关更多信息,请参见第 13.1.18.2 节“ CREATE TEMPORARY TABLE 语句”。
table 克隆和复制
LIKE
使用CREATE TABLE ... LIKE
根据另一个 table 的定义创建一个空 table,包括在原始 table 中定义的所有列属性和索引:
CREATE TABLE new_tbl LIKE orig_tbl;
有关更多信息,请参见第 13.1.18.3 节“ CREATE TABLE ... LIKE 语句”。
[AS] query_expression
要从另一个 table 创建一个 table,请在CREATE TABLE语句的末尾添加一个SELECT语句:
CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
有关更多信息,请参见第 13.1.18.4 节“ CREATE TABLE ... SELECT 语句”。
IGNORE | REPLACE
IGNORE
和REPLACE
选项指示在使用SELECT语句复制 table 时如何处理重复唯一键值的行。
有关更多信息,请参见第 13.1.18.4 节“ CREATE TABLE ... SELECT 语句”。
列数据类型和属性
每个 table 有 4096 列的硬限制,但是对于给定的 table,有效最大值可能更少,并且取决于第 8.4.7 节“table 列数和行大小的限制”中讨论的因素。
data_type
data_type
*table 示列定义中的数据类型。有关可用于指定列数据类型的语法的完整说明,以及有关每种类型的属性的信息,请参见第 11 章,数据类型。
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.7 在字符的字符列定义中解释长度规范。 BINARY和VARBINARY的长度以字节为单位。
- 对于CHAR,VARCHAR,BINARY和VARBINARY列,可以使用
col_name(length)
语法指定索引前缀长度,以创建仅使用列值开头的索引。 BLOB和TEXT列也可以构建索引,但必须提供前缀长度*。对于非二进制字符串类型,前缀长度以字符形式给出;对于二进制字符串类型,前缀长度以字节形式给出。也就是说,索引条目由CHAR,VARCHAR和TEXT列的每个列值的前*length
个字符以及BINARY,VARBINARY和BLOB列的每个列值的前length
*个字节组成。像这样仅索引列值的前缀可以使索引文件小得多。有关索引前缀的其他信息,请参见第 13.1.14 节“ CREATE INDEX 语句”。
仅InnoDB
和MyISAM
存储引擎支持BLOB和TEXT列上的索引。例如:
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 中,只有InnoDB
,MyISAM
和MEMORY
存储引擎支持可以具有NULL
值的列上的索引。在其他情况下,必须将索引列声明为NOT NULL
或错误结果。
DEFAULT
指定列的默认值。有关默认值处理的更多信息,包括列定义不包含显式DEFAULT
值的情况,请参阅第 11.6 节“数据类型默认值”。
如果启用了NO_ZERO_DATE或NO_ZERO_IN_DATE SQL 模式,并且根据该模式设置的日期值默认值不正确,则如果未启用严格 SQL 模式,则CREATE TABLE会产生警告,而如果启用严格模式,则会产生错误。例如,启用NO_ZERO_IN_DATE时,c1 DATE DEFAULT '2010-00-00'
会产生警告。
AUTO_INCREMENT
整数或浮点列可以具有附加属性AUTO_INCREMENT
。在索引AUTO_INCREMENT
列中插入NULL
(推荐)或0
的值时,该列将设置为下一个序列值。通常,这是value+1
,其中* value
*是 table 中当前列的最大值。 AUTO_INCREMENT
序列以1
开头。
要在插入行后检索AUTO_INCREMENT
值,请使用LAST_INSERT_ID() SQL 函数或mysql_insert_id() C API 函数。参见第 12.15 节“信息功能”和第 27.7.6.38 节“ mysql_insert_id()”。
如果启用了NO_AUTO_VALUE_ON_ZERO SQL 模式,则可以将0
在AUTO_INCREMENT
列中存储为0
,而无需生成新的序列值。参见第 5.1.10 节“服务器 SQL 模式”。
每个 table 只能有一个AUTO_INCREMENT
列,必须对其进行索引,并且不能具有DEFAULT
值。 AUTO_INCREMENT
列仅包含正值时才能正常工作。插入负数被视为插入非常大的正数。这样做是为了避免数字从正数“换”为负数时出现精度问题,并确保您不会偶然得到包含0
的AUTO_INCREMENT
列。
对于MyISAM
table,您可以在多列键中指定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_INCREMENT
和 MySQL 复制的信息,请参见第 16.4.1.1 节“复制和 AUTO_INCREMENT”。
COMMENT
列的 Comments 可以使用COMMENT
选项指定,最长为 1024 个字符。该 Comments 由显示创建 table和显示全栏语句显示。
COLUMN_FORMAT
在 NDB 群集中,也可以使用COLUMN_FORMAT
为NDBtable 的各个列指定数据存储格式。允许的列格式为FIXED
,DYNAMIC
和DEFAULT
。 FIXED
用于指定固定宽度存储,DYNAMIC
允许列为可变宽度,DEFAULT
导致列使用固定宽度或可变宽度存储,具体取决于列的数据类型(可能被ROW_FORMAT
指定符覆盖) 。
从 MySQL NDB Cluster 7.5.4 开始,对于NDB个 table,COLUMN_FORMAT
的默认值为FIXED
。 (在 MySQL NDB Cluster 7.5.1 中,默认设置已切换为DYNAMIC
,但此更改已恢复,以保持与现有 GA 版本系列的向后兼容性.)(错误#24487363)
在 NDB 群集中,用COLUMN_FORMAT=FIXED
定义的列的最大可能偏移为 8188 字节。有关更多信息和可能的解决方法,请参阅第 21.1.7.5 节“与 NDB 群集中的数据库对象相关联的限制”。
COLUMN_FORMAT
当前对使用NDB以外的存储引擎的 table 的列无效。在 MySQL 5.7 和更高版本中,COLUMN_FORMAT
被静默忽略。
STORAGE
对于NDBtable,可以使用STORAGE
子句指定该列是存储在磁盘上还是存储在内存中。 STORAGE DISK
导致将列存储在磁盘上,而STORAGE MEMORY
导致使用内存中存储。使用的CREATE TABLE语句必须仍然包含TABLESPACE
子句:
mysql> CREATE TABLE t1 (
-> c1 INT STORAGE DISK,
-> c2 INT STORAGE MEMORY
-> ) ENGINE NDB;
ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)
mysql> CREATE TABLE t1 (
-> c1 INT STORAGE DISK,
-> c2 INT STORAGE MEMORY
-> ) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.06 sec)
对于NDB个 table,STORAGE DEFAULT
等效于STORAGE MEMORY
。
STORAGE
子句对使用除NDB以外的存储引擎的 table 不起作用。 NDB Cluster 随附的mysqld版本仅支持STORAGE
关键字;在任何其他版本的 MySQL 中都无法识别它,其中使用STORAGE
关键字的任何尝试都会导致语法错误。
GENERATED ALWAYS
用于指定生成的列 table 达式。有关generated columns的信息,请参见第 13.1.18.7 节“创建 table 和生成的列”。
存储的生成列可以被索引。 InnoDB
支持虚拟生成的列上的二级索引。参见第 13.1.18.8 节“辅助索引和生成的列”。
索引和外键
几个关键字适用于创建索引和外键。有关以下描述的一般背景,请参见第 13.1.14 节“ CREATE INDEX 语句”和第 13.1.18.5 节“外键约束”。
CONSTRAINT symbol
可以提供CONSTRAINT symbol
子句以命名约束。如果未提供该子句,或者在CONSTRAINT
关键字后不包含* symbol
*,则 MySQL 将自动生成约束名称,但以下情况除外。 * symbol
值(如果使用)对于每种模式(数据库)和每种约束类型必须是唯一的。重复 symbol
*导致错误。另请参阅有关在第 9.2.1 节“标识符长度限制”处生成的约束标识符的长度限制的讨论。
Note
如果在外键定义中未提供CONSTRAINT symbol
子句,或者在CONSTRAINT
关键字后未包含* symbol
*,则NDB使用外键索引名。
SQL 标准指定所有类型的约束(主键,唯一索引,外键,检查)都属于同一名称空间。在 MySQL 中,每个约束类型每个模式都有其自己的名称空间。因此,每种约束类型的名称在每个架构中必须唯一。
PRIMARY KEY
唯一索引,其中所有键列必须定义为NOT NULL
。如果未将它们显式声明为NOT NULL
,则 MySQL 会如此隐式(无声地)声明它们。一个 table 只能有一个PRIMARY KEY
。 PRIMARY KEY
的名称始终为PRIMARY
,因此不能用作任何其他种类的索引的名称。
如果您没有PRIMARY KEY
并且应用程序在 table 中要求PRIMARY KEY
,则 MySQL 返回的第一个UNIQUE
索引将没有NULL
列作为PRIMARY KEY
。
在InnoDB
table 中,请将PRIMARY KEY
保持较短,以最大程度地减少辅助索引的存储开销。每个辅助索引条目都包含对应行的主键列的副本。 (请参阅第 14.6.2.1 节“群集索引和二级索引”。)
在创建的 table 中,首先放置PRIMARY KEY
,然后放置所有UNIQUE
索引,然后放置非唯一索引。这有助于 MySQL 优化器确定使用哪个索引的优先级,还可以更快地检测到重复的UNIQUE
键。
PRIMARY KEY
可以是多列索引。但是,您不能使用列规范中的PRIMARY KEY
键属性创建多列索引。这样做只会将该单列标记为主要列。您必须使用单独的PRIMARY KEY(key_part, ...)
子句。
如果 table 的PRIMARY KEY
或UNIQUE NOT NULL
索引由具有整数类型的单个列组成,则可以使用_rowid
来引用SELECT语句中的索引列,如Unique Indexes中所述。
在 MySQL 中,PRIMARY KEY
的名称为PRIMARY
。对于其他索引,如果未分配名称,则为索引分配与第一个索引列相同的名称,并带有可选的后缀(_2
,_3
,...
)以使其唯一。您可以使用SHOW INDEX FROM tbl_name
查看 table 的索引名称。参见第 13.7.5.22 节,“ SHOW INDEX 语句”。
KEY | INDEX
KEY
通常是INDEX
的同义词。在列定义中给出键属性PRIMARY KEY
时,也可以仅将其指定为KEY
。这样做是为了与其他数据库系统兼容。
UNIQUE
UNIQUE
索引会创建约束,以使索引中的所有值都必须不同。如果您尝试添加键值与现有行匹配的新行,则会发生错误。对于所有引擎,UNIQUE
索引允许可以包含NULL
的列使用多个NULL
值。如果为UNIQUE
索引中的列指定前缀值,则列值在前缀长度内必须唯一。
如果 table 的PRIMARY KEY
或UNIQUE NOT NULL
索引由具有整数类型的单个列组成,则可以使用_rowid
来引用SELECT语句中的索引列,如Unique Indexes中所述。
FULLTEXT
FULLTEXT
索引是用于全文搜索的特殊索引类型。仅InnoDB和MyISAM存储引擎支持FULLTEXT
索引。只能从CHAR,VARCHAR和TEXT列创建它们。索引总是在整个列上进行;不支持列前缀索引,并且如果指定则忽略任何前缀长度。有关操作的详细信息,请参见第 12.9 节“全文搜索功能”。如果全文索引和搜索操作需要特殊处理,则可以将WITH PARSER
子句指定为* index_option
*值,以将解析器插件与索引关联。此子句仅对FULLTEXT
索引有效。 InnoDB和MyISAM都支持全文分析器插件。有关更多信息,请参见全文解析器插件和第 28.2.4.4 节“编写全文分析器插件”。
SPATIAL
您可以在空间数据类型上创建SPATIAL
索引。仅MyISAM
和InnoDB
table 支持空间类型,并且索引列必须声明为NOT NULL
。参见第 11.4 节“空间数据类型”。
FOREIGN KEY
MySQL 支持外键和外键约束,这些外键使您可以跨 table 交叉引用相关数据,外键约束则有助于使这些扩展数据保持一致。有关定义和选项的信息,请参见reference_definition和reference_option。
使用InnoDB存储引擎的分区 table 不支持外键。有关更多信息,请参见第 22.6 节“分区的限制和限制”。
CHECK
CHECK
子句已解析,但被所有存储引擎忽略。
-
key_part
-
key_part
*规范可以以ASC
或DESC
结尾。这些关键字允许将来用于指定升序或降序索引值存储的扩展。目前,它们已被解析但被忽略;索引值始终按升序存储。
- 由*
length
*属性定义的前缀对于InnoDB
table 最长为 767 个字节,如果启用了innodb_large_prefix选项,则最长为 3072 字节。对于MyISAM
table,前缀长度限制为 1000 个字节。
前缀限制以字节为单位。但是,对于CREATE TABLE,ALTER TABLE和CREATE INDEX语句中的索引规范,前缀* length *被解释为非二进制字符串类型(CHAR,VARCHAR,TEXT)的字符数和二进制字符串类型(BINARY,VARBINARY,BLOB)的字节数。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。
index_type
一些存储引擎允许您在创建索引时指定索引类型。 * index_type
*指定符的语法为USING type_name
。
Example:
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
USING
的首选位置在索引列列 table 之后。可以在列列 table 之前给出它,但是不赞成在该位置使用该选项,并且在以后的 MySQL 版本中将删除该支持。
index_option
index_option
*值指定索引的其他选项。
KEY_BLOCK_SIZE
对于MyISAM个 table,KEY_BLOCK_SIZE
(可选)以字节为单位指定用于索引键块的大小。该值被视为提示;如有必要,可以使用其他大小。为单个索引定义指定的KEY_BLOCK_SIZE
值将覆盖 table 级KEY_BLOCK_SIZE
的值。
有关 table 级KEY_BLOCK_SIZE
属性的信息,请参见Table Options。
WITH PARSER
WITH PARSER
选项只能与FULLTEXT
索引一起使用。如果全文索引和搜索操作需要特殊处理,它将解析器插件与索引关联。 InnoDB和MyISAM都支持全文分析器插件。如果您具有MyISAMtable 以及关联的全文分析器插件,则可以使用ALTER TABLE
将 table 转换为InnoDB
。
COMMENT
在 MySQL 5.7 中,索引定义可以包含最多 1024 个字符的可选 Comments。
您可以使用index_option
COMMENT
子句为单个索引设置InnoDB
MERGE_THRESHOLD
的值。参见第 14.8.12 节“为索引页面配置合并阈值”。
有关允许的index_option
值的更多信息,请参见第 13.1.14 节“ CREATE INDEX 语句”。有关索引的更多信息,请参见第 8.3.1 节“ MySQL 如何使用索引”。
有关* reference_definition
*语法的详细信息和示例,请参见第 13.1.18.5 节“外键约束”。
InnoDB和NDBtable 支持检查外键约束。被引用 table 的列必须始终明确命名。支持外键上的ON DELETE
和ON UPDATE
操作。有关更多详细信息和示例,请参见第 13.1.18.5 节“外键约束”。
对于其他存储引擎,MySQL Server 会解析并忽略CREATE TABLE语句中的FOREIGN KEY
和REFERENCES
语法。参见第 1.8.2.3 节“外键约束差异”。
Important
对于熟悉 ANSI/ISO SQL 标准的用户,请注意,没有存储引擎(包括InnoDB
)可以识别或强制执行引用完整性约束定义中使用的MATCH
子句。使用显式的MATCH
子句将不会具有指定的效果,并且还会导致ON DELETE
和ON UPDATE
子句被忽略。由于这些原因,应避免指定MATCH
。
SQL 标准中的MATCH
子句控制与主键进行比较时如何处理复合(多列)外键中的NULL
值。 InnoDB
本质上实现了MATCH SIMPLE
定义的语义,该语义允许外键全部或部分为NULL
。在这种情况下,允许插入包含此类外键的(子 table)行,并且该行与引用的(父)table 中的任何行都不匹配。使用触发器可以实现其他语义。
另外,MySQL 要求对引用的列进行索引以提高性能。但是,InnoDB
并不强制要求将引用的列声明为UNIQUE
或NOT NULL
。对于诸如UPDATE
或DELETE CASCADE
之类的操作,未很好地定义对非唯一键或包含NULL
值的键的外键引用的处理。建议您使用仅引用UNIQUE
(或PRIMARY
)和NOT NULL
的外键。
MySQL 解析但忽略“内联REFERENCES
规范”(如 SQL 标准所定义),其中引用被定义为列规范的一部分。 MySQL 仅在被指定为单独的FOREIGN KEY
规范的一部分时才接受REFERENCES
子句。
有关RESTRICT
,CASCADE
,SET NULL
,NO ACTION
和SET DEFAULT
选项的信息,请参见第 13.1.18.5 节“外键约束”。
Table Options
table 选项用于优化 table 的行为。在大多数情况下,您无需指定任何一个。除非另有说明,否则这些选项适用于所有存储引擎。不适用于给定存储引擎的选项可以被接受并记住作为 table 定义的一部分。如果您以后使用ALTER TABLE将 table 转换为使用其他存储引擎,则将应用这些选项。
ENGINE
使用下 table 中显示的名称之一指定 table 的存储引擎。引擎名称可以不加引号或加引号。带引号的名称'DEFAULT'
被识别但被忽略。
Storage Engine | Description |
---|---|
InnoDB | 具有行锁定和外键的事务安全 table。新 table 的默认存储引擎。如果您有 MySQL 经验,但不熟悉InnoDB ,请参见第 14 章,InnoDB 存储引擎,尤其是第 14.1 节“ InnoDB 简介”。 |
MyISAM | 二进制便携式存储引擎,主要用于只读或以只读为主的工作负载。参见第 15.2 节“ MyISAM 存储引擎”。 |
MEMORY | 该存储引擎的数据仅存储在内存中。参见第 15.3 节“ MEMORY 存储引擎”。 |
CSV | 以逗号分隔值格式存储行的 table。参见第 15.4 节“ CSV 存储引擎”。 |
ARCHIVE | 归档存储引擎。参见第 15.5 节“ ARCHIVE 存储引擎”。 |
EXAMPLE | 示例引擎。参见第 15.9 节“示例存储引擎”。 |
FEDERATED | 访问远程 table 的存储引擎。参见第 15.8 节“联邦存储引擎”。 |
HEAP | 这是MEMORY 的同义词。 |
MERGE | MyISAM 个 table 的集合用作一个 table。也称为MRG_MyISAM 。参见第 15.7 节“ MERGE 存储引擎”。 |
NDB | 群集的,基于内存的容错 table,支持事务和外键。也称为NDBCLUSTER。参见第 21 章,MySQL NDB 群集 7.5 和 NDB 群集 7.6。 |
默认情况下,如果指定了不可用的存储引擎,则该语句将失败并显示错误。您可以通过从服务器 SQL 模式中删除NO_ENGINE_SUBSTITUTION(请参见第 5.1.10 节“服务器 SQL 模式”)来覆盖此行为,以便 MySQL 允许将指定的引擎替换为默认的存储引擎。通常,在这种情况下,它是InnoDB
,这是default_storage_engine系统变量的默认值。禁用NO_ENGINE_SUBSTITUTION
时,如果不遵守存储引擎规范,则会发生警告。
AUTO_INCREMENT
table 格的初始AUTO_INCREMENT
值。在 MySQL 5.7 中,这适用于MyISAM
,MEMORY
,InnoDB
和ARCHIVE
table。要为不支持AUTO_INCREMENT
table 选项的引擎设置第一个自动增量值,请在创建 table 后插入“虚拟”行,其值比所需值小一,然后删除虚拟行。
对于在CREATE TABLE语句中支持AUTO_INCREMENT
table 选项的引擎,您还可以使用ALTER TABLE tbl_name AUTO_INCREMENT = N
重置AUTO_INCREMENT
值。该值不能设置为低于该列中当前的最大值。
AVG_ROW_LENGTH
table 的平均行长的近似值。您仅需要为具有可变大小行的大型 table 设置此选项。
创建MyISAM
table 时,MySQL 使用MAX_ROWS
和AVG_ROW_LENGTH
选项的乘积来确定结果 table 的大小。如果您未指定任何选项,则MyISAM
数据和索引文件的最大大小默认为 256TB。 (如果 os 不支持那么大的文件,则 table 的大小受文件大小限制的约束.)如果要减小指针大小以使索引变小和变快,而实际上并不需要大文件,则可以可以通过设置myisam_data_pointer_size系统变量来减小默认指针大小。 (请参阅第 5.1.7 节“服务器系统变量”。)如果希望所有 table 都可以超过默认限制,并且希望 table 的速度稍慢一些且比必要的要大,则可以通过设置此变量来增加默认指针的大小。将该值设置为 7 将允许 table 最大为 65,536TB。
[DEFAULT] CHARACTER SET
指定 table 的默认字符集。 CHARSET
是CHARACTER SET
的同义词。如果字符集名称为DEFAULT
,则使用数据库字符集。
CHECKSUM
如果您希望 MySQL 维护所有行的实时校验和(即,当 table 更改时 MySQL 自动更新的校验和),请将其设置为 1.这使 table 的更新速度稍慢,但也更容易找到损坏的 table。 CHECKSUM TABLE语句报告校验和。 (仅MyISAM
.)
[DEFAULT] COLLATE
指定 table 的默认排序规则。
COMMENT
table 格的 Comments,最多 2048 个字符。
您可以使用table_option
COMMENT
子句为 table 设置InnoDB
MERGE_THRESHOLD
的值。参见第 14.8.12 节“为索引页面配置合并阈值”。
设置 NDB_TABLE 选项. 在 MySQL NDB Cluster 7.5.2 和更高版本中,CREATE TABLE
或ALTER TABLE语句中的 tableComments 还可用于指定NDB_TABLE
选项NOLOGGING
,READ_BACKUP
,PARTITION_BALANCE
或FULLY_REPLICATED
中的一到四个。作为一组名称/值对,如果需要,用逗号分隔,紧跟在引号 Comments 文本开头的字符串NDB_TABLE=
之后。此处显示了使用此语法的示例语句(强调文本):
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 VARCHAR(100),
c3 VARCHAR(100) )
ENGINE=NDB
COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";
带引号的字符串中不允许有空格。字符串不区分大小写。
Comments 将显示为显示创建 table的输出的一部分。Comments 的文本也可以作为 MySQL Information Schema TABLEStable 的 TABLE_COMMENT 列使用。
NDB
个 table 的ALTER TABLE语句也支持此 Comments 语法。请记住,与ALTER TABLE
一起使用的 tableComments 将替换该 table 以前可能具有的所有现有 Comments。
NDBtable 不支持在 tableComments 中设置MERGE_THRESHOLD
选项(将被忽略)。
有关完整的语法信息和示例,请参见第 13.1.18.9 节“设置 NDB_TABLE 选项”。
COMPRESSION
用于InnoDB
个 table 的页面级压缩的压缩算法。支持的值包括Zlib
,LZ4
和None
。透明页面压缩功能引入了COMPRESSION
属性。仅位于file-per-tabletable 空间中的InnoDB
table 支持页面压缩,并且仅在支持稀疏文件和打孔的 Linux 和 Windows 平台上可用。有关更多信息,请参见第 14.9.2 节“ InnoDB 页面压缩”。
CONNECTION
FEDERATED
table 的连接字符串。
Note
较旧的 MySQL 版本使用COMMENT
选项作为连接字符串。
DATA DIRECTORY
,INDEX DIRECTORY
对于InnoDB
,DATA DIRECTORY='directory'
子句允许在数据目录之外创建 table。必须启用innodb_file_per_table变量才能使用DATA DIRECTORY
子句。必须指定完整目录路径。有关更多信息,请参见第 14.6.1.2 节“在外部创建 table”。
创建MyISAM
table 时,可以使用DATA DIRECTORY='directory'
子句,INDEX DIRECTORY='directory'
子句或同时使用这两个子句。它们指定分别放置MyISAM
table 的数据文件和索引文件的位置。与InnoDB
table 不同,在创建带有DATA DIRECTORY
或INDEX DIRECTORY
选项的MyISAM
table 时,MySQL 不会创建与数据库名称相对应的子目录。在指定的目录中创建文件。
从 MySQL 5.7.17 开始,您必须具有FILE特权才能使用DATA DIRECTORY
或INDEX DIRECTORY
table 选项。
Important
对于分区 table,将忽略 table 级别的DATA DIRECTORY
和INDEX DIRECTORY
选项。错误 32091)
这些选项仅在不使用--skip-symbolic-links选项时有效。您的 os 还必须有一个有效的线程安全realpath()
调用。有关更多完整信息,请参见第 8.12.3.2 节,“在 Unix 上为 MyISAMtable 使用符号链接”。
如果创建的MyISAM
table 没有DATA DIRECTORY
选项,则将在数据库目录中创建.MYD
文件。默认情况下,如果MyISAM
在这种情况下找到了现有的.MYD
文件,它将覆盖该文件。对于没有INDEX DIRECTORY
选项创建的 table,.MYI
文件也是如此。若要抑制此行为,请使用--keep_files_on_create选项启动服务器,在这种情况下MyISAM
不会覆盖现有文件,而是返回错误。
如果使用DATA DIRECTORY
或INDEX DIRECTORY
选项创建了MyISAM
table,并且找到了现有的.MYD
或.MYI
文件,则 MyISAM 总是返回错误。它不会覆盖指定目录中的文件。
Important
您不能使用包含DATA DIRECTORY
或INDEX DIRECTORY
的 MySQL 数据目录的路径名。这包括分区 table 和单个 table 分区。 (请参阅 Bug#32167.)
DELAY_KEY_WRITE
如果要在关闭 table 之前延迟 table 的键更新,请将其设置为 1.请参阅第 5.1.7 节“服务器系统变量”中对delay_key_write系统变量的描述。 (仅MyISAM
.)
ENCRYPTION
将ENCRYPTION
选项设置为'Y'
以对在file-per-tabletable 空间中创建的InnoDB
table 启用页面级数据加密。选项值不区分大小写。 InnoDB
table 空间加密功能引入了ENCRYPTION
选项;参见第 14.14 节“ InnoDB 静态数据加密”。必须先安装和配置keyring
插件,然后才能启用加密。
INSERT_METHOD
如果要将数据插入MERGE
table,则必须用INSERT_METHOD
指定应将行插入到的 table。 INSERT_METHOD
是仅对MERGE
table 有用的选项。使用FIRST
或LAST
的值可将插入内容移到第一个或最后一个 table,或使用NO
的值可防止插入。参见第 15.7 节“ MERGE 存储引擎”。
KEY_BLOCK_SIZE
对于MyISAM个 table,KEY_BLOCK_SIZE
(可选)以字节为单位指定用于索引键块的大小。该值被视为提示;如有必要,可以使用其他大小。为单个索引定义指定的KEY_BLOCK_SIZE
值将覆盖 table 级KEY_BLOCK_SIZE
的值。
对于InnoDB个 table,KEY_BLOCK_SIZE
指定要用于compressed InnoDB
个 table 的page大小(以千字节为单位)。 KEY_BLOCK_SIZE
值被视为提示;如有必要,InnoDB
可以使用其他大小。 KEY_BLOCK_SIZE
只能小于或等于innodb_page_size值。值 0table 示默认的压缩页面大小,是innodb_page_size值的一半。取决于innodb_page_size,可能的KEY_BLOCK_SIZE
值包括 0、1、2、4、8 和 16.有关更多信息,请参见第 14.9.1 节“ InnoDBtable 压缩”。
Oracle 建议在为InnoDB
table 指定KEY_BLOCK_SIZE
时启用innodb_strict_mode。启用innodb_strict_mode时,指定无效的KEY_BLOCK_SIZE
值将返回错误。如果禁用innodb_strict_mode,则无效的KEY_BLOCK_SIZE
值将导致警告,并且KEY_BLOCK_SIZE
选项将被忽略。
响应显示 table 格状态的Create_options
列报告了最初指定的KEY_BLOCK_SIZE
选项,而显示创建 table也是如此。
InnoDB
仅在 table 级别支持KEY_BLOCK_SIZE
。
KEY_BLOCK_SIZE
不支持 32KB 和 64KB innodb_page_size值。 InnoDB
table 压缩不支持这些页面大小。
MAX_ROWS
您计划在 table 中存储的最大行数。这不是硬性限制,而是向存储引擎提示 table 必须至少能够存储这么多行。
Important
从 NDB Cluster 7.5.4 开始,不建议使用MAX_ROWS
和NDB
table 来控制 table 分区数。为了向后兼容,它在更高版本中仍受支持,但将来的版本中可能会删除它。改用 PARTITION_BALANCE;参见设置 NDB_TABLE 选项。
NDB存储引擎将此值视为最大值。如果计划创建非常大的 NDB 群集 table(包含数百万行),则应使用此选项来确保NDB通过设置MAX_ROWS = 2 * rows
在散列 table 中分配足够数量的索引槽用于存储 table 主键的哈希,其中* rows
*是您希望插入 table 中的行数。
MAX_ROWS
最大值为 4294967295;较大的值将被截断到此限制。
MIN_ROWS
您计划在 table 中存储的最小行数。 MEMORY存储引擎使用此选项作为有关内存使用的提示。
PACK_KEYS
仅对MyISAM
个 table 生效。如果要使用较小的索引,请将此选项设置为 1.这通常会使更新速度变慢,读取速度也会加快。将选项设置为 0 将禁用所有键打包。将其设置为DEFAULT
会告诉存储引擎仅打包长CHAR,VARCHAR,BINARY或VARBINARY列。
如果不使用PACK_KEYS
,则默认值为打包字符串,但不打包数字。如果您使用PACK_KEYS=1
,数字也会被打包。
在打包二进制数字键时,MySQL 使用前缀压缩:
-
每个密钥都需要一个额外的字节,以指示上一个密钥的多少个字节与下一个密钥相同。
- 指向行的指针直接在键之后以高字节优先 Sequences 存储,以提高压缩率。
这意味着,如果在连续的两行上有许多相等的键,则所有后面的“相同”键通常仅占用两个字节(包括指向该行的指针)。将其与以下键取storage_size_for_key + pointer_size
(指针大小通常为 4)的普通情况进行比较。相反,仅当您具有许多相同的数字时,您才能从前缀压缩中获得显着的好处。如果所有键完全不同,则如果该键不是可以具有NULL
值的键,则每个键要多使用一个字节。 (在这种情况下,打包的密钥长度存储在用于标记密钥是否为NULL
的同一字节中.)
PASSWORD
此选项未使用。如果您需要加密.frm
文件并使它们无法用于任何其他 MySQL 服务器,请与我们的销售部门联系。
ROW_FORMAT
定义存储行的物理格式。
创建禁用了strict mode的 table 时,如果不支持指定的行格式,则使用存储引擎的默认行格式。响应显示 table 格状态,table 的实际行格式在Row_format
列中报告。 Create_options
列显示CREATE TABLE语句中指定的行格式,显示创建 table也是如此。
行格式选择取决于 table 所使用的存储引擎。
对于InnoDB
个 table:
- 默认行格式由innodb_default_row_format定义,其默认设置为
DYNAMIC
。当未定义ROW_FORMAT
选项或使用ROW_FORMAT=DEFAULT
时,将使用默认行格式。
如果未定义ROW_FORMAT
选项,或者如果使用ROW_FORMAT=DEFAULT
,则重建 table 的操作还将无提示地将 table 的行格式更改为innodb_default_row_format定义的默认格式。有关更多信息,请参见定义 table 格的行格式。
-
为了更有效地
InnoDB
存储数据类型,尤其是BLOB类型,请使用DYNAMIC
。有关DYNAMIC
行格式的要求,请参见动态行格式。 -
要为
InnoDB
个 table 启用压缩,请指定ROW_FORMAT=COMPRESSED
。有关与COMPRESSED
行格式相关的要求,请参见第 14.9 节“ InnoDBtable 和页面压缩”。 -
仍可以通过指定
REDUNDANT
行格式来请求在旧版 MySQL 中使用的行格式。 -
当您指定非默认的
ROW_FORMAT
子句时,请考虑同时启用innodb_strict_mode配置选项。 -
不支持
ROW_FORMAT=FIXED
。如果在禁用innodb_strict_mode的同时指定ROW_FORMAT=FIXED
,则InnoDB
发出警告并假定ROW_FORMAT=DYNAMIC
。如果在启用innodb_strict_mode时指定了ROW_FORMAT=FIXED
,这是默认值,则InnoDB
返回错误。 -
有关
InnoDB
行格式的其他信息,请参见第 14.11 节“ InnoDB 行格式”。
对于MyISAM
table,对于静态或可变长度的行格式,选项值可以为FIXED
或DYNAMIC
。 myisampack将类型设置为COMPRESSED
。参见第 15.2.3 节“ MyISAMtable 存储格式”。
对于NDBtable,MySQL NDB Cluster 7.5.1 和更高版本中的默认ROW_FORMAT
是DYNAMIC
。 (以前是FIXED
.)
STATS_AUTO_RECALC
指定是否自动为InnoDB
table 重新计算persistent statistics。值DEFAULT
使 table 的持久统计信息设置由innodb_stats_auto_recalc配置选项确定。值1
导致 table 中 10%的数据已更改时重新计算统计信息。值0
可防止对该 table 进行自动重新计算;使用此设置,对 table 进行实质性更改后,发出ANALYZE TABLE语句以重新计算统计信息。有关持久统计信息功能的更多信息,请参见第 14.8.11.1 节“配置持久性优化器统计参数”。
STATS_PERSISTENT
指定是否为InnoDB
table 启用persistent statistics。值DEFAULT
使 table 的持久统计信息设置由innodb_stats_persistent配置选项确定。值1
启用 table 的持久统计信息,而值0
则关闭此功能。通过CREATE TABLE
或ALTER TABLE
语句启用持久统计信息后,在将代 table 性数据加载到 table 中之后,发出ANALYZE TABLE语句以计算统计信息。有关持久统计信息功能的更多信息,请参见第 14.8.11.1 节“配置持久性优化器统计参数”。
STATS_SAMPLE_PAGES
估计索引列的基数和其他统计信息(例如ANALYZE TABLE计算的那些索引)时要采样的索引页数。有关更多信息,请参见第 14.8.11.1 节“配置持久性优化器统计参数”。
TABLESPACE
TABLESPACE
子句可用于在现有的常规 table 空间,每 table 文件 table 空间或系统 table 空间中创建 table。
CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name
您指定的常规 table 空间在使用TABLESPACE
子句之前必须存在。有关常规 table 空间的信息,请参见第 14.6.3.3 节“常规 table 空间”。
tablespace_name
是区分大小写的标识符。它可以被引用或不被引用。不允许使用正斜杠字符(“ /”)。以“ innodb_”开头的名称保留作特殊用途。
要在系统 table 空间中创建 table,请指定innodb_system
作为 table 空间名称。
CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system
使用TABLESPACE [=] innodb_system
,无论innodb_file_per_table设置如何,都可以将任何未压缩行格式的 table 放置在系统 table 空间中。例如,您可以使用TABLESPACE [=] innodb_system
将具有ROW_FORMAT=DYNAMIC
的 table 添加到系统 table 空间。
要在每个 table 文件 table 空间中创建一个 table,请指定innodb_file_per_table
作为 table 空间名称。
CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table
Note
如果启用了innodb_file_per_table,则无需指定TABLESPACE=innodb_file_per_table
即可创建InnoDB
每 table 文件 table 空间。启用innodb_file_per_table时,默认情况下在每 table 文件 table 空间中创建InnoDB
个 table。
Note
MySQL 5.7.24 不支持在共享的InnoDB
table 空间中创建 table 分区,并且在将来的 MySQL 版本中将不再支持该功能。共享 table 空间包括InnoDB
系统 table 空间和常规 table 空间。
DATA DIRECTORY
子句与CREATE TABLE ... TABLESPACE=innodb_file_per_table
一起允许,但不支持与TABLESPACE
选项结合使用。
Note
从 MySQL 5.7.24 开始,不支持对带有创建临时 table的TABLESPACE = innodb_file_per_table
和TABLESPACE = innodb_temporary
子句的支持,并将在将来的 MySQL 版本中删除。
STORAGE
table 选项仅与NDBtable 一起使用。 STORAGE
确定使用的存储类型(磁盘或内存),可以是DISK
或MEMORY
。
TABLESPACE ... STORAGE DISK
将 table 分配给 NDB 群集磁盘数据 table 空间。该 table 空间必须已经使用CREATE TABLESPACE创建。有关更多信息,请参见第 21.5.10 节“ NDB 群集磁盘数据 table”。
Important
没有TABLESPACE
子句,不能在CREATE TABLE语句中使用STORAGE
子句。
用于访问一个相同的MyISAM
table 的集合。仅适用于MERGE
个 table。参见第 15.7 节“ MERGE 存储引擎”。
您 Map 到MERGE
table 的 table 必须具有SELECT,UPDATE和DELETE特权。
Note
以前,所有使用的 table 必须与MERGE
table 本身位于同一数据库中。此限制不再适用。
Table Partitioning
partition_options
*可用于控制用CREATE TABLE创建的 table 的分区。
并非本节开头* partition_options
*语法中显示的所有选项都不适用于所有分区类型。请参阅以下各个类型的 Lists 以获取每种类型的特定信息,并参阅第 22 章,分区以获得有关 MySQL 分区工作方式和用途的更完整信息,以及 table 创建的其他示例以及与 MySQL 分区有关的其他语句。
分区可以被修改,合并,添加到 table 中以及从 table 中删除。有关完成这些任务的 MySQL 语句的基本信息,请参见第 13.1.8 节“ ALTER TABLE 语句”。有关更多详细的描述和示例,请参见第 22.3 节“分区 Management”。
PARTITION BY
如果使用,则* partition_options
子句以PARTITION BY
开头。此子句包含用于确定分区的函数;该函数返回一个整数值,范围是 1 到 num
,其中 num
*是分区数。 (一个 table 可能包含的用户定义的分区的最大数量为 1024;该分区的最大数量包括了子分区的数量(本节稍后讨论)。)
Note
PARTITION BY
子句中使用的 table 达式(* expr
*)不能引用不在正在创建的 table 中的任何列;明确不允许使用此类引用,这些引用会导致语句失败并出现错误。 (缺陷#29444)
HASH(expr)
散列一个或多个列以创建用于放置和定位行的键。 * expr
*是使用一个或多个 table 列的 table 达式。这可以是产生单个整数值的任何有效 MySQLtable 达式(包括 MySQL 函数)。例如,这些都是使用PARTITION BY HASH
的有效CREATE TABLE语句:
CREATE TABLE t1 (col1 INT, col2 CHAR(5))
PARTITION BY HASH(col1);
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
PARTITION BY HASH ( YEAR(col3) );
您不得将VALUES LESS THAN
或VALUES IN
子句与PARTITION BY HASH
一起使用。
PARTITION BY HASH
使用* expr
*的余数除以分区数(即模数)。有关示例和其他信息,请参见第 22.2.4 节“ HASH 分区”。
LINEAR
关键字需要一些不同的算法。在这种情况下,作为一个或多个逻辑AND运算的结果,计算存储行的分区的数量。有关线性哈希的讨论和示例,请参见第 22.2.4.1 节“线性哈希分区”。
KEY(column_list)
这类似于HASH
,除了 MySQL 提供散列功能以保证均匀的数据分布。 * column_list
*参数只是一个 1 或多个 table 列的列 table(最多 16 个)。此示例显示了一个简单的 table,该 table 按键进行分区,并具有 4 个分区:
CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY KEY(col3)
PARTITIONS 4;
对于按键分区的 table,可以通过使用LINEAR
关键字来采用线性分区。这与由HASH
分区的 table 具有相同的效果。也就是说,使用&运算符而不是模数来找到分区号(有关详细信息,请参见第 22.2.4.1 节“线性哈希分区”和第 22.2.5 节“密钥分区”)。本示例按键使用线性分区在 5 个分区之间分配数据:
CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR KEY(col3)
PARTITIONS 5;
[SUB]PARTITION BY [LINEAR] KEY
支持ALGORITHM={1 | 2}
选项。 ALGORITHM=1
使服务器使用与 MySQL 5.1 相同的键哈希函数; ALGORITHM=2
table 示服务器采用默认在 MySQL 5.5 及更高版本中为新的KEY
分区 table 实现和使用的键哈希函数。 (使用 MySQL 5.5 和更高版本中使用的键哈希函数创建的分区 table 不能由 MySQL 5.1 服务器使用.)不指定该选项与使用ALGORITHM=2
具有相同的效果。此选项主要用于在 MySQL 5.1 和更高版本的 MySQL 之间升级或降级[LINEAR] KEY
分区 table 时,或在 MySQL 5.5 或更高版本的服务器上创建可以由KEY
或LINEAR KEY
分区的 table 时,该 table 可以在 MySQL 5.1 服务器上使用。有关更多信息,请参见第 13.1.8.1 节,“ ALTER TABLE 分区操作”。
MySQL 5.7(及更高版本)中的mysqldump将此选项写在版本 Comments 中,如下所示:
CREATE TABLE t1 (a INT)
/*!50100 PARTITION BY KEY */ /*!50611 ALGORITHM = 1 */ /*!50100 ()
PARTITIONS 3 */
这将导致 MySQL 5.6.10 和更早版本的服务器忽略该选项,否则将导致这些版本中的语法错误。如果您打算将使用KEY
进行分区或子分区的 table 加载到 MySQL 5.7 服务器上进行的转储,请将该数据库转储到 5.6.11 之前的 MySQL 5.6 服务器中,请确保先查阅MySQL 5.6 中的更改,然后再 continue。 (如果将包含由 MySQL 5.7(实际上是 5.6.11 或更高版本)服务器制作的KEY
分区 table 或子分区 table 的转储加载到 MySQL 5.5.30 或更早版本的服务器中,则在此找到的信息也适用。)
同样在 MySQL 5.6.11 和更高版本中,在必要时在显示创建 table的输出中以与mysqldump相同的方式使用版本化 Comments 显示ALGORITHM=1
。 ALGORITHM=2
始终从SHOW CREATE TABLE
输出中省略,即使在创建原始 table 时已指定此选项。
您不得将VALUES LESS THAN
或VALUES IN
子句与PARTITION BY KEY
一起使用。
RANGE(expr)
在这种情况下,* expr
*使用一组VALUES LESS THAN
运算符显示值的范围。使用范围分区时,必须使用VALUES LESS THAN
定义至少一个分区。您不能将VALUES IN
与范围分区一起使用。
Note
对于由RANGE
分区的 table,必须将VALUES LESS THAN
与整数 Literals 值或计算结果为单个整数值的 table 达式一起使用。在 MySQL 5.7 中,您可以在使用PARTITION BY RANGE COLUMNS
定义的 table 中克服此限制,如本节稍后所述。
假设您有一个 table,希望根据以下方案在包含年份值的列上进行分区。
Partition Number: | Years Range: |
---|---|
0 | 1990 及更早版本 |
1 | 1991 至 1994 |
2 | 1995 至 1998 年 |
3 | 1999 至 2002 |
4 | 2003 至 2005 年 |
5 | 2006 及更高版本 |
可以通过此处显示的CREATE TABLE语句来实现实现这种分区方案的 table:
CREATE TABLE t1 (
year_col INT,
some_data INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999),
PARTITION p3 VALUES LESS THAN (2002),
PARTITION p4 VALUES LESS THAN (2006),
PARTITION p5 VALUES LESS THAN MAXVALUE
);
PARTITION ... VALUES LESS THAN ...
语句以连续的方式工作。 VALUES LESS THAN MAXVALUE
用于指定“剩余”值,该值大于否则指定的最大值。
VALUES LESS THAN
子句以与switch ... case
块的case
部分相似的方式 Sequences 工作(在许多编程语言(如 C,Java 和 PHP 中都可以找到))。也就是说,子句的排列方式必须使每个连续VALUES LESS THAN
中指定的上限大于前一个上限,而引用MAXVALUE
的上限在列 table 中排在最后。
RANGE COLUMNS(column_list)
RANGE
上的此变体有助于使用多个列上的范围条件(即具有诸如WHERE a = 1 AND b < 10
或WHERE a = 1 AND b = 10 AND c < 10
之类的条件)对查询进行分区修剪。它使您可以通过使用COLUMNS
子句中的列列 table 和每个PARTITION ... VALUES LESS THAN (value_list)
分区定义子句中的一组列值来指定多列中的值范围。 (在最简单的情况下,此集合由单个列组成.)* column_list
和 value_list
*中可以引用的最大列数为 16.
COLUMNS
子句中使用的* column_list
*只能包含列名;列 table 中的每一列必须是以下 MySQL 数据类型之一:整数类型;字符串类型;和时间或日期列类型。不允许使用BLOB
,TEXT
,SET
,ENUM
,BIT
或空间数据类型的列;也不允许使用浮点数类型的列。您也不得在COLUMNS
子句中使用函数或算术 table 达式。
分区定义中使用的VALUES LESS THAN
子句必须为出现在COLUMNS()
子句中的每一列指定 Literals 值;也就是说,每个VALUES LESS THAN
子句使用的值列 table 必须包含与COLUMNS
子句中列出的列数量相同的值。尝试在VALUES LESS THAN
子句中使用比COLUMNS
子句中更多或更少的值会导致该语句失败,并显示错误:使用列列 table 进行分区...不一致。对于VALUES LESS THAN
中出现的任何值,您都不能使用NULL
。对于给定的列而不是第一列,可以多次使用MAXVALUE
,如本示例所示:
CREATE TABLE rc (
a INT NOT NULL,
b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (10,5),
PARTITION p1 VALUES LESS THAN (20,10),
PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
VALUES LESS THAN
值列 table 中使用的每个值都必须与相应列的类型完全匹配;没有进行任何转换。例如,不能将字符串'1'
用于与使用整数类型的列匹配的值(必须使用数字1
代替),也不能将数字1
用于与使用字符串类型的列匹配的值(在这种情况下,必须使用带引号的字符串:'1'
)。
有关更多信息,请参见第 22.2.1 节“ RANGE 分区”和第 22.4 节“分区修剪”。
LIST(expr)
当基于具有有限的可能值集(例如 State 或国家/locale 代码)的 table 列分配分区时,此功能很有用。在这种情况下,可以将与某个 State 或国家/locale 有关的所有行分配给单个分区,或者可以为某个 State 或国家/locale 的某个组保留一个分区。它类似于RANGE
,不同之处在于仅VALUES IN
可以用于指定每个分区的允许值。
VALUES IN
与要匹配的值列 table 一起使用。例如,您可以创建如下分区方案:
CREATE TABLE client_firms (
id INT,
name VARCHAR(35)
)
PARTITION BY LIST (id) (
PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);
使用列 table 分区时,必须使用VALUES IN
定义至少一个分区。您不能将VALUES LESS THAN
与PARTITION BY LIST
结合使用。
Note
对于被LIST
分区的 table,与VALUES IN
一起使用的值列 table 必须仅由整数值组成。在 MySQL 5.7 中,您可以使用LIST COLUMNS
进行分区来克服此限制,本节稍后将对此进行介绍。
LIST COLUMNS(column_list)
LIST
上的此变体使用多个列上的比较条件(即具有WHERE a = 5 AND b = 5
或WHERE a = 1 AND b = 10 AND c = 5
之类的条件)来简化查询的分区修剪。它使您可以通过使用COLUMNS
子句中的列列 table 和每个PARTITION ... VALUES IN (value_list)
分区定义子句中的一组列值来指定多列中的值。
关于LIST COLUMNS(column_list)
中使用的列列 table 和VALUES IN(value_list)
中使用的值列 table 的数据类型的规则分别与RANGE COLUMNS(column_list)
和VALUES LESS THAN(value_list)
中使用的列列 table 的数据类型相同,除了VALUES IN
子句MAXVALUE
不允许,您可以使用NULL
。
与PARTITION BY LIST
一起使用时,与PARTITION BY LIST COLUMNS
一起用于VALUES IN
的值列 table 之间有一个重要区别。与PARTITION BY LIST COLUMNS
一起使用时,VALUES IN
子句中的每个元素都必须是一组*列值;每个集合中的值数必须与COLUMNS
子句中使用的列数相同,并且这些值的数据类型必须与列的数据类型匹配(并以相同的 Sequences 出现)。在最简单的情况下,集合由单个列组成。 * column_list
和组成 value_list
*的元素中可以使用的最大列数为 16.
以下CREATE TABLE
语句定义的 table 提供了使用LIST COLUMNS
分区的 table 的示例:
CREATE TABLE lc (
a INT NULL,
b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
);
PARTITIONS num
可以选择使用PARTITIONS num
子句指定分区的数量,其中* num
是分区的数量。如果此子句和*都使用了任何PARTITION
子句,则num
*必须等于使用PARTITION
子句声明的任何分区的总数。
Note
在创建被RANGE
或LIST
分区的 table 时,无论是否使用PARTITIONS
子句,都必须在 table 定义中至少包含一个PARTITION VALUES
子句(请参见下文)。
SUBPARTITION BY
分区可以可选地划分为多个子分区。这可以通过使用可选的SUBPARTITION BY
子句来指示。可以通过HASH
或KEY
进行分区。这些都可以是LINEAR
。这些工作方式与先前针对等效分区类型所述的方式相同。 (无法通过LIST
或RANGE
进行子分区.)
可以使用SUBPARTITIONS
关键字后跟一个整数值来指示子分区的数量。
-
严格检查
PARTITIONS
或SUBPARTITIONS
子句中使用的值,并且该值必须遵守以下规则: -
该值必须是一个非零的正整数。
-
不允许前导零。
-
该值必须是整数 Literals,并且不能为 table 达式。例如,即使
0.2E+01
计算为2
,也不允许PARTITIONS 0.2E+01
。错误 15890)
-
-
partition_definition
每个分区可以使用* partition_definition
*子句单独定义。组成此子句的各个部分如下:
PARTITION partition_name
指定分区的逻辑名。
VALUES
对于范围分区,每个分区必须包含VALUES LESS THAN
子句;对于列 table 分区,必须为每个分区指定一个VALUES IN
子句。这用于确定哪些行要存储在此分区中。有关语法示例,请参见第 22 章,分区中有关分区类型的讨论。
[STORAGE] ENGINE
分区处理程序为PARTITION
和SUBPARTITION
接受[STORAGE] ENGINE
选项。当前,唯一可以使用此方法的方法是将所有分区或所有子分区设置为同一存储引擎,并且尝试为同一 table 中的分区或子分区设置不同的存储引擎将产生错误 ERROR 1469( HY000):此版本的 MySQL 中不允许在分区中混合使用处理程序。我们希望在将来的 MySQL 版本中取消对分区的限制。
COMMENT
可选的COMMENT
子句可用于指定描述分区的字符串。例:
COMMENT = 'Data for the years previous to 1999'
分区 Comments 的最大长度为 1024 个字符。
DATA DIRECTORY
和INDEX DIRECTORY
DATA DIRECTORY
和INDEX DIRECTORY
可用于指示目录,该分区的数据和索引将分别存储在该目录中。 data_dir
和index_dir
都必须是绝对系统路径名。
从 MySQL 5.7.17 开始,您必须具有FILE特权才能使用DATA DIRECTORY
或INDEX DIRECTORY
分区选项。
Example:
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
PARTITION p1999 VALUES IN (1995, 1999, 2003)
DATA DIRECTORY = '/var/appdata/95/data'
INDEX DIRECTORY = '/var/appdata/95/idx',
PARTITION p2000 VALUES IN (1996, 2000, 2004)
DATA DIRECTORY = '/var/appdata/96/data'
INDEX DIRECTORY = '/var/appdata/96/idx',
PARTITION p2001 VALUES IN (1997, 2001, 2005)
DATA DIRECTORY = '/var/appdata/97/data'
INDEX DIRECTORY = '/var/appdata/97/idx',
PARTITION p2002 VALUES IN (1998, 2002, 2006)
DATA DIRECTORY = '/var/appdata/98/data'
INDEX DIRECTORY = '/var/appdata/98/idx'
);
DATA DIRECTORY
和INDEX DIRECTORY
的行为与MyISAM
table 中使用的CREATE TABLE语句的* table_option
*子句相同。
每个分区可以指定一个数据目录和一个索引目录。如果未指定,则默认情况下,数据和索引存储在 table 的数据库目录中。
在 Windows 上,MyISAMtable 的单个分区或子分区不支持DATA DIRECTORY
和INDEX DIRECTORY
选项,而InnoDBtable 的单个分区或子分区不支持INDEX DIRECTORY
选项。在 Windows 上将忽略这些选项,但会生成警告。错误 30459)
Note
如果NO_DIR_IN_CREATE有效,则忽略DATA DIRECTORY
和INDEX DIRECTORY
选项以创建分区 table。错误 24633)
MAX_ROWS
和MIN_ROWS
可以分别用来指定要存储在分区中的最大和最小行数。 * max_number_of_rows
和 min_number_of_rows
*的值必须为正整数。与具有相同名称的 table 级选项一样,这些选项仅充当服务器的“建议”,而不是硬性限制。
TABLESPACE
可用于为分区指定 table 空间。受 NDB 群集支持。对于InnoDB
个 table,可以通过指定 TABLESPACE``来为分区指定每 table 文件 table 空间。所有分区必须属于同一存储引擎。
Note
MySQL 5.7.24 中弃用了将InnoDB
table 分区放置在共享InnoDB
table 空间中的支持,并且在将来的 MySQL 版本中将不再支持。共享 table 空间包括InnoDB
系统 table 空间和常规 table 空间。
subpartition_definition
分区定义可以选择包含一个或多个* subpartition_definition
子句。每一个都至少由SUBPARTITION name
组成,其中 name
*是该子分区的标识符。除了用SUBPARTITION
替换PARTITION
关键字之外,子分区定义的语法与分区定义的语法相同。
子分区必须由HASH
或KEY
完成,并且只能在RANGE
或LIST
分区上进行。参见第 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()定义生成的列。