CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
[check_constraint_definition]
}
data_type:
(see Chapter 13, Data Types)
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| START TRANSACTION
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| tablespace_option
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
tablespace_option:
TABLESPACE tablespace_name [STORAGE DISK]
| [TABLESPACE tablespace_name] STORAGE MEMORY
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE
创建具有给定名称的表。您必须对该表拥有 CREATE
权限。
默认情况下,表在默认数据库中创建,使用 InnoDB
存储引擎。如果表存在、没有默认数据库或数据库不存在,则会发生错误。
MySQL 对表数量没有限制。底层文件系统可能对代表表的数量有限制。各个存储引擎可能会施加引擎特定的约束。 InnoDB
允许最多 40 亿个表。
有关表的物理表示的信息,请参见 第 15.1.20.1 节,“CREATE TABLE 创建的文件”。
创建 CREATE TABLE
语句包含几个方面,在本节的以下主题中进行了描述。
表名
tbl_name
表名可以指定为
db_name.tbl_name
,以在特定数据库中创建表。这在存在默认数据库的情况下有效,前提是数据库存在。如果您使用带引号的标识符,请分别为数据库和表名加引号。例如,编写`mydb`.`mytbl`
,而不是`mydb.mytbl`
。允许的表名的规则在 第 11.2 节“模式对象名称” 中给出。
IF NOT EXISTS
如果表存在,则防止发生错误。但是,不验证现有表的结构是否与
CREATE TABLE
语句指示的结构相同。
临时表
在创建表时,可以使用 TEMPORARY
关键字。一个 TEMPORARY
表仅在当前会话中可见,并在会话关闭时自动删除。有关详细信息,请参阅 第 15.1.20.2 节“CREATE TEMPORARY TABLE 语句”。
表克隆和复制
LIKE
使用
CREATE TABLE ... LIKE
根据另一个表的定义创建空表,包括原始表中定义的任何列属性和索引。CREATE TABLE new_tbl LIKE orig_tbl;
有关详细信息,请参阅 第 15.1.20.3 节“CREATE TABLE ... LIKE 语句”。
[AS]
query_expression
要从另一个表创建一个表,请在
CREATE TABLE
语句末尾添加一个SELECT
语句。CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
有关详细信息,请参阅 第 15.1.20.4 节“CREATE TABLE ... SELECT 语句”。
IGNORE | REPLACE
使用
SELECT
语句复制表时,IGNORE
和REPLACE
选项指示如何处理重复唯一键值的行。有关详细信息,请参阅 第 15.1.20.4 节“CREATE TABLE ... SELECT 语句”。
列数据类型和属性
每个表最多可以有 4096 列,但对于给定表,实际最大值可能更小,并且取决于 第 10.4.7 节“表列数和行大小限制” 中讨论的因素。
data_type
data_type
代表列定义中的数据类型。有关指定列数据类型可用的语法的完整描述,以及有关每种类型属性的信息,请参阅 第 13 章“数据类型”。AUTO_INCREMENT
仅适用于整数类型。字符数据类型(
CHAR
、VARCHAR
、TEXT
类型、ENUM
、SET
以及任何同义词)可以包含CHARACTER SET
来指定列的字符集。CHARSET
是CHARACTER SET
的同义词。可以使用COLLATE
属性为字符集指定排序规则,以及任何其他属性。有关详细信息,请参阅 第 12 章“字符集、排序规则、Unicode”。示例CREATE TABLE t (c CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
对于
CHAR
、VARCHAR
、BINARY
和VARBINARY
列,可以使用
语法来指定索引前缀长度,从而创建仅使用列值的前一部分的索引。col_name
(length
)BLOB
和TEXT
列也可以被索引,但必须给出前缀长度。非二进制字符串类型的长度以字符为单位,二进制字符串类型的长度以字节为单位。也就是说,索引条目由每个列值的前length
个字符组成,用于CHAR
、VARCHAR
和TEXT
列,每个列值的前length
个字节组成,用于BINARY
、VARBINARY
和BLOB
列。这样只索引列值的前缀可以使索引文件更小。有关索引前缀的更多信息,请参阅 第 15.1.15 节“CREATE INDEX 语句”。只有
InnoDB
和MyISAM
存储引擎支持对BLOB
和TEXT
列进行索引。例如CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
如果指定的索引前缀超过最大列数据类型大小,
CREATE TABLE
会按如下方式处理索引对于非唯一索引,如果启用了严格 SQL 模式,则会发生错误;如果未启用严格 SQL 模式,则索引长度会减少以保持在最大列数据类型大小内,并会生成警告。
对于唯一索引,无论 SQL 模式如何,都会发生错误,因为减少索引长度可能会允许插入不符合指定唯一性要求的非唯一条目。
JSON
列不能被索引。您可以通过在从JSON
列中提取标量值的生成列上创建索引来解决此限制。有关详细示例,请参阅 索引生成列以提供 JSON 列索引。
NOT NULL | NULL
如果未指定
NULL
或NOT NULL
,则该列将被视为已指定NULL
。在 MySQL 9.0 中,只有
InnoDB
、MyISAM
和MEMORY
存储引擎支持对可以具有NULL
值的列进行索引。在其他情况下,您必须将索引列声明为NOT NULL
,否则会导致错误。DEFAULT
为列指定默认值。有关默认值处理的更多信息,包括列定义不包含显式
DEFAULT
值的情况,请参阅 第 13.6 节“数据类型默认值”。如果启用了
NO_ZERO_DATE
或NO_ZERO_IN_DATE
SQL 模式,并且日期值的默认值不符合该模式,则如果未启用严格 SQL 模式,CREATE TABLE
会生成警告;如果启用了严格模式,则会生成错误。例如,在启用了NO_ZERO_IN_DATE
的情况下,c1 DATE DEFAULT '2010-00-00'
会生成警告。VISIBLE
、INVISIBLE
指定列的可见性。如果未出现任何关键字,则默认值为
VISIBLE
。表必须至少有一个可见列。尝试使所有列不可见会导致错误。有关详细信息,请参阅 第 15.1.20.10 节“不可见列”。AUTO_INCREMENT
整数列可以具有附加属性
AUTO_INCREMENT
。当您将NULL
(推荐)或0
值插入到索引的AUTO_INCREMENT
列时,该列将设置为下一个序列值。通常情况下,这将是
,其中value
+1value
是表中当前列的最大值。AUTO_INCREMENT
序列从1
开始。要插入行后检索
AUTO_INCREMENT
值,请使用LAST_INSERT_ID()
SQL 函数或mysql_insert_id()
C API 函数。请参阅 第 14.15 节“信息函数” 和 mysql_insert_id()。如果启用了
NO_AUTO_VALUE_ON_ZERO
SQL 模式,则您可以在AUTO_INCREMENT
列中将0
存储为0
,而不会生成新的序列值。请参阅 第 7.1.11 节“服务器 SQL 模式”。每个表只能有一个
AUTO_INCREMENT
列,它必须被索引,并且不能具有DEFAULT
值。只有当AUTO_INCREMENT
列仅包含正值时,它才能正常工作。插入负数被视为插入一个很大的正数。这样做是为了避免数字从正数“换行”到负数时出现的精度问题,并确保您不会意外地获得包含0
的AUTO_INCREMENT
列。对于
MyISAM
表,您可以在多列键中指定一个AUTO_INCREMENT
次要列。请参阅 第 5.6.9 节“使用 AUTO_INCREMENT”。为了使 MySQL 与某些 ODBC 应用程序兼容,您可以使用以下查询找到最后插入行的
AUTO_INCREMENT
值。SELECT * FROM tbl_name WHERE auto_col IS NULL
此方法要求
sql_auto_is_null
变量未设置为 0。请参阅 第 7.1.8 节“服务器系统变量”。有关
InnoDB
和AUTO_INCREMENT
的信息,请参见 第 17.6.1.6 节,“InnoDB 中的 AUTO_INCREMENT 处理”。有关AUTO_INCREMENT
和 MySQL 复制的信息,请参见 第 19.5.1.1 节,“复制和 AUTO_INCREMENT”.COMMENT
可以使用
COMMENT
选项为列指定注释,长度最多为 1024 个字符。注释将由SHOW CREATE TABLE
和SHOW FULL COLUMNS
语句显示。它还会在信息模式的COLUMNS
表的COLUMN_COMMENT
列中显示。COLUMN_FORMAT
在 NDB 集群中,还可以使用
COLUMN_FORMAT
为NDB
表的各个列指定数据存储格式。允许的列格式为FIXED
、DYNAMIC
和DEFAULT
。FIXED
用于指定固定宽度存储,DYNAMIC
允许列为可变宽度,DEFAULT
使列使用固定宽度或可变宽度存储,具体取决于列的数据类型(可能被ROW_FORMAT
说明符覆盖)。对于
NDB
表,COLUMN_FORMAT
的默认值为FIXED
。在 NDB 集群中,使用
COLUMN_FORMAT=FIXED
定义的列的最大可能偏移量为 8188 字节。有关更多信息和可能的解决方法,请参见 第 25.2.7.5 节,“NDB 集群中与数据库对象相关的限制”.COLUMN_FORMAT
目前对使用除NDB
之外的存储引擎的表的列没有影响。MySQL 9.0 会静默忽略COLUMN_FORMAT
。ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
选项用于指定主存储引擎和辅助存储引擎的列属性。这些选项为将来使用保留。分配给此选项的值是一个包含有效 JSON 文档或空字符串('')的字符串文字。无效 JSON 会被拒绝。
CREATE TABLE t1 (c1 INT ENGINE_ATTRIBUTE='{"key":"value"}');
ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
值可以重复,不会出错。在这种情况下,使用最后指定的值。ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
值不会由服务器检查,也不会在表存储引擎更改时清除。STORAGE
对于
NDB
表,可以使用STORAGE
子句指定列是存储在磁盘上还是在内存中。STORAGE DISK
使列存储在磁盘上,STORAGE MEMORY
使使用内存存储。使用的CREATE TABLE
语句仍必须包含TABLESPACE
子句mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) ENGINE NDB; ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140) mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) TABLESPACE ts_1 ENGINE NDB; Query OK, 0 rows affected (1.06 sec)
对于
NDB
表,STORAGE DEFAULT
等效于STORAGE MEMORY
。STORAGE
子句对使用除NDB
之外的存储引擎的表没有影响。STORAGE
关键字仅在与 NDB 集群一起提供的 mysqld 的构建中受支持;它在 MySQL 的任何其他版本中都无法识别,尝试使用STORAGE
关键字会导致语法错误。GENERATED ALWAYS
用于指定生成的列表达式。有关 生成列 的信息,请参见 第 15.1.20.8 节,“CREATE TABLE 和生成列”.
存储的生成列 可以被索引。
InnoDB
支持 虚拟生成列 的辅助索引。请参见 第 15.1.20.9 节,“辅助索引和生成列”.
索引、外键和 CHECK 约束
几个关键字适用于索引、外键和 CHECK
约束的创建。除了以下描述之外,有关一般背景信息,请参见 第 15.1.15 节,“CREATE INDEX 语句”、第 15.1.20.5 节,“FOREIGN KEY 约束” 和 第 15.1.20.6 节,“CHECK 约束”.
CONSTRAINT
symbol
CONSTRAINT
子句可用于命名约束。如果未给出该子句或未在symbol
CONSTRAINT
关键字后包含symbol
,MySQL 会自动生成一个约束名称,但有以下例外情况。如果使用symbol
值,它必须在每个架构(数据库)中针对每种约束类型是唯一的。重复的symbol
会导致错误。另请参见有关在 第 11.2.1 节,“标识符长度限制” 中生成的约束标识符的长度限制的讨论。注意如果在外国关键定义中没有给出
CONSTRAINT
子句,或者在symbol
CONSTRAINT
关键字后没有包含symbol
,MySQL 会自动生成一个约束名称。SQL 标准规定所有类型的约束(主键、唯一索引、外键、检查)都属于同一个命名空间。在 MySQL 中,每种约束类型在每个架构中都有自己的命名空间。因此,每种约束类型的名称必须在每个架构中是唯一的,但不同类型的约束可以具有相同的名称。
PRIMARY KEY
一个唯一索引,其中所有键列必须定义为
NOT NULL
。如果它们没有被显式声明为NOT NULL
,MySQL 会隐式(并静默)地将其声明为NOT NULL
。一个表只能有一个PRIMARY KEY
。PRIMARY KEY
的名称始终为PRIMARY
,因此不能用作任何其他类型索引的名称。如果没有
PRIMARY KEY
并且应用程序要求表中的PRIMARY KEY
,MySQL 会将第一个没有NULL
列的UNIQUE
索引作为PRIMARY KEY
返回。在
InnoDB
表中,请将PRIMARY KEY
保持简短,以最大程度地减少辅助索引的存储开销。每个辅助索引条目包含对应行的主键列的副本。(请参见 第 17.6.2.1 节,“聚簇索引和辅助索引”。)在创建的表中,
PRIMARY KEY
位于第一位,其次是所有UNIQUE
索引,然后是非唯一索引。这有助于 MySQL 优化器确定优先使用哪个索引,以及更快地检测到重复的UNIQUE
键。PRIMARY KEY
可以是多列索引。但是,不能使用列规范中的PRIMARY KEY
键属性创建多列索引。这样做只会将该单个列标记为主键。必须使用单独的PRIMARY KEY(
子句。key_part
, ...)如果表具有一个由具有整数类型的单个列组成的
PRIMARY KEY
或UNIQUE NOT NULL
索引,则可以在SELECT
语句中使用_rowid
来引用索引列,如 唯一索引 中所述。在 MySQL 中,
PRIMARY KEY
的名称为PRIMARY
。对于其他索引,如果未分配名称,则会分配与第一个索引列相同的名称,并带有可选后缀 (_2
、_3
、...
) 以使其唯一。可以使用SHOW INDEX FROM
查看表的索引名称。请参见 第 15.7.7.23 节,“SHOW INDEX 语句”.tbl_name
KEY | INDEX
KEY
通常是INDEX
的同义词。当在列定义中给出时,键属性PRIMARY KEY
也可以指定为KEY
。这是为了与其他数据库系统兼容而实现的。UNIQUE
UNIQUE
索引会创建一个约束,这样索引中的所有值都必须不同。如果尝试添加一个与现有行相匹配的键值的新行,则会发生错误。对于所有引擎,UNIQUE
索引允许为可以包含NULL
的列设置多个NULL
值。如果在UNIQUE
索引中为列指定前缀值,则列值必须在前缀长度内是唯一的。如果表具有一个由具有整数类型的单个列组成的
PRIMARY KEY
或UNIQUE NOT NULL
索引,则可以在SELECT
语句中使用_rowid
来引用索引列,如 唯一索引 中所述。FULLTEXT
FULLTEXT
索引是一种用于全文本搜索的特殊索引类型。只有InnoDB
和MyISAM
存储引擎支持FULLTEXT
索引。它们只能从CHAR
、VARCHAR
和TEXT
列创建。索引始终在整个列上进行;不支持列前缀索引,并且如果指定了任何前缀长度,则会忽略它。有关操作的详细信息,请参见 第 14.9 节,“全文本搜索函数”。如果全文本索引和搜索操作需要特殊处理,则可以将WITH PARSER
子句指定为index_option
值,以将解析器插件与索引关联。此子句仅对FULLTEXT
索引有效。InnoDB
和MyISAM
支持全文本解析器插件。有关更多信息,请参见 全文本解析器插件 和 编写全文本解析器插件。SPATIAL
可以在空间数据类型上创建
SPATIAL
索引。空间类型仅适用于InnoDB
和MyISAM
表,并且索引列必须声明为NOT NULL
。请参见 第 13.4 节,“空间数据类型”.FOREIGN KEY
MySQL 支持外键,它允许您跨表交叉引用相关数据,以及外键约束,它有助于保持这些分散数据的完整性。有关定义和选项信息,请参阅
reference_definition
和reference_option
。使用
InnoDB
存储引擎的分区表不支持外键。有关更多信息,请参阅 第 26.6 节,“分区限制和局限性”。CHECK
CHECK
子句允许创建约束,以检查表行中的数据值。请参阅 第 15.1.20.6 节,“CHECK 约束”。key_part
key_part
规范可以用ASC
或DESC
结尾,以指定索引值是按升序还是降序存储。如果未指定顺序说明符,则默认为升序。前缀由
length
属性定义,对于使用REDUNDANT
或COMPACT
行格式的InnoDB
表,前缀长度限制为 767 字节。对于使用DYNAMIC
或COMPRESSED
行格式的InnoDB
表,前缀长度限制为 3072 字节。对于MyISAM
表,前缀长度限制为 1000 字节。前缀 限制 以字节为单位。但是,
CREATE TABLE
、ALTER TABLE
和CREATE INDEX
语句中索引规范的前缀 长度 被解释为非二进制字符串类型(CHAR
、VARCHAR
、TEXT
)的字符数和二进制字符串类型(BINARY
、VARBINARY
、BLOB
)的字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时,请牢记这一点。key_part
规范的expr
可以采用(CAST
的形式,以在json_path
AStype
ARRAY)JSON
列上创建多值索引。 多值索引 提供了有关创建、使用以及多值索引限制和局限性的详细信息。
index_type
某些存储引擎允许您在创建索引时指定索引类型。
index_type
说明符的语法为USING
。type_name
示例
CREATE TABLE lookup (id INT, INDEX USING BTREE (id) ) ENGINE = MEMORY;
USING
的首选位置是在索引列列表之后。它可以在列列表之前给出,但在此位置使用该选项的支持已过时,您应该预期它将在未来的 MySQL 版本中被移除。index_option
index_option
值指定索引的附加选项。KEY_BLOCK_SIZE
对于
MyISAM
表,KEY_BLOCK_SIZE
可选地指定用于索引键块的大小(以字节为单位)。该值被视为提示;如有必要,可以使用不同的尺寸。为单个索引定义指定的KEY_BLOCK_SIZE
值会覆盖表级KEY_BLOCK_SIZE
值。有关表级
KEY_BLOCK_SIZE
属性的信息,请参阅 表选项。WITH PARSER
WITH PARSER
选项仅可与FULLTEXT
索引一起使用。如果全文索引和搜索操作需要特殊处理,它会将解析器插件与索引关联。InnoDB
和MyISAM
支持全文解析器插件。如果您有一个关联了全文解析器插件的MyISAM
表,则可以使用ALTER TABLE
将该表转换为InnoDB
。COMMENT
索引定义可以包含一个可选的注释,最多 1024 个字符。
您可以使用
index_option
COMMENT
子句为单个索引设置InnoDB
MERGE_THRESHOLD
值。请参阅 第 17.8.11 节,“配置索引页面的合并阈值”。VISIBLE
、INVISIBLE
指定索引可见性。索引默认可见。不可见索引不被优化器使用。索引可见性的指定适用于除主键(显式或隐式)以外的索引。有关更多信息,请参阅 第 10.3.12 节,“不可见索引”。
ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
选项用于指定主存储引擎和辅助存储引擎的索引属性。这些选项保留供将来使用。
有关允许的
index_option
值的更多信息,请参阅 第 15.1.15 节,“CREATE INDEX 语句”。有关索引的更多信息,请参阅 第 10.3.1 节,“MySQL 如何使用索引”。有关
reference_definition
语法详细信息和示例,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”。InnoDB
和NDB
表支持检查外键约束。引用的表列必须始终显式命名。ON DELETE
和ON UPDATE
操作都支持外键。有关更详细的信息和示例,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”。对于其他存储引擎,MySQL 服务器会在
CREATE TABLE
语句中解析并忽略FOREIGN KEY
语法。重要对于熟悉 ANSI/ISO SQL 标准的用户,请注意,包括
InnoDB
在内的任何存储引擎都不识别或强制执行引用完整性约束定义中使用的MATCH
子句。使用显式MATCH
子句不会产生指定的效果,还会导致ON DELETE
和ON UPDATE
子句被忽略。出于这些原因,应避免指定MATCH
。SQL 标准中的
MATCH
子句控制如何处理复合(多列)外键中的NULL
值,方法是在与主键进行比较时。InnoDB
本质上实现了由MATCH SIMPLE
定义的语义,允许外键全部或部分为NULL
。在这种情况下,包含此类外键的(子表)行被允许插入,并且与引用(父)表中的任何行都不匹配。可以使用触发器来实现其他语义。此外,MySQL 要求引用的列被索引以提高性能。但是,
InnoDB
不强制执行任何要求引用的列被声明为UNIQUE
或NOT NULL
的要求。对于UPDATE
或DELETE CASCADE
等操作,处理对非唯一键或包含NULL
值的键的外键引用没有明确定义。建议使用仅引用既是UNIQUE
(或PRIMARY
)又是NOT NULL
的键的外键。MySQL 接受 “内联
REFERENCES
规范”(如 SQL 标准中定义的那样),其中引用定义为列规范的一部分。MySQL 还接受对父表主键的隐式引用。有关更多信息,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”,以及 第 1.7.2.3 节,“FOREIGN KEY 约束差异”。有关
RESTRICT
、CASCADE
、SET NULL
、NO ACTION
和SET DEFAULT
选项的信息,请参阅 第 15.1.20.5 节,“FOREIGN KEY 约束”。
表选项
表选项用于优化表的行为。在大多数情况下,您不必指定任何选项。这些选项适用于所有存储引擎,除非另有说明。不适用于特定存储引擎的选项可能会被接受并作为表定义的一部分被记住。如果稍后使用 ALTER TABLE
将表转换为使用其他存储引擎,这些选项就会适用。
ENGINE
使用下表中所示的名称之一指定表的存储引擎。引擎名称可以加引号或不加引号。引用的名称
'DEFAULT'
会被识别,但会被忽略。存储引擎 描述 InnoDB
具有行级锁和外键的事务安全表。新表的默认存储引擎。请参阅 第 17 章,InnoDB 存储引擎,尤其是如果您有 MySQL 经验但刚接触 InnoDB
,请参阅 第 17.1 节,“InnoDB 简介”。MyISAM
主要用于只读或读为主的工作负载的二进制可移植存储引擎。请参阅 第 18.2 节,“MyISAM 存储引擎”。 MEMORY
此存储引擎的数据仅存储在内存中。请参阅 第 18.3 节,“MEMORY 存储引擎”。 CSV
将行存储为逗号分隔值格式的表。请参阅 第 18.4 节,“CSV 存储引擎”。 ARCHIVE
存档存储引擎。请参阅 第 18.5 节,“ARCHIVE 存储引擎”。 EXAMPLE
示例引擎。请参阅 第 18.9 节,“EXAMPLE 存储引擎”。 FEDERATED
访问远程表的存储引擎。参见 第 18.8 节,“FEDERATED 存储引擎”。 HEAP
这是 MEMORY
的同义词。MERGE
一组用作单个表的 MyISAM
表。也称为MRG_MyISAM
。参见 第 18.7 节,“MERGE 存储引擎”。NDB
支持事务和外键的集群式、容错、基于内存的表。也称为 NDBCLUSTER
。参见 第 25 章,MySQL NDB 集群 9.0。默认情况下,如果指定的存储引擎不可用,语句将失败并显示错误。可以通过从服务器 SQL 模式中删除
NO_ENGINE_SUBSTITUTION
来覆盖此行为(参见 第 7.1.11 节,“服务器 SQL 模式”),这样 MySQL 就会允许用默认存储引擎替换指定的引擎。通常,在这种情况下,默认存储引擎是InnoDB
,它是default_storage_engine
系统变量的默认值。当NO_ENGINE_SUBSTITUTION
被禁用时,如果存储引擎规范未被遵守,将会出现警告。AUTOEXTEND_SIZE
定义了当
InnoDB
表空间已满时,其大小增长的量。设置值必须是 4MB 的倍数。默认设置是 0,这会导致表空间根据隐式默认行为进行扩展。有关更多信息,请参见 第 17.6.3.9 节,“表空间 AUTOEXTEND_SIZE 配置”。AUTO_INCREMENT
表的初始
AUTO_INCREMENT
值。在 MySQL 9.0 中,这适用于MyISAM
、MEMORY
、InnoDB
和ARCHIVE
表。要为不支持AUTO_INCREMENT
表选项的引擎设置第一个自动递增值,请在创建表后插入一个 “dummy” 行,其值比所需值小 1,然后删除该虚拟行。对于在
CREATE TABLE
语句中支持AUTO_INCREMENT
表选项的引擎,您也可以使用ALTER TABLE
来重置tbl_name
AUTO_INCREMENT =N
AUTO_INCREMENT
值。该值不能低于该列当前的最大值。AVG_ROW_LENGTH
对表的平均行长度的近似值。您只需要为具有可变大小行的巨大表设置此值。
当您创建一个
MyISAM
表时,MySQL 使用MAX_ROWS
和AVG_ROW_LENGTH
选项的乘积来确定结果表的尺寸。如果未指定这两个选项,则MyISAM
数据和索引文件的最大尺寸默认值为 256TB。(如果您的操作系统不支持如此大的文件,则表尺寸将受到文件尺寸限制。)如果您希望缩小指针尺寸以使索引更小更快,并且实际上并不需要大型文件,则可以通过设置myisam_data_pointer_size
系统变量来降低默认指针尺寸。(参见 第 7.1.8 节,“服务器系统变量”。)如果您希望所有表都能增长到超过默认限制,并且愿意让您的表比必要时稍慢且更大,则可以通过设置此变量来增加默认指针尺寸。将该值设置为 7 允许表尺寸达到 65,536TB。[DEFAULT] CHARACTER SET
指定表的默认字符集。
CHARSET
是CHARACTER SET
的同义词。如果字符集名称为DEFAULT
,则使用数据库字符集。CHECKSUM
如果您希望 MySQL 为所有行维护实时校验和(即 MySQL 会在表更改时自动更新的校验和),则将其设置为 1。这会使表的更新速度略慢,但也更容易找到损坏的表。
CHECKSUM TABLE
语句会报告校验和。(仅限MyISAM
。)[DEFAULT] COLLATE
指定表的默认排序规则。
COMMENT
表的注释,最多 2048 个字符。
您可以使用
table_option
COMMENT
子句为InnoDB
设置MERGE_THRESHOLD
值。参见 第 17.8.11 节,“配置索引页面的合并阈值”。设置 NDB_TABLE 选项。 在创建
NDB
表的CREATE TABLE
或更改NDB
表的ALTER TABLE
语句中,表的注释也可以用来指定最多四个NDB_TABLE
选项NOLOGGING
、READ_BACKUP
、PARTITION_BALANCE
或FULLY_REPLICATED
,作为一组名称-值对,在必要时用逗号分隔,紧跟在开始引用的注释文本的字符串NDB_TABLE=
之后。以下显示了使用此语法的示例语句(强调部分):CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100), c3 VARCHAR(100) ) ENGINE=NDB COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";
引用的字符串中不允许使用空格。该字符串不区分大小写。
注释会显示在
SHOW CREATE TABLE
的输出中。注释文本也可以作为 MySQL Information SchemaTABLES
表的 TABLE_COMMENT 列获得。此注释语法也适用于
NDB
表的ALTER TABLE
语句。请记住,与ALTER TABLE
一起使用的表注释将替换表可能以前具有的任何现有注释。在表注释中设置
MERGE_THRESHOLD
选项对于NDB
表不受支持(将被忽略)。有关完整的语法信息和示例,请参见 第 15.1.20.12 节,“设置 NDB 注释选项”。
COMPRESSION
用于
InnoDB
表的页面级压缩的压缩算法。支持的值包括Zlib
、LZ4
和None
。COMPRESSION
属性是在透明页面压缩功能引入时引入的。页面压缩仅支持位于 每个表一个文件 表空间中的InnoDB
表,并且仅在支持稀疏文件和打孔的 Linux 和 Windows 平台上可用。有关更多信息,请参见 第 17.9.2 节,“InnoDB 页面压缩”。CONNECTION
FEDERATED
表的连接字符串。注意早期版本的 MySQL 使用
COMMENT
选项作为连接字符串。DATA DIRECTORY
、INDEX DIRECTORY
对于
InnoDB
,DATA DIRECTORY='
子句允许在数据目录之外创建表。必须启用directory
'innodb_file_per_table
变量才能使用DATA DIRECTORY
子句。必须指定完整目录路径,并且该路径必须为InnoDB
所知。有关更多信息,请参见 第 17.6.1.2 节,“在外部创建表”。在创建
MyISAM
表时,您可以使用DATA DIRECTORY='
子句、directory
'INDEX DIRECTORY='
子句或两者。它们分别指定了directory
'MyISAM
表的数据文件和索引文件的位置。与InnoDB
表不同,MySQL 在使用DATA DIRECTORY
或INDEX DIRECTORY
选项创建MyISAM
表时不会创建与数据库名称相对应的子目录。文件将在指定的目录中创建。您必须具有
FILE
权限才能使用DATA DIRECTORY
或INDEX DIRECTORY
表选项。重要表级
DATA DIRECTORY
和INDEX DIRECTORY
选项对于分区表将被忽略。(错误号 #32091)这些选项仅在您未使用
--skip-symbolic-links
选项时有效。您的操作系统还必须具有可正常工作的线程安全的realpath()
调用。参见 第 10.12.2.2 节,“在 Unix 上对 MyISAM 表使用符号链接”,以获取更多完整信息。如果
MyISAM
表是在没有DATA DIRECTORY
选项的情况下创建的,则.MYD
文件将创建在数据库目录中。默认情况下,如果MyISAM
在这种情况下发现了一个现有的.MYD
文件,则会覆盖它。对于没有INDEX DIRECTORY
选项创建的表,.MYI
文件也一样。要禁止此行为,请使用--keep_files_on_create
选项启动服务器,在这种情况下,MyISAM
不会覆盖现有文件,而是返回错误。如果
MyISAM
表是在使用DATA DIRECTORY
或INDEX DIRECTORY
选项的情况下创建的,并且发现了一个现有的.MYD
或.MYI
文件,则MyISAM
始终返回错误,并且不会覆盖指定目录中的文件。重要您不能使用包含 MySQL 数据目录的路径名与
DATA DIRECTORY
或INDEX DIRECTORY
。这包括分区表和单个表分区。(参见错误号 #32167。)DELAY_KEY_WRITE
如果您希望延迟到表关闭时才更新表的键,则将其设置为 1。参见 第 7.1.8 节,“服务器系统变量” 中对
delay_key_write
系统变量的描述。(仅限MyISAM
。)ENCRYPTION
ENCRYPTION
子句启用或禁用InnoDB
表的页面级数据加密。在启用加密之前,必须安装和配置一个密钥环插件。ENCRYPTION
子句可以在在每个表一个文件表空间中创建表时指定,也可以在创建通用表空间中的表时指定。如果未指定
ENCRYPTION
子句,则表将继承默认的架构加密。如果启用了table_encryption_privilege_check
变量,则需要TABLE_ENCRYPTION_ADMIN
权限才能创建具有与默认架构加密不同的ENCRYPTION
子句设置的表。在通用表空间中创建表时,表和表空间加密必须匹配。如果使用的存储引擎不支持加密,则不允许指定
ENCRYPTION
子句的值不为'N'
或''
。有关更多信息,请参阅 第 17.13 节,“InnoDB 数据休眠加密”。
ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
选项用于为主要和次要存储引擎指定表属性。这些选项保留供将来使用。分配给这两个选项的值必须是包含有效 JSON 文档的字符串文字或空字符串 (''). 无效 JSON 将被拒绝。
CREATE TABLE t1 (c1 INT) ENGINE_ATTRIBUTE='{"key":"value"}';
ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
值可以重复,不会出错。在这种情况下,使用最后指定的值。ENGINE_ATTRIBUTE
和SECONDARY_ENGINE_ATTRIBUTE
值不会由服务器检查,也不会在表存储引擎更改时清除。INSERT_METHOD
如果要将数据插入
MERGE
表,则必须使用INSERT_METHOD
指定应将行插入的表。INSERT_METHOD
是一个仅对MERGE
表有用的选项。使用FIRST
或LAST
值以使插入进入第一个或最后一个表,或使用NO
值以阻止插入。请参阅 第 18.7 节,“MERGE 存储引擎”。KEY_BLOCK_SIZE
对于
MyISAM
表,KEY_BLOCK_SIZE
可选地指定用于索引键块的大小(以字节为单位)。该值被视为提示;如有必要,可以使用不同的尺寸。为单个索引定义指定的KEY_BLOCK_SIZE
值会覆盖表级KEY_BLOCK_SIZE
值。对于
InnoDB
表,KEY_BLOCK_SIZE
指定用于压缩
的页
大小(以千字节为单位)InnoDB
表。KEY_BLOCK_SIZE
值被视为提示;如果需要,InnoDB
可以使用不同的尺寸。KEY_BLOCK_SIZE
只能小于或等于innodb_page_size
值。值 0 表示默认的压缩页大小,它等于innodb_page_size
值的一半。根据innodb_page_size
,可能的KEY_BLOCK_SIZE
值包括 0、1、2、4、8 和 16。有关更多信息,请参阅 第 17.9.1 节,“InnoDB 表压缩”。Oracle 建议在为
InnoDB
表指定KEY_BLOCK_SIZE
时启用innodb_strict_mode
。当innodb_strict_mode
启用时,指定无效的KEY_BLOCK_SIZE
值将返回错误。如果innodb_strict_mode
禁用,无效的KEY_BLOCK_SIZE
值将导致警告,并且KEY_BLOCK_SIZE
选项将被忽略。响应
SHOW TABLE STATUS
的Create_options
列报告表实际使用的KEY_BLOCK_SIZE
,SHOW CREATE TABLE
也是如此。InnoDB
仅在表级别支持KEY_BLOCK_SIZE
。32KB 和 64KB
innodb_page_size
值不支持KEY_BLOCK_SIZE
。InnoDB
表压缩不支持这些页面大小。在创建临时表时,
InnoDB
不支持KEY_BLOCK_SIZE
选项。MAX_ROWS
您计划存储在表中的最大行数。这不是硬限制,而是对存储引擎的提示,表明表必须能够存储至少这么多行。
重要使用
MAX_ROWS
与NDB
表来控制表分区数量已过时。在更高版本中,它仍受支持以实现向后兼容,但可能会在将来的版本中删除。请改用 PARTITION_BALANCE;请参阅 设置 NDB_TABLE 选项。NDB
存储引擎将此值视为最大值。如果您计划创建非常大的 NDB Cluster 表(包含数百万行),则应使用此选项来确保NDB
在哈希表中分配足够的索引槽位,用于通过设置MAX_ROWS = 2 *
来存储表的主键的哈希值,其中rows
rows
是您预计要插入表的行数。最大
MAX_ROWS
值为 4294967295;更大的值将被截断为该限制。MIN_ROWS
您计划存储在表中的最小行数。
MEMORY
存储引擎使用此选项作为关于内存使用的提示。PACK_KEYS
仅对
MyISAM
表起作用。如果要创建更小的索引,请将此选项设置为 1。这通常会使更新速度变慢,而读取速度更快。将选项设置为 0 将禁用所有键打包。将其设置为DEFAULT
将告诉存储引擎仅打包长的CHAR
、VARCHAR
、BINARY
或VARBINARY
列。如果不使用
PACK_KEYS
,默认情况下会打包字符串,但不会打包数字。如果使用PACK_KEYS=1
,也会打包数字。在打包二进制数字键时,MySQL 使用前缀压缩
每个键都需要一个额外的字节来指示前一个键中有多少字节对于下一个键是相同的。
指向行的指针以高字节优先的顺序直接存储在键之后,以提高压缩效率。
这意味着,如果在两行连续的行上有很多相同的键,则所有后续的 “相同” 键通常只占用两个字节(包括指向行的指针)。将此与通常情况下后续键占用
storage_size_for_key + pointer_size
(其中指针大小通常为 4)的情况进行比较。相反,只有在有很多相同的数字时,才能从前缀压缩中获得显着的益处。如果所有键都完全不同,则每个键会多使用一个字节(如果键不是可以具有NULL
值的键)。(在这种情况下,打包键的长度存储在用于标记键是否为NULL
的相同字节中。)PASSWORD
此选项未使用。
ROW_FORMAT
定义存储行的物理格式。
在创建 严格模式 禁用的表时,如果指定的行格式不受支持,则使用存储引擎的默认行格式。表的实际行格式在响应
SHOW TABLE STATUS
的Row_format
列中报告。Create_options
列显示在CREATE TABLE
语句中指定的行格式,SHOW CREATE TABLE
也是如此。行格式选项根据用于表的存储引擎的不同而不同。
对于
InnoDB
表默认行格式由
innodb_default_row_format
定义,默认设置为DYNAMIC
。当未定义ROW_FORMAT
选项或使用ROW_FORMAT=DEFAULT
时,使用默认行格式。如果未定义
ROW_FORMAT
选项,或者使用ROW_FORMAT=DEFAULT
,则重建表的操作也会静默地将表的行格式更改为由innodb_default_row_format
定义的默认值。有关更多信息,请参阅 定义表的行格式。为了更有效地
InnoDB
存储数据类型,尤其是BLOB
类型,请使用DYNAMIC
。请参阅 DYNAMIC 行格式 以了解与DYNAMIC
行格式相关的要求。要为
InnoDB
表启用压缩,请指定ROW_FORMAT=COMPRESSED
。在创建临时表时,不支持ROW_FORMAT=COMPRESSED
选项。有关与COMPRESSED
行格式相关的要求,请参阅 第 17.9 节,“InnoDB 表和页面压缩”。MySQL 较旧版本中使用的行格式仍然可以通过指定
REDUNDANT
行格式来请求。当您指定非默认的
ROW_FORMAT
子句时,请考虑也启用innodb_strict_mode
配置选项。ROW_FORMAT=FIXED
不受支持。如果在innodb_strict_mode
禁用的情况下指定ROW_FORMAT=FIXED
,InnoDB
会发出警告并假定为ROW_FORMAT=DYNAMIC
。如果在innodb_strict_mode
启用的情况下指定ROW_FORMAT=FIXED
(这是默认设置),InnoDB
将返回错误。有关
InnoDB
行格式的更多信息,请参阅 第 17.10 节,“InnoDB 行格式”。
对于
MyISAM
表,选项值可以是FIXED
或DYNAMIC
,用于静态或可变长度行格式。 myisampack 将类型设置为COMPRESSED
。请参阅 第 18.2.3 节,“MyISAM 表存储格式”。对于
NDB
表,默认的ROW_FORMAT
是DYNAMIC
。START TRANSACTION
这是一个内部使用表选项,用于允许在使用基于行的复制以及支持原子 DDL 的存储引擎时,将
CREATE TABLE ... SELECT
记录为二进制日志中的单个原子事务。仅允许BINLOG
、COMMIT
和ROLLBACK
语句在CREATE TABLE ... START TRANSACTION
之后。有关相关信息,请参阅 第 15.1.1 节,“原子数据定义语句支持”。STATS_AUTO_RECALC
指定是否自动重新计算 持久性统计信息 用于
InnoDB
表。值DEFAULT
导致表的持久性统计信息设置由innodb_stats_auto_recalc
配置选项决定。值1
导致当表中 10% 的数据发生变化时重新计算统计信息。值0
阻止自动重新计算此表;使用此设置,在对表进行大量更改后,发出ANALYZE TABLE
语句以重新计算统计信息。有关持久性统计信息功能的更多信息,请参见 第 17.8.10.1 节,“配置持久性优化器统计信息参数”。STATS_PERSISTENT
指定是否为
InnoDB
表启用 持久性统计信息。值DEFAULT
导致表的持久性统计信息设置由innodb_stats_persistent
配置选项决定。值1
为表启用持久性统计信息,而值0
关闭此功能。在通过CREATE TABLE
或ALTER TABLE
语句启用持久性统计信息后,在将代表性数据加载到表中后,发出ANALYZE TABLE
语句以计算统计信息。有关持久性统计信息功能的更多信息,请参见 第 17.8.10.1 节,“配置持久性优化器统计信息参数”。STATS_SAMPLE_PAGES
估计索引列的基数和其他统计信息(例如由
ANALYZE TABLE
计算的统计信息)时要采样的索引页数。有关更多信息,请参见 第 17.8.10.1 节,“配置持久性优化器统计信息参数”。TABLESPACE
可以使用
TABLESPACE
子句在现有的一般表空间、每个表文件表空间或系统表空间中创建InnoDB
表。CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name
您指定的一般表空间必须在使用
TABLESPACE
子句之前存在。有关一般表空间的信息,请参见 第 17.6.3.3 节,“一般表空间”。
是一个区分大小写的标识符。它可以是带引号的或不带引号的。斜杠字符 (“/”) 不允许。以 “innodb_” 开头的名称保留供特殊使用。tablespace_name
要在系统表空间中创建表,请将
innodb_system
指定为表空间名称。CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system
使用
TABLESPACE [=] innodb_system
,无论innodb_file_per_table
设置如何,您都可以将任何未压缩行格式的表放置在系统表空间中。例如,您可以使用TABLESPACE [=] innodb_system
将具有ROW_FORMAT=DYNAMIC
的表添加到系统表空间。要在每个表文件表空间中创建表,请将
innodb_file_per_table
指定为表空间名称。CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table
注意如果启用了
innodb_file_per_table
,您无需指定TABLESPACE=innodb_file_per_table
来创建InnoDB
每个表文件表空间。当启用innodb_file_per_table
时,InnoDB
表默认情况下是在每个表文件表空间中创建的。可以使用
DATA DIRECTORY
子句与CREATE TABLE ... TABLESPACE=innodb_file_per_table
结合使用,但在其他情况下,不支持将其与TABLESPACE
子句结合使用。在DATA DIRECTORY
子句中指定的目录必须为InnoDB
所知。有关更多信息,请参见 使用 DATA DIRECTORY 子句。注意在
CREATE TEMPORARY TABLE
中支持使用TABLESPACE = innodb_file_per_table
和TABLESPACE = innodb_temporary
子句已弃用;预计在 MySQL 的未来版本中将删除它。仅当使用
NDB
表时才使用STORAGE
表选项。STORAGE
确定使用的存储类型,可以是DISK
或MEMORY
。TABLESPACE ... STORAGE DISK
将表分配到 NDB Cluster 磁盘数据表空间。STORAGE DISK
不能在CREATE TABLE
中使用,除非在前面有TABLESPACE
tablespace_name
。对于
STORAGE MEMORY
,表空间名称是可选的,因此,您可以使用TABLESPACE
或简单地使用tablespace_name
STORAGE MEMORYSTORAGE MEMORY
来明确指定表是内存中的。有关更多信息,请参见 第 25.6.11 节,“NDB Cluster 磁盘数据表”。
用于将一组相同的
MyISAM
表作为单个表访问。这仅适用于MERGE
表。请参见 第 18.7 节,“MERGE 存储引擎”。您必须对映射到
MERGE
表的表具有SELECT
、UPDATE
和DELETE
权限。注意以前,所有使用的表都必须与
MERGE
表本身位于同一个数据库中。此限制不再适用。
表分区
partition_options
可用于控制使用 CREATE TABLE
创建的表的划分。
本节开头 partition_options
语法中显示的并非所有选项都适用于所有分区类型。请参见以下各个类型的列表,了解特定于每种类型的 信息,并参见 第 26 章,分区,了解有关 MySQL 中分区的运作和使用的更完整信息,以及有关创建表和其他与 MySQL 分区相关的语句的更多示例。
可以修改分区、合并分区、将分区添加到表以及从表中删除分区。有关执行这些任务的 MySQL 语句的基本信息,请参见 第 15.1.9 节,“ALTER TABLE 语句”。有关更详细的描述和示例,请参见 第 26.3 节,“分区管理”。
PARTITION BY
如果使用,
partition_options
子句以PARTITION BY
开头。此子句包含用于确定分区的函数;该函数返回一个整数,范围从 1 到num
,其中num
是分区的数量。(表中可能包含的用户定义分区的最大数量为 1024;此最大值中包含了稍后在本节中讨论的子分区的数量。)注意在
PARTITION BY
子句中使用的表达式 (expr
) 不能引用正在创建的表中不存在的任何列;此类引用明确不允许,并会导致语句因错误而失败。(错误 #29444)HASH(
expr
)对一个或多个列进行哈希处理,以创建用于放置和定位行的键。
expr
是使用一个或多个表列的表达式。这可以是任何有效的 MySQL 表达式(包括 MySQL 函数),它产生单个整数值。例如,以下两者都是使用PARTITION BY HASH
的有效CREATE TABLE
语句CREATE TABLE t1 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1); CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH ( YEAR(col3) );
您不能对
PARTITION BY HASH
使用VALUES LESS THAN
或VALUES IN
子句。PARTITION BY HASH
使用expr
除以分区数(即模数)的余数。有关示例和更多信息,请参见 第 26.2.4 节,“HASH 分区”。关键字
LINEAR
涉及一种略有不同的算法。在这种情况下,存储行的分区的编号是通过一个或多个逻辑AND
操作的结果计算得出的。有关线性哈希的讨论和示例,请参见 第 26.2.4.1 节,“线性 HASH 分区”。KEY(
column_list
)这类似于
HASH
,只是 MySQL 提供了哈希函数,以保证数据均匀分布。column_list
参数只是一个或多个表列的列表(最大:16)。此示例显示了一个简单地按键划分的表,该表有 4 个分区CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;
对于按键划分的表,您可以通过使用
LINEAR
关键字来使用线性分区。这与按HASH
划分的表的效果相同。也就是说,分区的编号是使用&
运算符而不是模数找到的(有关详细信息,请参见 第 26.2.4.1 节,“线性 HASH 分区” 和 第 26.2.5 节,“KEY 分区”)。此示例使用按键的线性分区将数据分配到 5 个分区之间CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5;
选项
ALGORITHM={1 | 2}
支持使用[SUB]PARTITION BY [LINEAR] 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 或更高版本的服务器上创建按KEY
或LINEAR KEY
划分的表,这些表可以在 MySQL 5.1 服务器上使用。有关更多信息,请参见 第 15.1.9.1 节,“ALTER TABLE 分区操作”。mysqldump 将此选项写入版本化的注释中。
ALGORITHM=1
在SHOW CREATE TABLE
的输出中,使用版本化注释的方式显示,与 mysqldump 相同。ALGORITHM=2
始终从SHOW CREATE TABLE
输出中省略,即使在创建原始表时指定了此选项。您不能将
VALUES LESS THAN
或VALUES IN
子句与PARTITION BY KEY
一起使用。RANGE(
expr
)在这种情况下,
expr
使用一组VALUES LESS THAN
运算符显示一个值范围。使用范围分区时,必须使用VALUES LESS THAN
定义至少一个分区。您不能将VALUES IN
与范围分区一起使用。注意对于按
RANGE
分区的表,VALUES LESS THAN
必须与整型字面量值或计算结果为单个整型值的表达式一起使用。在 MySQL 9.0 中,您可以克服使用PARTITION BY RANGE COLUMNS
定义的表中的此限制,如本节后面所述。假设您有一个表,您希望根据以下方案对包含年值的列进行分区。
分区编号 年范围 0 1990 年及之前 1 1991 年至 1994 年 2 1995 年至 1998 年 3 1999 年至 2002 年 4 2003 年至 2005 年 5 2006 年及之后 实现此类分区方案的表可以通过以下所示的
CREATE TABLE
语句来实现CREATE TABLE t1 ( year_col INT, some_data INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2002), PARTITION p4 VALUES LESS THAN (2006), PARTITION p5 VALUES LESS THAN MAXVALUE );
PARTITION ... VALUES LESS THAN ...
语句按连续方式工作。VALUES LESS THAN MAXVALUE
用于指定大于否则指定的最大值的“剩余” 值。VALUES LESS THAN
子句按顺序工作,类似于switch ... case
块的case
部分(如在 C、Java 和 PHP 等许多编程语言中找到的)。也就是说,这些子句必须以这样的方式排列:在每个后续VALUES LESS THAN
中指定的上限大于前一个上限,其中引用MAXVALUE
的上限在列表中最后出现。RANGE COLUMNS(
column_list
)此
RANGE
变体便于对使用多个列的范围条件的查询进行分区修剪(即,具有WHERE a = 1 AND b < 10
或WHERE a = 1 AND b = 10 AND c < 10
等条件)。它允许您通过在COLUMNS
子句中使用列列表,并在每个PARTITION ... VALUES LESS THAN (
分区定义子句中使用一组列值来指定多个列中的值范围。(在最简单的情况下,此集合包含单个列。)value_list
)column_list
和value_list
中可以引用的最大列数为 16。COLUMNS
子句中使用的column_list
只能包含列名;列表中的每列必须是以下 MySQL 数据类型之一:整型;字符串类型;以及时间或日期列类型。使用BLOB
、TEXT
、SET
、ENUM
、BIT
或空间数据类型的列不允许;使用浮点数类型的列也不允许。您也不能在COLUMNS
子句中使用函数或算术表达式。分区定义中使用的
VALUES LESS THAN
子句必须为COLUMNS()
子句中出现的每列指定一个字面量值;也就是说,用于每个VALUES LESS THAN
子句的值列表必须包含与COLUMNS
子句中列出的列数一样多的值。尝试在VALUES LESS THAN
子句中使用比COLUMNS
子句中多的值或少的值会导致语句失败,并出现错误 分区使用列列表不一致...。您不能对VALUES LESS THAN
中出现的任何值使用NULL
。在以下示例中,可以为除第一个列以外的给定列多次使用MAXVALUE
CREATE TABLE rc ( a INT NOT NULL, b INT NOT NULL ) PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (10,5), PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (50,MAXVALUE), PARTITION p3 VALUES LESS THAN (65,MAXVALUE), PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE) );
在
VALUES LESS THAN
值列表中使用的每个值必须与相应列的类型完全匹配;不进行任何转换。例如,您不能对与使用整型类型的列匹配的值使用字符串'1'
(您必须改为使用数字1
),也不能对与使用字符串类型的列匹配的值使用数字1
(在这种情况下,您必须使用带引号的字符串:'1'
)。有关更多信息,请参见 第 26.2.1 节,“RANGE 分区” 和 第 26.4 节,“分区修剪”。
LIST(
expr
)当根据具有有限可能值的表列(如州或国家代码)分配分区时,此方法很有用。在这种情况下,所有与特定州或国家相关的行都可以分配到一个分区,或者可以为特定州或国家集保留一个分区。它类似于
RANGE
,只是只有VALUES IN
可以用来指定每个分区允许的值。VALUES IN
与要匹配的值列表一起使用。例如,您可以创建以下分区方案CREATE TABLE client_firms ( id INT, name VARCHAR(35) ) PARTITION BY LIST (id) ( PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) );
使用列表分区时,必须使用
VALUES IN
定义至少一个分区。您不能将VALUES LESS THAN
与PARTITION BY LIST
一起使用。注意对于按
LIST
分区的表,与VALUES IN
一起使用的值列表必须仅包含整型值。在 MySQL 9.0 中,您可以使用按LIST COLUMNS
分区来克服此限制,这将在本节后面介绍。LIST COLUMNS(
column_list
)此
LIST
变体便于对使用多个列的比较条件的查询进行分区修剪(即,具有WHERE a = 5 AND b = 5
或WHERE a = 1 AND b = 10 AND c = 5
等条件)。它允许您通过在COLUMNS
子句中使用列列表,并在每个PARTITION ... VALUES IN (
分区定义子句中使用一组列值来指定多个列中的值。value_list
)关于
LIST COLUMNS(
中使用的列列表和column_list
)VALUES IN(
中使用的值列表的数据类型规则与value_list
)RANGE COLUMNS(
中使用的列列表和column_list
)VALUES LESS THAN(
中使用的值列表的规则相同,只是在value_list
)VALUES IN
子句中,MAXVALUE
不允许,并且您可以使用NULL
。与
PARTITION BY LIST
一样,VALUES IN
与PARTITION BY LIST COLUMNS
一起使用的值列表之间存在一个重要区别。当与PARTITION BY LIST COLUMNS
一起使用时,VALUES IN
子句中的每个元素必须是列值的集;每个集合中的值数量必须与COLUMNS
子句中使用的列数相同,并且这些值的数据类型必须与列匹配(并且按相同顺序出现)。在最简单的情况下,集合包含单个列。column_list
和组成value_list
的元素中可以使用的最大列数为 16。以下
CREATE TABLE
语句定义的表提供了使用LIST COLUMNS
分区的表的示例CREATE TABLE lc ( a INT NULL, b INT NULL ) PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) );
PARTITIONS
num
可以选择使用
PARTITIONS
子句指定分区数量,其中num
num
是分区数量。如果同时使用此子句和任何PARTITION
子句,num
必须等于使用PARTITION
子句声明的任何分区的总数。注意无论您是否在创建按
RANGE
或LIST
分区的表时使用PARTITIONS
子句,您都必须在表定义中包含至少一个PARTITION VALUES
子句(见下文)。SUBPARTITION BY
可以选择将分区划分为多个子分区。这可以使用可选的
SUBPARTITION BY
子句来指示。子分区可以通过HASH
或KEY
完成。这两个都可以是LINEAR
。这些工作方式与之前描述的等效分区类型相同。(不能按LIST
或RANGE
进行子分区。)可以使用
SUBPARTITIONS
关键字后跟一个整数值来指示子分区数量。对
PARTITIONS
或SUBPARTITIONS
子句中使用的值的严格检查将被应用,并且此值必须符合以下规则该值必须是正的非零整数。
不允许有前导零。
该值必须是整型字面量,不能是表达式。例如,
PARTITIONS 0.2E+01
不允许,即使0.2E+01
评估为2
。(错误 #15890)
partition_definition
可以使用
partition_definition
子句单独定义每个分区。构成此子句的各个部分如下PARTITION
partition_name
指定分区的逻辑名称。
VALUES
对于范围分区,每个分区必须包含一个
VALUES LESS THAN
子句;对于列表分区,您必须为每个分区指定一个VALUES IN
子句。这用于确定将哪些行存储在此分区中。请参见 第 26 章,分区 中的分区类型讨论,以获取语法示例。[STORAGE] ENGINE
MySQL 接受
[STORAGE] ENGINE
选项,用于PARTITION
和SUBPARTITION
。目前,此选项的唯一使用方式是将所有分区或所有子分区设置为相同的存储引擎,并且尝试为同一表中的分区或子分区设置不同的存储引擎会引发错误 ERROR 1469 (HY000): 此版本的 MySQL 不允许在分区中混合处理程序。COMMENT
可以使用可选的
COMMENT
子句指定描述分区的字符串。示例COMMENT = 'Data for the years previous to 1999'
分区注释的最大长度为 1024 个字符。
DATA DIRECTORY
和INDEX DIRECTORY
DATA DIRECTORY
和INDEX DIRECTORY
可用于指示分别存储此分区数据和索引的目录。
和data_dir
都必须是绝对的系统路径名。index_dir
在
DATA DIRECTORY
子句中指定的目录必须为InnoDB
所知。有关更多信息,请参阅 使用 DATA DIRECTORY 子句。您必须具有
FILE
权限才能使用DATA DIRECTORY
或INDEX DIRECTORY
分区选项。示例
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE) PARTITION BY LIST(YEAR(adate)) ( PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx', PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx', PARTITION p2001 VALUES IN (1997, 2001, 2005) DATA DIRECTORY = '/var/appdata/97/data' INDEX DIRECTORY = '/var/appdata/97/idx', PARTITION p2002 VALUES IN (1998, 2002, 2006) DATA DIRECTORY = '/var/appdata/98/data' INDEX DIRECTORY = '/var/appdata/98/idx' );
DATA DIRECTORY
和INDEX DIRECTORY
的行为与CREATE TABLE
语句的table_option
子句中用于MyISAM
表的方式相同。每个分区可以指定一个数据目录和一个索引目录。如果未指定,则默认情况下数据和索引将存储在表的数据库目录中。
如果
NO_DIR_IN_CREATE
生效,则创建分区表时将忽略DATA DIRECTORY
和INDEX DIRECTORY
选项。MAX_ROWS
和MIN_ROWS
可用于分别指定要存储在分区中的最大和最小行数。
max_number_of_rows
和min_number_of_rows
的值必须为正整数。与具有相同名称的表级选项一样,这些选项仅作为服务器的 “建议”,而不是硬性限制。TABLESPACE
可用于通过指定
TABLESPACE `innodb_file_per_table`
来为分区指定一个InnoDB
文件每表表空间。所有分区必须属于同一个存储引擎。不支持将
InnoDB
表分区放置在共享的InnoDB
表空间中。共享表空间包括InnoDB
系统表空间和通用表空间。
subpartition_definition
分区定义可以包含一个或多个
subpartition_definition
子句(可选)。每个子句至少包含SUBPARTITION
,其中name
name
是子分区的标识符。除了将PARTITION
关键字替换为SUBPARTITION
外,子分区定义的语法与分区定义的语法相同。子分区必须通过
HASH
或KEY
进行,并且只能在RANGE
或LIST
分区上进行。请参阅 第 26.2.6 节,“子分区”。
通过生成列进行分区
允许通过生成列进行分区。例如
CREATE TABLE t1 (
s1 INT,
s2 INT AS (EXP(s1)) STORED
)
PARTITION BY LIST (s2) (
PARTITION p1 VALUES IN (1)
);
分区将生成列视为普通列,这使得能够解决对分区不允许使用的函数的限制(请参阅 第 26.6.3 节,“与函数相关的分区限制”)。前面的示例演示了此技术:EXP()
不能直接在 PARTITION BY
子句中使用,但可以使用 EXP()
定义的生成列是允许的。