「MySQL」- CREATE TABLE

  CREATED BY JENKINSBOT

主题:介绍MySQL中的CREATE TABLE的语法。

吐槽一下自己,下面是我能写出的最复杂的CREATE TABLE语句了:

CREATE TABLE DEMO(
	ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Username',
	.......
)ENGINE=INNODB DEFAULT CHARSET UTF8

语法

这个的语法功能可是很长的,远比想想中多得多,而平时用的却是基础中的基础:

语句结构概览
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

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)

[index_option] …

| {INDEX|KEY} [index_name] [index_type] (index_col_name,…)

[index_option] …

| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

[index_name] [index_type] (index_col_name,…)

[index_option] …

| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,…)

[index_option] …

| [CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (index_col_name,…) reference_definition

| CHECK (expr)

column_definition:

data_type [NOT NULL | NULL] [DEFAULT default_value]

[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

[COMMENT ‘string’]

[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]

[STORAGE {DISK|MEMORY|DEFAULT}]

[reference_definition]

| data_type [GENERATED ALWAYS] AS (expression)

[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]

[NOT NULL | NULL] [[PRIMARY] KEY]

data_type:

BIT[(length)]

| TINYINT[(length)] [UNSIGNED] [ZEROFILL]

| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

| INT[(length)] [UNSIGNED] [ZEROFILL]

| INTEGER[(length)] [UNSIGNED] [ZEROFILL]

| BIGINT[(length)] [UNSIGNED] [ZEROFILL]

| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]

| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

| DATE

| TIME[(fsp)]

| TIMESTAMP[(fsp)]

| DATETIME[(fsp)]

| YEAR

| CHAR[(length)] [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

| VARCHAR(length) [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

| BINARY[(length)]

| VARBINARY(length)

| TINYBLOB

| BLOB

| MEDIUMBLOB

| LONGBLOB

| TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

| TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

| MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

| LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

| ENUM(value1,value2,value3,…) [CHARACTER SET charset_name] [COLLATE collation_name]

| SET(value1,value2,value3,…) [CHARACTER SET charset_name] [COLLATE collation_name]

| JSON

| spatial_type

index_col_name:

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 (index_col_name,…)

[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:

ENGINE [=] engine_name

| 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 DIRECTORY [=] ‘absolute path to directory’

| DELAY_KEY_WRITE [=] {0 | 1}

| ENCRYPTION [=] {‘Y’ | ‘N’}

| INDEX DIRECTORY [=] ‘absolute path to directory’

| 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|DEFAULT}]

| 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 [=] ‘comment_text’ ]

[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 [=] ‘comment_text’ ]

[DATA DIRECTORY [=] ‘data_dir’]

[INDEX DIRECTORY [=] ‘index_dir’]

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

[TABLESPACE [=] tablespace_name]

query_expression:

SELECT … (Some valid select or union statement)

权限

CREATE TABLE时必须有CREATE权限。

默认情况下,表创建在当前数据库中,存储引擎为InnoDB。表已经存在,或者没有选择当前数据库,或者数据不存在则报错。

在硬盘中,CREATE TABLE创建的文件

MySQL通过.frm表格式(定义)文件表示每张表,默认使位于data目录的。所以.frm文件使共有的。但是存储引擎可能会创建其他文件。

对于MyISAM存储引擎
会在硬盘上创建三个文件:

tbl_name.frm 表格式定义文件。

tbl_name.MYD 数据文件

tbl_name.MYI 索引文件

对于InnoDB存储引擎
对于在独立表文件表空间中创建的InnoDB表或者一般表空间,表数据和关联的索引存储在ibd文件中。当在系统表空间中创建了InnoDB表,表数据和索引存储在表示系统表空间的ibdata* 文件中。innodb_file_per_table选项控制了在默认情况下是创建独立表文件表空间还是系统表空间。TABLESPACE选项可以无视innodb_file_per_table选项的设置,控制表存放在独立表文件表空间、一般表空间、系统表空间。

其他存储引擎
对于其他存储引擎创建的文件,查看:https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
如果表名中含有特殊字符,表文件名称会包含这些字符的编码版本,查看:https://dev.mysql.com/doc/refman/5.7/en/identifier-mapping.html

ibdata 文件
文件名为ibdata1、ibdata2…的文件集合组成了InnoDB的系统表空间。文件包含了InnoDB表的元数据、用于一个或多个undo日志的存储区域、变更缓冲、双写缓冲。他们也可以包含一些或者全部的表数据(取决于当表创建时吃否受独立表文件模式的影响)。当启用了innodb_file_per_table option选项,对于新创建的表,数据和索引被保存在独立的.ibd文件而不是系统表空间。
ibdata文件的增长受innodb_autoextend_increment选项的影响。

ibd 文件
用于独立表文件表空间一般表空间的数据文件。独立表文件表空间的.ibd文件包含了单个表和关联的索引数据。一般表空间的.ibd文件可能包含了用于多张表的表和索引数据。一般表空间在MySQL5.7.6中引入的。
.ibd文件扩展名不会应用于系统表空间,系统表空间由一个或多个ibdata文件组成。
如果一个独立表文件表空间或者一般表空间在创建的时候使用了DATA DIRECTORY = clause,.ibd文件会放在指定的路径,通常数据目录的外边,并且被一个.isl文件指向。
当.ibd文件包含在由MySQL Enterprise Backup产品压缩的备份中时,压缩等价于.ibz文件。

isl 文件
对于在MySQL 5.6中创建InnoDB表时使用了“DATA DIRECTORY =”或者在MySQL 5.7.8+中使用了“CREATE TABLESPACE … ADD DATAFILE”,该文件指明了.ibd文件的位置,它的功能有点像符号链接,没有平台限制的实际符号链接机制。您可以将InnoDB表空间存储在数据库目录之外,比如,在特别大或快速的存储设备上,具体取决于表的用途。如何在数据目录之外创建独立表文件表空间,查看:https://dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html%E3%80%82InnoDB%E7%9A%84%E4%B8%80%E8%88%AC%E8%A1%A8%E7%A9%BA%E9%97%B4%EF%BC%8C%E6%9F%A5%E7%9C%8B%EF%BC%9Ahttps://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

建表语句的存储

创建表时,原始的CREATE TABLE语句(包括所有规范和表选项)由MySQL存储。详细信息参考文档:https://dev.mysql.com/doc/refman/5.7/en/create-table-statement-retention.html

临时表

创建表的时候可以使用TEMPORARY关键字。此时会创建一个只有当前session可见的临时表,在session关闭的时候会自动删除。这就表示两个不同的session可以使用相同的表名而不会产生混乱,或者和一张已经存在的非临时表具有相同的名字(注意:在临时表删除之前,同名的非临时表是不可见的)。如果要创建临时表必须要有CREATE TEMPORARY TABLES权限。

如果使用了TEMPORARY关键字,CREATE TABLE 不会自动提交当前活动的事物。

TEMPORARY表和当前数据库的关系非常松散。删除某个数据库不会删除当前数据库下的临时表 。此外,如果使用CREATE TABLE语句中的数据库名称限定表名,则可以在不存在的数据库中创建TEMPORARY表,在这种情况下,对表的所有后续引用必须使用数据库名称进行限定。

表名

tbl_name
以db_name.tbl_name的形式指定在哪个库中创建表,当然,数据库必须是存在的。如果要引用标识符,正确的格式是`mydb`.`mytbl`,而非 `mydb.mytbl`。

关于合法的表明,查看:https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

IF NOT EXISTS
防止由于表存在而产生的错误。这个只是简单的检查表名是否存在,并不会去检查表的结构。

克隆或复制表

LIKE
使用CREATE TABLE … LIKE创建一张基于另一个表的定义的空表,包含了源表的列属性、索引定义:

CREATE TABLE new_tbl LIKE orig_tbl;

更多信息及语法,查看:https://dev.mysql.com/doc/refman/5.7/en/create-table-like.html

[AS] query_expression
从另一张表中创建一张表,在CREATE TABLE后面添加一个SELECT语句:

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

更多的信息及语法,查看:
https://dev.mysql.com/doc/refman/5.7/en/create-table-select.html

IGNORE|REPLACE
IGNORE和REPLACE选项指示在使用SELECT语句复制表时如何处理重复唯一键值的行。

更多的信息及语法,查看:https://dev.mysql.com/doc/refman/5.7/en/create-table-select.html

列数据类型和属性

每个表最多有4096个列,但对于给定表格的有效最大值可能较少,并且取决于某些因素,详情查看:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

data_type
data_type表示列定义的数据类型。spatial_type表示空间数据类型。关于数据类型的详细介绍,查看《Data Type》
有些属性并不能用于所有的数据类型。AUTO_INCREMENT只能用于浮点数和整数。 DEFAULT不能应用于BLOB,、TEXT、GEOMETRY、JSON类型。
字符数据类型 (CHAR, VARCHAR, TEXT)可以包含CHARACTER SET和COLLATE属性用于指定字符集和校对规则。详细查看:https://dev.mysql.com/doc/refman/5.7/en/charset.html CHARSET和CHARACTER SET是同义的。看Demo:

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类型的列亦是。对于非二进制类型给出的前缀长度指的是字符个数,而二进制类型的字符串指的是字节数。关于索引的更多内容,查看:《CREATE INDEX》

只有InnoDB和MyISAM存储引擎支持在BLOB和TEXT上创建索引,Demo:

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

NOT NULL | NULL
如果未指定,则默认为NULL,表示列值允许为NULL。
在MySQL 5.7中,只有InnoDB, MyISAM, MEMORY存储引擎支持在可为NULL的列上创建索引。其他的情况下,只能在非空的列上创建索引。

DEFAULT
指定列的默认值。默认值必须是常量,不能是表达式或者函数。意思是说,不能将DATE类型的列的默认值设置为诸如NOW()或CURRENT_DATE之类的函数的值。例外是可以将CURRENT_TIMESTAMP指定为TIMESTAMP或DATETIME类型的列的默认值。关于“为TIMESTAMP和DATETIME自动初始化和更新”,查看:https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

如果没有指定默认值,各个数据类型的默认值是不同的,详细查看:《Data Type》

BLOB, TEXT, JSON类型列不能设置默认值。

如果SQL mode设置了NO_ZERO_DATE或NO_ZERO_IN_DATE,并且在该模式下,如果DATA的默认值不正确,那么:如果未启用严格SQL mode,则CREATE TABLE会生成警告,如果启用了严格模式,则会产生错误。例如,启用了NO_ZERO_IN_DATE,col1 DATE DEFAULT ‘2010-00-00’会产生警告。

AUTO_INCREMENT
整数或浮点列可以具有AUTO_INCREMENT。将NULL(推荐的值)或0值插入索引的AUTO_INCREMENT列时,列被设置为下一个序列值(通常是表中当前列的最大值+1)。AUTO_INCREMENT序列从1开始。

要在插入行之后检索AUTO_INCREMENT值,使用LAST_INSERT_ID()(SQL函数)或mysql_insert_id()(C的API函数)。详情查看:https://dev.mysql.com/doc/refman/5.7/en/information-functions.html ,以及:https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html

如果SQL mode设置了NO_AUTO_VALUE_ON_ZERO,则在AUTO_INCREMENT列中可以将0存储为0,而不生成新的序列值。详情:https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

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

对于MyISAM表,可以在多列索引中指定AUTO_INCREMENT辅助列。详情:https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

要使MySQL与某些ODBC应用程序兼容,可以使用以下SELECT找到最后插入的行的AUTO_INCREMENT值:

SELECT * FROM tbl_name WHERE auto_col IS NULL

但是,此方法要求sql_auto_is_null变量未设置为0。详情查看:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

关于InnoDB和AUTO_INCREMENT的更多内容可以查看文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

AUTO_INCREMENT和MySQL Replication可以查看文档:https://dev.mysql.com/doc/refman/5.7/en/replication-features-auto-increment.html

COMMENT
通过COMMENT可以为列添加注释,最多1024个字符。可以通过SHOW CREATE TABLE和SHOW FULL COLUMNS查看注释。

COLUMN_FORMAT
在NDB集群中,也可以使用COLUMN_FORMAT为NDB表的各列指定数据存储格式。允许列格式有:FIXED,DYNAMIC和DEFAULT。FIXED用于指定固定宽度存储,DYNAMIC允许列为可变宽度,DEFAULT使列使用由列的数据类型定义的固定宽度或可变宽度来存储(可能被ROW_FORMAT说明符覆盖)。

从MySQL NDB Cluster 7.5.4开始,COLUMN_FORMAT的默认值为FIXED,MySQL NDB Cluster 7.5.1中的默认值已切换到DYNAMIC,但是这种更改已恢复,以保持与现有GA版本系列的向后兼容性。(Bug #24487363)

COLUMN_FORMAT目前对使用NDB以外的存储引擎的表的列没有影响。在MySQL 5.7+版本中,COLUMN_FORMAT被忽略。

STORAGE
对于NDB表,可以通过使用STORAGE来指定列是存储在disk上还是memory中。STORAGE DISK导致列存储在磁盘上,STORAGE MEMORY导致使用内存中的存储空间。并且使用的CREATE TABLE语句必须包含TABLESPACE。对于NDB表,STORAGE DEFAULT等同于STORAGE MEMORY。

STORAGE对使用NDB以外的存储引擎的表不起作用。STORAGE关键字仅在提供了NDB Cluster的mysqld的构建中受到支持;在任何其他版本的MySQL中都不会识别,因为任何对STORAGE关键字的使用都会导致语法错误。

GENERATED ALWAYS
用于指定生成的列表达式。相关内容查看:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_generated_column
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

存储的生成列(Stored generated columns)可以索引。 InnoDB支持在虚拟生成列(virtual generated columns)上的辅助索引。相关内容可以查看:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_stored_generated_column
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_virtual_generated_column
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

索引和外键

CONSTRAINT symbol
如果给出了CONSTRAINT symbol,如果使用了symbol,那么symbol在数据库中必须是唯一的。重复的symbol会导致错误。如果没有给出子句,或者在CONSTRAINT后没有给出symbol,则会自动创建约束的名称。

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

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

在InnoDB表中,保持PRIMARY KEY短小,可以减少辅助索引的存储开销。每个辅助索引条目包含相应行的主键列的副本。详细内容:https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

在创建的表中,建议首先设置PRIMARY KEY,然后设置所有UNIQUE索引,然后是非唯一索引。这有助于MySQL优化器优先使用哪个索引,也可以更快地检测重复的UNIQUE索引。

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

如果一个PRIMARY KEY只包含一个具有整数类型的列,那么也可以在SELECT语句中引用列为_rowid。详细内容查看文档:https://dev.mysql.com/doc/refman/5.7/en/select.html

在MySQL中,PRIMARY KEY的名称为PRIMARY。对于其他索引,如果不指定名称,索引名为第一个索引列的名称,并具有可选的后缀(_2,_3,…),以保证其唯一。可以使用SHOW INDEX FROM tbl_name查看表的索引名称。SHOW的语法详细内容可以查看文档:https://dev.mysql.com/doc/refman/5.7/en/show-index.html

KEY | INDEX
KEY通常是INDEX的同义词。在列定义中给出时,PRIMARY KEY也可以指定为KEY。这是为了和其他数据库兼容。

UNIQUE
UNIQUE索引会创建一个约束,使得索引中的所有值必须是不同的。如果要插入的行中的唯一索引列的值已经在存在于索引中,会导致错误。对于所有引擎,UNIQUE索引允许可以包含NULL的列的多个NULL值。

如果一个UNIQUE索引只包含一个具有整数类型的列,那么也可以在SELECT语句中将该列引用为_rowid。详细内容查看文档:https://dev.mysql.com/doc/refman/5.7/en/select.html

FULLTEXT
FULLTEXT索引是用于全文搜索的特殊类型的索引。只有InnoDB和MyISAM存储引擎支持FULLTEXT索引。只有CHAR,VARCHAR和TEXT列支持创建FULLTEXT。索引总是发生在整个列上;不支持列前缀索引,如果指定了,任何前缀长度都将被忽略。有关操作的详细信息,请参见:https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

如果全文索引和搜索操作需要特殊处理,则可以将WITH PARSER指定为index_option值,将解析器插件与索引相关联。此子句仅对FULLTEXT索引有效。 InnoDB和MyISAM都支持全文解析器插件。其他的全文转化插件信息,请参见文档:
https://dev.mysql.com/doc/refman/5.7/en/plugin-types.html#full-text-plugin-type
https://dev.mysql.com/doc/refman/5.7/en/writing-full-text-plugins.html

SPATIAL
可以在空间数据类型上创建SPATIAL索引。空间类型仅支持MyISAM和InnoDB表,索引列必须声明为NOT NULL。详细内容参见文档中“空间数据扩展”:https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html

CHECK
CHECK子句被解析,但被所有存储引擎忽略。详细内容查看文档中“外键差异”:https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-foreign-keys.html

FOREIGN KEY
MySQL支持外键,可让您跨表参考相关数据和外键限制,有助于保持这种扩展数据的一致性。
关于定义和选项的信息,查看:
https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-reference-definition
https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-reference-option

采用InnoDB存储引擎的分区表不支持外键。关于分区的限制,查看文档:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html

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

对于InnoDB表,前缀最多为767字节长,如果启用了innodb_large_prefix选项,那么前缀可以达到3072字节。对于MyISAM表,前缀限制为1000字节。

在CREATE TABLE,ALTER TABLE和CREATE INDEX语句中的前缀长度,如果是非二进制字符串类型(CHAR,VARCHAR,TEXT),则表示字符个数,如果是二进制字符串类型(BINARY, VARBINARY,BLOB),则表示字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时考虑到这一点。

index_type
某些存储引擎允许您在创建索引时指定索引类型。 index_type说明符的语法是USING type_name。例如:

CREATE TABLE lookup

(id INT, INDEX USING BTREE (id))

ENGINE = MEMORY;

使用的首选位置在索引列列表之后。它可以在列列表之前给出,但支持使用该位置的选项已被弃用,并将在将来的MySQL版本中被删除。

index_option
index_option的值指定了一个索引的附加选项:

KEY_BLOCK_SIZE
对于MyISAM表,KEY_BLOCK_SIZE可选地指定要用于索引key块的大小(以字节为单位)。该值被视为提示;如果需要,可以使用不同的尺寸。为单个索引定义指定的KEY_BLOCK_SIZE值将覆盖表级别的KEY_BLOCK_SIZE值。更多表级KEY_BLOCK_SIZE的信息,查看:https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-options

WITH PARSER
WITH PARSER选项只能与FULLTEXT索引一起使用。如果全文索引和搜索操作需要特殊处理,它会将解析器插件与索引相关联。InnoDB和MyISAM都支持全文解析器插件。如果一个MyISAM表包含相关全文解析器插件,则可以使用ALTER TABLE将表转换为InnoDB。

COMMENT
在MySQL 5.7中,索引定义可以包含最多1024个字符的可选注释。可以使用index_option COMMENT子句为单个索引设置InnoDB MERGE_THRESHOLD值。参见“配置索引页面的合并阈值”:https://dev.mysql.com/doc/refman/5.7/en/index-page-merge-threshold.html

有关允许的index_option值的更多信息,参见:https://dev.mysql.com/doc/refman/5.7/en/create-index.html。
有关索引的更多信息,参见:https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

reference_definition
有关reference_definition语法详细信息和示例,参见“使用FOREIGN KEY约束”:https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
有关InnoDB中外键的信息,参见“InnoDB和FOREIGN KEY约束”:https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html

InnoDB和NDB表支持检查外键约束。引用表的列必须始终被明确命名。支持对外键的ON DELETE和ON UPDATE操作。有关更多详细信息和示例,参见“使用FOREIGN KEY约束”:https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html 。有关InnoDB中外键的信息,参见“InnoDB和FOREIGN KEY约束”:https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html

对于其他存储引擎,MySQL Server解析并忽略CREATE TABLE语句中的FOREIGN KEY和REFERENCES语法。“外键差异”:https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-foreign-keys.html

注意:对于熟悉ANSI/ISO SQL标准的用户,请注意,没有存储引擎(包括InnoDB)可以识别或强制引用完整性约束定义中使用的MATCH子句。使用明确的MATCH子句将不具有指定的效果,也会导致ON DELETE和ON UPDATE子句被忽略。由于这些原因,应避免指定MATCH。 SQL标准中的MATCH子句控制在与主键进行比较时处理复合(多列)外键中的NULL值。 InnoDB基本上实现了MATCH SIMPLE定义的语义,允许外键全部或部分为空。在这种情况下,允许插入包含这样的外键的(子表)行,并且与引用(父)表中的任何行不匹配。可以使用触发器实现其他语义。 另外,MySQL要求将引用的列编入索引以实现性能。但是,InnoDB不强制执行引用的列被声明为UNIQUE或NOT NULL的任何要求。对包含NULL值的非唯一键或键的外键引用的处理对于诸如UPDATE或DELETE CASCADE的操作没有很好地定义。建议您使用仅引用UNIQUE(或PRIMARY)和NOT NULL的键的外键。 MySQL解析,但忽略“引用”作为列规范的一部分定义的“内联参考规范”(在SQL标准中定义)。仅当指定为单独的FOREIGN KEY规范的一部分时,MySQL才接受REFERENCES子句。

reference_option
有关RESTRICT,CASCADE,SET NULL,NO ACTION和SET DEFAULT选项的信息,参见“使用FOREIGN KEY约束”:https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

表选项

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

ENGINE
使用下面所示的名称指定表的存储引擎。引擎名称可以不引用或引用。引用的名称“DEFAULT”被识别但被忽略。

InnoDB:具有行锁定和外键的事务安全表。新表的默认存储引擎。关于InnoDB的信息,参见:https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html

MyISAM:主要用于只读或大部分读取工作负载的二进制便携式存储引擎。请参见第16.2节“MyISAM存储引擎”。参见:https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

MEMORY:此存储引擎的数据仅存储在内存中。参见“存储器存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

CSV:以逗号分隔的值格式存储行的表。参见“CSV存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/csv-storage-engine.html

ARCHIVE:归档存储引擎。参见“ARCHIVE存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/archive-storage-engine.html

EXAMPLE:一个示例引擎。参见“示例存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/example-storage-engine.html

FEDERATED:访问远程表的存储引擎。参见“联合存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html

HEAP:这是MEMORY的同义词。

MERGE:用作一个表的MyISAM表的集合。也称为MRG_MyISAM。参见“MERGE存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html

NDB:集群,容错,基于内存的表,支持事务和外键。又称为NDBCLUSTER。参见MySQL NDB Cluster 7.5:https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html

默认情况下,如果指定的存储引擎不可用,语句将失败并显示错误。您可以通过从服务器SQL模式中删除NO_ENGINE_SUBSTITUTION来覆盖此行为(参见“服务器SQL模式”:https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html%EF%BC%89%EF%BC%8C%E4%BB%A5%E4%BE%BFMySQL%E5%85%81%E8%AE%B8%E4%BD%BF%E7%94%A8%E9%BB%98%E8%AE%A4%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E6%9B%BF%E6%8D%A2%E6%8C%87%E5%AE%9A%E7%9A%84%E5%BC%95%E6%93%8E%E3%80%82%E9%80%9A%E5%B8%B8%E5%9C%A8%E8%BF%99%E7%A7%8D%E6%83%85%E5%86%B5%E4%B8%8B%EF%BC%8Cdefault_storage_engine%E7%B3%BB%E7%BB%9F%E5%8F%98%E9%87%8F%E7%9A%84%E9%BB%98%E8%AE%A4%E5%80%BC%E6%98%AFInnoDB%E3%80%82%E5%BD%93NO_ENGINE_SUBSTITUTION%E8%A2%AB%E7%A6%81%E7%94%A8%E6%97%B6%EF%BC%8C%E5%A6%82%E6%9E%9C%E4%B8%8D%E9%81%B5%E5%AE%88%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E8%A7%84%E8%8C%83%EF%BC%8C%E5%88%99%E4%BC%9A%E5%8F%91%E7%94%9F%E8%AD%A6%E5%91%8A。

AUTO_INCREMENT
表的初始AUTO_INCREMENT值。在MySQL 5.7中,适用于MyISAM,MEMORY,InnoDB和ARCHIVE表。要为不支持AUTO_INCREMENT的引擎设置第一个自动增量值,请在创建表之后插入一个值小于所需值的“虚拟行“,然后删除”虚拟行“。

对于支持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系统变量来减小默认指针大小。 (见“服务器系统变量”:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html%EF%BC%89%E5%A6%82%E6%9E%9C%E5%B8%8C%E6%9C%9B%E6%89%80%E6%9C%89%E7%9A%84%E8%A1%A8%E9%83%BD%E8%83%BD%E5%A4%9F%E8%B6%85%E8%BF%87%E9%BB%98%E8%AE%A4%E9%99%90%E5%88%B6%EF%BC%8C%E5%B9%B6%E4%B8%94%E6%84%BF%E6%84%8F%E8%AE%A9%E8%A1%A8%E7%9A%84%E9%80%9F%E5%BA%A6%E7%A8%8D%E5%A4%A7%E4%BA%8E%E5%BF%85%E8%A6%81%E7%9A%84%EF%BC%8C%E5%8F%AF%E4%BB%A5%E9%80%9A%E8%BF%87%E8%AE%BE%E7%BD%AE%E6%AD%A4%E5%8F%98%E9%87%8F%E6%9D%A5%E5%A2%9E%E5%8A%A0%E9%BB%98%E8%AE%A4%E7%9A%84%E6%8C%87%E9%92%88%E5%A4%A7%E5%B0%8F%E3%80%82%E5%B0%86%E5%80%BC%E8%AE%BE%E7%BD%AE%E4%B8%BA7%E5%8F%AF%E5%85%81%E8%AE%B8%E8%A1%A8%E6%A0%BC%E5%A4%A7%E5%B0%8F%E4%B8%BA65,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值。参见“配置索引页面的合并阈值”:https://dev.mysql.com/doc/refman/5.7/en/index-page-merge-threshold.html

设置NDB_TABLE选项。在MySQL NDB Cluster 7.5.2+中,CREATE TABLE或ALTER TABLE语句中的表注释也可用于指定1到4个NDB_TABLE选项NOLOGGING,READ_BACKUP,PARTITION_BALANCE或FULLY_REPLICATED作为一组name-value对,如果需要,用逗号分隔,紧跟着字符串NDB_TABLE=开始引用的注释文本。如下:

CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

c2 VARCHAR(100),

c3 VARCHAR(100) )
ENGINE=NDB

COMMENT=”NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE”;

引用的字符串中不允许使用空格。字符串不区分大小写。

注释显示为SHOW CREATE TABLE的输出的一部分。注释的文本也可以作为MySQL Information Schema TABLES表的TABLE_COMMENT列。

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

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

有关完整的语法信息和示例,参见“在表注释中设置NDB_TABLE选项”:https://dev.mysql.com/doc/refman/5.7/en/create-table-ndb-table-comment-options.html

COMPRESSION
用于InnoDB表的页面级压缩的压缩算法。支持的值:Zlib,LZ4,None。 COMPRESSION属性和透明页面压缩功能一起被引入。页面压缩仅支持驻留在独立表文件表空间中的InnoDB表,并且仅在支持稀疏文件和hole punching的Linux和Windows平台上可用。有关更多信息,参见“InnoDB页面压缩”:https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html

CONNECTION
FEDERATED表的连接字符串。注意:旧版本的MySQL对连接字符串使用COMMENT选项。

DATA DIRECTORY, INDEX DIRECTORY
对于InnoDB,DATA DIRECTORY =’directory’选项允许在MySQL数据目录之外创建InnoDB独立表文件表空间。在指定的目录中,MySQL会创建一个对应于数据库名称的子目录,并在其中保存表的.ibd文件。必须启用innodb_file_per_table配置选项才能使用InnoDB的DATA DIRECTORY选项。必须指定完整的目录路径。参见第“在数据目录之外创建文件每表表空间”:https://dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html

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

从MySQL 5.7.17开始,您必须具有FILE权限才能使用DATA DIRECTORY或INDEX DIRECTORY表选项。

注意:对于分区表,表级DATA DIRECTORY和INDEX DIRECTORY选项将被忽略。(bug#32091)

只有当不使用–skip-symbolic-links选项时,这些选项才可用。操作系统也必须有一个工作,线程安全的realpath()调用。有关更完整的信息,参见“在Unix上使用MyISAM表的符号链接”:https://dev.mysql.com/doc/refman/5.7/en/symbolic-links-to-tables.html

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

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

注意:与DATA DIRECTORY或INDEX DIRECTORY一起,不能使用包含MySQL数据目录的路径名。这包括分区表和单独的表分区。(bug#32167)

DELAY_KEY_WRITE
如果要延迟表的key更新到表关闭,请将其设置为1。参见“服务器系统变量”中的delay_key_write系统变量的描述:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html。 (仅限MyISAM)

ENCRYPTION
将ENCRYPTION选项设置为“Y”,以便对独立表文件表空间中创建的InnoDB表启用页面级数据加密。选项值不区分大小写。 ENCRYPTION选项伴随InnoDB表空间加密功能被引入;参见“InnoDB表空间加密”:https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html 。必须加载keyring_file插件以使用ENCRYPTION选项。

INSERT_METHOD
如果要将数据插入到MERGE表中,则必须使用INSERT_METHOD指定要插被入行的表。 INSERT_METHOD仅适用于MERGE表。使用FIRST或LAST的值将插入到第一个或最后一个表,或者使用值NO来防止插入。参见“MERGE存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html

KEY_BLOCK_SIZE
对于MyISAM表,KEY_BLOCK_SIZE可选地指定要用于索引key块的大小(以字节为单位)。该值被视为提示;如果需要,可以使用不同的尺寸。为单个索引定义指定的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。有关详细信息,参见第“InnoDB表压缩”:https://dev.mysql.com/doc/refman/5.7/en/innodb-table-compression.html

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

InnoDB仅支持表级别的KEY_BLOCK_SIZE。

32k和64k的innodb_page_size值不支持KEY_BLOCK_SIZE。 InnoDB表压缩不支持这些页面大小。

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

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

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

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

PACK_KEYS
仅对MyISAM表有影响。如果要缩小索引,请将此选项设置为1。这通常会使更新速度更慢,读取速度更快。将选项设置为0将禁用所有key的打包。将其设置为DEFAULT告诉存储引擎只打包长CHAR,VARCHAR,BINARY或VARBINARY列。

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

当打包二进制数字键时,MySQL使用前缀压缩: 1). 每个键需要一个额外的字节来指示上一个键的多少字节对于下一个键是相同的。 2). 指向行的指针在key之后直接以高字节优先顺序存储,以提高压缩

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

PASSWORD
此选项未使用。如果您需要争夺您的.frm文件,并使其无法使用任何其他MySQL服务器,请联系我们的销售部门。

ROW_FORMAT
定义存储行的物理格式。
执行CREATE TABLE语句时,如果指定了存储引擎不支持的ROW_FORMAT,则使用存储引擎的默认行格式来创建该表。SHOW TABLE STATUS报告的信息是实际使用的行格式。这可能与Create_options列中的值不同,因为在创建过程中保留原始的CREATE TABLE定义。

行格式选项因表使用的存储引擎而异:

对于InnoDB表:
1). 默认行格式由innodb_default_row_format定义,其默认设置为DYNAMIC。当未定义ROW_FORMAT选项或使用ROW_FORMAT = DEFAULT时,将使用默认行格式。如果未定义ROW_FORMAT选项,或者如果使用ROW_FORMAT=DEFAULT,那么重建表的操作也会将表的行格式默认地更改为由innodb_default_row_format定义的默认值。有关详细信息,参见“为表指定行格式”:https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html
2). 为了更有效的InnoDB存储数据类型,特别是BLOB类型,请使用DYNAMIC。有关与DYNAMIC行格式相关的要求,参见”动态和压缩行格式”:https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html
3). 要启用InnoDB表的压缩,请指定ROW_FORMAT = COMPRESSED。有关与COMPRESSED行格式相关的要求,参见“InnoDB表和页面压缩”:https://dev.mysql.com/doc/refman/5.7/en/innodb-compression.html
4). 仍然可以通过指定REDUNDANT行格式来请求旧版本的MySQL中使用的行格式。
5). 指定非默认ROW_FORMAT子句时,请考虑启用innodb_strict_mode配置选项。
6). 不支持ROW_FORMAT = FIXED。如果在禁用innodb_strict_mode时指定了ROW_FORMAT = FIXED,InnoDB将发出警告,并假定ROW_FORMAT = DYNAMIC。如果在启用innodb_strict_mode时指定了ROW_FORMAT = FIXED,则默认情况下,InnoDB返回错误。
7). 有关InnoDB行格式的其他信息,参见“InnoDB行存储和行格式”:https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

对于MyISAM表:
对于静态或可变长度的行格式,选项值可以是FIXED或DYNAMIC。 myisampack将类型设置为COMPRESSED。参见“MyISAM表存储格式”:https://dev.mysql.com/doc/refman/5.7/en/myisam-table-formats.html

对于NDB表,MySQL NDB Cluster 7.5.1及更高版本中的默认ROW_FORMAT为DYNAMIC。 (以前,它是固定的。)

STATS_AUTO_RECALC
指定是否自动重新计算InnoDB表的持久统计信息。值DEFAULT导致表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定。当表中的10%的数据已更改时,值1会导致统计信息重新计算。值0可防止对此表进行自动重新计算;使用此设置,发出ANALYZE TABLE语句以便在对表进行实质性更改后重新计算统计信息。有关持久性统计特性的更多信息,参见“配置持久优化器统计参数”:https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

STATS_PERSISTENT
指定是否启用InnoDB表的持久统计信息。值DEFAULT导致表的持久统计信息设置由innodb_stats_persistent配置选项确定。值1启用表的持久统计,而值0关闭此功能。通过CREATE TABLE或ALTER TABLE语句启用持久性统计信息之后,在将代表性数据加载到表中之后,发出ANALYZE TABLE语句来计算统计信息。有关持久性统计特性的更多信息,参见“配置持久优化器统计参数”:https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

STATS_SAMPLE_PAGES
估计索引列的基数和其他统计信息的索引页数,例如由ANALYZE TABLE计算的索引页数。有关详细信息,参见“配置持久优化器统计参数”:https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

UNION
用于访问相同的MyISAM表的集合作为一个。这仅适用于MERGE表。参见“MERGE存储引擎”:https://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html

您必须对映射到MERGE表的表具有SELECT,UPDATE和DELETE权限。

注意:以前所有使用的表必须和MERGE表本身在同一数据库中。此限制不再适用。

TABLESPACE
TABLESPACE选项用于在InnoDB一般表空间中创建一个表。

CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name

使用TABLESPACE选项之前,必须存在指定的一般表空间。有关一般表空间的信息,参见“InnoDB常规表空间”:https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

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

TABLESPACE选项可用于将InnoDB表分区或子分区分配给一般表空间,单独的独立表文件表空间或系统表空间。 MySQL 5.7中支持表分区和子分区的TABLESPACE选项。所有分区必须具有相同存储引擎。

表级指定的表空间将成为新分区和子分区的默认表空间。可以通过在CREATE TABLE或ALTER TABLE语句中的分区或子分区级别指定表空间来覆盖默认表空间。以下示例显示了在表级别和分区级别定义的表空间:

mysql> CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))

-> ENGINE=InnoDB TABLESPACE ts1
-> PARTITION BY RANGE (a) PARTITIONS 3 (
-> PARTITION P1 VALUES LESS THAN (2),

-> PARTITION P2 VALUES LESS THAN (4) TABLESPACE ts2,

-> PARTITION P3 VALUES LESS THAN (6) TABLESPACE ts3);

有关TABLESPACE选项和分区的更多信息,参见“InnoDB常规表空间”:https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

要在系统表空间中创建表,请指定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选项结合使用。

ALTER TABLE和ALTER TABLE … REORGANIZE PARTITION语句支持TABLESPACE选项,可用于将表和分区从一个表空间分别移动到另一个表空间。有关更多信息,参见“InnoDB常规表空间”:https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

STORAGE表选项仅用于NDB表。 STORAGE确定使用的存储器类型(磁盘或内存),并且可以是DISK,MEMORY或DEFAULT之一。

TABLESPACE … STORAGE DISK将表分配给NDB Cluster Disk Data表空间。表空间必须已经使用CREATE TABLESPACE创建。有关详细信息,参见“NDB群集磁盘数据表”:https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-disk-data.html

注意:无法在没有TABLESPACE子句的CREATE TABLE语句中使用STORAGE子句。

创建分区表

partition_options

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

并非在本节开头的partition_options语法中显示的所有选项都可用于所有分区类型。请参阅以下各种类型的列表,了解每种类型的特定信息,有关MySQL中分区的使用和使用的更完整信息,参见文档“分区”:https://dev.mysql.com/doc/refman/5.7/en/partitioning.html ,以及有关表创建和其他相关陈述的其他示例到MySQL分区。

可以将分区修改,合并,添加到表中,并从表中删除。有关完成这些任务的MySQL语句的基本信息,参见“ALTER TABLE语法”:https://dev.mysql.com/doc/refman/5.7/en/alter-table.html%E3%80%82%E6%9C%89%E5%85%B3%E6%9B%B4%E8%AF%A6%E7%BB%86%E7%9A%84%E8%AF%B4%E6%98%8E%E5%92%8C%E7%A4%BA%E4%BE%8B%EF%BC%8C%E5%8F%82%E8%A7%81%E2%80%9C%E5%88%86%E5%8C%BA%E7%AE%A1%E7%90%86%E2%80%9D%EF%BC%9Ahttps://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html

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

在PARTITION BY子句中使用的表达式(expr)不能引用正在创建的表中的任何列;这样的引用是特别不允许的,并导致语句失败并出现错误。(bug#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的余数除以分区数(即模数)。有关示例和其他信息,参见HASH分区”:https://dev.mysql.com/doc/refman/5.7/en/partitioning-hash.html

LINEAR关键字需要一些不同的算法。在这种情况下,作为一个或多个逻辑AND运算的结果计算存储行的分区的编号。有关线性散列的讨论和示例,参见“线性哈希分区”:https://dev.mysql.com/doc/refman/5.7/en/partitioning-linear-hash.html

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分区的表具有相同的效果。也就是说,使用&操作符而不是模数找到分区号,有关详细信息,参见“线性哈希分区”和“关键分区”:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-linear-hash.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html

此示例使用键的线性分区在5个分区之间分配数据:

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

PARTITIONS 5;

使用MySQL 5.7.1开始的
[SUB]PARTITION BY [LINEAR] KEY支持
ALGORITHM = {1|2}选项。 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分区的表服务器。有关详细信息,请参见第14.1.8.1节“ALTER TABLE分区操作”。

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

CREATE TABLE t1 (a INT)


/*!50100 PARTITION BY KEY */ /*!50611 ALGORITHM = 1 */ /*!50100 ()


PARTITIONS 3 */

这导致MySQL 5.6.10和更早版本的服务器忽略该选项,否则会导致这些版本中的语法错误。如果您打算加载在MySQL 5.7服务器上进行的转储,您将使用由KEY分区或分区的表转移到5.6.11之前的MySQL 5.6服务器中,请务必先查看影响升级到MySQL 5.6的更改,然后再继续。 (如果您将从MySQL 5.7实际5.6.11或更高版本的服务器生成的KEY分区表或子分区表加载到MySQL 5.5.30或更早版本的服务器中,那么也会发现这些信息。)

同样在MySQL 5.6.11及更高版本中,在SHOW CREATE TABLE的输出中必要时显示ALGORITHM = 1,使用与mysqldump相同的版本注释。即使在创建原始表时指定了此选项,SHOW CREATE TABLE输出也总是忽略ALGORITHM = 2。

不得使用具有PARTITION BY KEY的VALUES LESS THAN或VALUES IN子句。

RANGE(expr)
在这种情况下,expr使用一组值不超过运算符来显示一系列值。使用范围分区时,必须使用VALUES LESS THAN至少定义一个分区。您不能使用VALUES IN进行范围分区。

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

假设您有一个表,您希望在包含年度值的列上分区,根据以下方案:

Partition Number: Years Range:
0 1990 and earlier

1 1991 to 1994

2 1995 to 1998

3 1999 to 2002

4 2003 to 2005

5 2006 and later

实现这种分区方案的表可以通过下面的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块的情况部分的顺序工作(如C,Java和PHP等许多编程语言中所发现的)。也就是说,这些条款必须以每个连续值不超过上限值的上限大于上一个值的方式进行排列,其中一个参考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子句中的值导致语句失败,并且在使用分区列列表时出现错误不一致。您不能对任何出现的值使用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’)。

有关更多信息,请参见“范围分区”和“分区修剪”:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html

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至少定义一个分区。您不能按分类列表使用不超过值。

注意:对于由LIST分区的表,与VALUES IN一起使用的值列表只能由整数值组成。在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子句中的列列表和每个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一起使用。当与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”子句中使用的值进行严格检查,此值必须遵守以下规则:

  • 该值必须为正,非零整数。
  • 不允许使用前导零。
  • 该值必须是一个整数文字,不能是一个表达式。例如,即使0.2E + 01评估为2,也不允许使用分区0.2E + 01(bug#15890)

partition_definition

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

PARTITION partition_name

指定分区的逻辑名称。

VALUES

对于范围分区,每个分区必须包含一个VALUES LESS THAN子句;对于列表分区,必须为每个分区指定VALUES IN子句。这用于确定在该分区中存储哪些行。有关语法示例,参阅“分区”中的“分区类型”的讨论:https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

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

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

COMMENT = ‘Data for the years previous to 1999’

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

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

从MySQL 5.7.17开始,您必须具有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的行为方式与在MyISAM表中使用的CREATE TABLE语句的table_option子句相同。

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

在Windows上,MyISAM表的各个分区或子分区不支持DATA DIRECTORY和INDEX DIRECTORY选项,INDo DIRECTORY选项不适用于各个分区或InnoDB表的子部分。这些选项在Windows中被忽略,但生成警告除外。 (bug#30459)

注意:如果NO_DIR_IN_CREATE有效,则创建分区表将忽略DATA DIRECTORY和INDEX DIRECTORY选项。 (bug#24633)

MAX_ROWS and MIN_ROWS
可以分别指定要存储在分区中的最大和最小行数。 max_number_of_rows和min_number_of_rows的值必须为正整数。与具有相同名称的表级别选项一样,它们仅作为服务器的“建议”,并不是硬限制。

TABLESPACE
可用于将InnoDB表分区或子分区分配给一般表空间,单独的每表表空间或系统表空间。 MySQL 5.7中添加了表分区和子分区的TABLESPACE选项支持,参见“InnoDB常规表空间”:https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html 。它也得到NDB集群的支持。所有分区必须属于同一存储引擎。

subpartition_definition

分区定义可以可选地包含一个或多个子分区定义子句。它们中的每一个至少包含SUBPARTITION名称,其中name是子分区的标识符。除了使用SUBPARTITION替换PARTITION关键字,子分区定义的语法与分区定义的语法相同。

子分区必须由HASH或KEY完成,只能在RANGE或LIST分区上完成。参见第21.2.6节“分组”。

生成列分区

允许通过生成列进行分区。例如:

CREATE TABLE t1 (

s1 INT,

s2 INT AS (EXP(s1)) STORED

)

PARTITION BY LIST (s2) (

PARTITION p1 VALUES IN (1)

);

分区将生成的列视为常规列,这对于不允许进行分区的功能的限制实现了解决方法(参见第21.6.3节“与功能分区限制”)。前面的例子演示了这种技术:EXP()不能直接在PARTITION BY子句中使用,但允许使用EXP()定义的生成列。

参考文献

https://dev.mysql.com/doc/refman/5.7/en/create-table.html