CREATE TABLE语句
CREATE [TEMPORARY ]TABLE [IF NOTEXISTS ] tbl_name (create_definition,...) [table_options] [partition_options]CREATE [TEMPORARY ]TABLE [IF NOTEXISTS ] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE |REPLACE ] [AS ] query_expressionCREATE [TEMPORARY ]TABLE [IF NOTEXISTS ] 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)} ] [AUTO_INCREMENT ] [UNIQUE [KEY ]] [[PRIMARY ]KEY ] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED |DYNAMIC |DEFAULT }] [STORAGE {DISK |MEMORY }] [reference_definition] [check_constraint_definition] | data_type [COLLATE collation_name] [GENERATED ALWAYS ]AS (expr) [VIRTUAL |STORED ] [NOT NULL | NULL] [UNIQUE [KEY ]] [[PRIMARY ]KEY ] [COMMENT 'string'] [reference_definition] [check_constraint_definition] data_type: (see Chapter 11,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 } check_constraint_definition: [CONSTRAINT [symbol]]CHECK (expr) [[NOT]ENFORCED ] reference_definition:REFERENCES tbl_name (key_part,...) [MATCHFULL | MATCHPARTIAL | MATCHSIMPLE ] [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 validselect orunion statement)
CREATE TABLE
创建具有给定名称的表。您必须具有CREATE
该表的特权。
默认情况下,表是使用InnoDB
存储引擎在默认数据库中创建的。如果该表存在,没有默认数据库或该数据库不存在,则会发生错误。
MySQL对表的数量没有限制。基础文件系统可能会对表示表的文件数量有所限制。各个存储引擎可能会强加特定于引擎的约束。InnoDB
最多允许40亿张桌子。
有关表的物理表示的信息,请参见“CREATE TABLE语句创建文件”。
CREATE TABLE
创建表时,原始语句(包括所有规范和表选项)由MySQL存储。有关更多信息,请参见“ CREATE TABLE语句保留”。
该CREATE TABLE
语句有多个方面,本节以下主题对此进行了描述:
- 表名
- 临时表
- 表克隆和复制
- 列数据类型和属性
- 索引,外键和检查约束
- 表格选项
- 表分区
表名
tbl_name
可以将表名指定为
db_name.tbl_name
在特定数据库中创建表。不管是否存在默认数据库(假定数据库存在),此方法都有效。如果使用带引号的标识符,请分别为数据库和表名加上引号。例如,写`mydb`.`mytbl`
而不是`mydb.mytbl`
。“架构对象名称”中给出了允许的表名称的规则。
IF NOT EXISTS
如果表存在,则防止发生错误。但是,没有验证现有表的结构与该
CREATE TABLE
语句指示的结构相同。
临时表
您可以TEMPORARY
在创建表时使用关键字。一个TEMPORARY
表仅在当前会话中可见,并且在关闭会话时会自动删除。有关更多信息,请参见“ CREATE TEMPORARY TABLE语句”。
表克隆和复制
LIKE
用于
CREATE TABLE ... LIKE
根据另一个表的定义创建一个空表,包括在原始表中定义的任何列属性和索引:CREATE TABLE new_tbl LIKE orig_tbl;有关更多信息,请参见“ CREATE TABLE ... LIKE语句”。
[AS]query_expression
要从另一个表创建一个表,请在
SELECT
语句末尾添加一条CREATE TABLE
语句:CREATE TABLE new_tblAS SELECT *FROM orig_tbl;有关更多信息,请参见“ CREATE TABLE ... SELECT语句”。
IGNORE|REPLACE
在
IGNORE
和REPLACE
选项指示如何处理使用复制表时复制唯一键值的行SELECT
声明。有关更多信息,请参见“ CREATE TABLE ... SELECT语句”。
列数据类型和属性
每个表都有4096列的硬限制,但是对于给定的表,有效最大值可能会更少,并且取决于“表的列数和行大小的限制”中讨论的因素。
data_type
data_type
表示列定义中的数据类型。有关可用于指定列数据类型的语法的完整说明,以及有关每种类型的属性的信息,请参见数据类型。- 某些属性并不适用于所有数据类型。
AUTO_INCREMENT
仅适用于整数和浮点类型。在此之前的MySQL 8.0.13,DEFAULT
并不适用于BLOB
,TEXT
,GEOMETRY
,和JSON
类型。 字符数据类型(
CHAR
,VARCHAR
,的TEXT
类型,ENUM
,SET
,和任何同义词)可以包括CHARACTER SET
指定该列的字符集。CHARSET
是的同义词CHARACTER SET
。可以使用COLLATE
属性以及任何其他属性来指定字符集的排序规则。有关详细信息,请参见字符集,归类和Unicode。例:CREATE TABLE t (c CHAR(20)CHARACTER SET utf8COLLATE utf8_bin);MySQL 8.0在字符的字符列定义中解释长度规范。对于长度
BINARY
和VARBINARY
是字节。为
CHAR
,VARCHAR
,BINARY
,和VARBINARY
列,索引可以创建仅使用列值的前导部分,使用语法来指定一个索引前缀长度。和列也可以建立索引,但是必须给出前缀长度。对于非二进制字符串类型,前缀长度以字符形式给出;对于二进制字符串类型,前缀长度以字节形式给出。即,索引项由所述第一的每个列的值的字符,和col_name(length)
BLOB
TEXT
length
CHAR
VARCHAR
TEXT
列,并且所述第一length
对每个列的值的字节BINARY
,VARBINARY
和BLOB
列。像这样仅索引列值的前缀可以使索引文件更小。有关索引前缀的更多信息,请参见“ 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 8.0中,只有
InnoDB
,MyISAM
和MEMORY
存储引擎支持在可以有列的索引NULL
值。在其他情况下,必须将索引列声明为,NOT NULL
否则将导致错误结果。DEFAULT
指定列的默认值。有关默认值处理的更多信息,包括列定义不包含显式
DEFAULT
值的情况,请参见“数据类型默认值”。如果启用了
NO_ZERO_DATE
或NO_ZERO_IN_DATE
SQL模式,并且根据该模式使用的日期值默认值不正确,CREATE TABLE
则在未启用严格SQL模式的情况下产生警告,而在启用严格模式的情况下产生错误。例如,NO_ZERO_IN_DATE
启用后c1 DATE DEFAULT '2010-00-00'
会产生警告。AUTO_INCREMENT
整数或浮点列可以具有Additional属性
AUTO_INCREMENT
。当您将NULL
(推荐)值或0
插入索引AUTO_INCREMENT
列时,该列将设置为下一个序列值。通常,这是value+1
,其中value
是表中当前列的最大值。AUTO_INCREMENT
序列以开头1
。要
AUTO_INCREMENT
在插入行后检索值,请使用LAST_INSERT_ID()
SQL函数或mysql_insert_id()
C API函数。请参见“信息函数”和“ mysql_insert_id()”。如果
NO_AUTO_VALUE_ON_ZERO
启用了SQL模式下,可以存储0
在AUTO_INCREMENT
列,0
而不会产生一个新的序列值。请参见“服务器SQL模式”。AUTO_INCREMENT
每个表只能有一个列,必须对其进行索引,并且不能有DEFAULT
值。一AUTO_INCREMENT
列正常工作,只有当它仅包含正值。插入负数被视为插入非常大的正数。这样做是为了避免数字从正数“换”为负数时出现精度问题,并确保您不会意外得到AUTO_INCREMENT
包含的列0
。对于
MyISAM
表,您可以AUTO_INCREMENT
在多列键中指定第二列。请参见“使用AUTO_INCREMENT”。为了使MySQL与某些ODBC应用程序兼容,可以
AUTO_INCREMENT
使用以下查询找到最后插入的行的值:SELECT *FROM tbl_nameWHERE auto_col IS NULL此方法要求
sql_auto_is_null
变量未设置为0。请参见“服务器系统变量”。有关信息
InnoDB
,并AUTO_INCREMENT
请参见第15.6.1.6,“AUTO_INCREMENT处理InnoDB中”。有关AUTO_INCREMENT
MySQL复制的信息,请参见“InnoDB中的AUTO_INCREMENT处理”。COMMENT
可以使用
COMMENT
选项指定列的注释,最长为1024个字符。注释由SHOW CREATE TABLE
和SHOW FULL COLUMNS
语句显示。COLUMN_FORMAT
在NDB Cluster中,还可以使用来为
NDB
表的各个列指定数据存储格式COLUMN_FORMAT
。允许列格式FIXED
,DYNAMIC
以及DEFAULT
。FIXED
用于指定固定宽度存储,DYNAMIC
允许DEFAULT
列为可变宽度,并导致列使用由列的数据类型确定的固定宽度或可变宽度存储(可能被ROW_FORMAT
说明符覆盖)。对于
NDB
表,默认值COLUMN_FORMAT
是FIXED
。在NDB群集中,用定义的列的最大可能偏移为
COLUMN_FORMAT=FIXED
8188字节。有关更多信息和可能的解决方法,请参见“NDB群集中与数据库对象相关联的限制”。COLUMN_FORMAT
目前对使用以外的存储引擎的表的列没有影响NDB
。MySQL 8.0默默忽略COLUMN_FORMAT
。STORAGE
对于
NDB
表,可以使用STORAGE
子句指定列是存储在磁盘上还是存储在内存中。STORAGE DISK
使该列存储在磁盘上,并STORAGE MEMORY
导致使用内存中存储。使用的CREATE TABLE
语句必须仍然包含一个TABLESPACE
子句:mysql>
CREATE TABLE t1 ( -> c1 INTSTORAGE DISK , -> c2 INTSTORAGE MEMORY -> )ENGINE NDB ; ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140) mysql>CREATE TABLE t1 ( -> c1 INTSTORAGE DISK , -> c2 INTSTORAGE MEMORY -> )TABLESPACE ts_1ENGINE NDB ; Query OK, 0 rows affected (1.06 sec)对于
NDB
表格,STORAGE DEFAULT
相当于STORAGE MEMORY
。该
STORAGE
子句对使用除以外的存储引擎的表无效NDB
。该STORAGE
关键字仅在NDB Cluster随附的mysqld构建中受支持。在任何其他版本的MySQL中,该STORAGE
关键字均无法识别,因为尝试使用该关键字会导致语法错误。GENERATED ALWAYS
用于指定生成的列表达式。有关生成的列的信息,请参见“创建表和生成的列”。
存储的生成列可以被索引。
InnoDB
支持虚拟生成的列上的二级索引。请参见“二级索引和生成的列”。
索引,外键和检查约束
有几个关键字适用于创建索引,外键和CHECK
约束。有关以下描述的一般背景,请参见“ CREATE INDEX语句”,“外键约束”和“ CHECK约束”。
CONSTRAINT symbol
可以给该子句命名以约束。如果没有给出该子句,或者在关键字之后不包含a ,则MySQL将自动生成约束名称,但以下情况除外。如果使用该值,则每个模式(数据库),每个约束类型都必须是唯一的。重复会导致错误。另请参见“标识符长度限制”中有关生成的约束标识符的长度限制的讨论。
CONSTRAINT symbol
symbol
CONSTRAINT
symbol
symbol
注意
如果没有在外键定义中提供该子句,或者在关键字之后不包含a ,则MySQL使用外键索引名称,直到MySQL 8.0.15,然后自动生成约束名称。
CONSTRAINT symbol
symbol
CONSTRAINT
SQL标准指定所有类型的约束(主键,唯一索引,外键,检查)都属于同一名称空间。在MySQL中,每个约束类型在每个模式都有自己的名称空间。因此,每种约束类型的名称在每个架构中必须唯一,但是不同类型的约束可以具有相同的名称。
PRIMARY KEY
唯一索引,其中所有键列必须定义为
NOT NULL
。如果未将其显式声明为NOT NULL
,则MySQL会如此隐式(无声地)声明它们。一个表只能有一个PRIMARY KEY
。a的名称PRIMARY KEY
始终为PRIMARY
,因此不能用作任何其他种类的索引的名称。如果没有,
PRIMARY KEY
而应用程序PRIMARY KEY
在表中要求,则MySQL返回第一个UNIQUE
没有NULL
列的索引作为PRIMARY KEY
。在
InnoDB
表中,请保持PRIMARY KEY
简短,以最大程度地减少辅助索引的存储开销。每个辅助索引条目都包含对应行的主键列的副本。(请参见“聚集索引和二级索引”。)在创建的表中,
PRIMARY KEY
首先放置a ,然后放置所有UNIQUE
索引,然后放置非唯一索引。这可以帮助MySQL优化器确定要使用的索引的优先级,还可以更快地检测重复的UNIQUE
键。A
PRIMARY KEY
可以是多列索引。但是,您不能使用PRIMARY KEY
列规范中的key属性创建多列索引。这样做只会将该单列标记为主要列。您必须使用单独的子句。PRIMARY KEY(key_part,...)
如果表的
PRIMARY KEY
或UNIQUE NOT NULL
索引由具有整数类型的单个列组成,则可以_rowid
用来引用SELECT
语句中的索引列,如Unique Indexes中所述。在MySQL中,a的名称
PRIMARY KEY
为PRIMARY
。对于其他指标,如果不指定一个名称,该指数被分配相同的名称作为第一个索引列,有一个可选的后缀(_2
,_3
,...
),以使其独特。您可以使用来参见表的索引名称。请参见“ SHOW INDEX语句”。SHOW INDEX FROM tbl_name
KEY | INDEX
KEY
通常是的同义词INDEX
。PRIMARY KEY
还可以像KEY
在列定义中给定键属性一样指定键属性。这样做是为了与其他数据库系统兼容。UNIQUE
一个
UNIQUE
索引创建的约束,使得该指数的所有值必须是不同的。如果您尝试添加键值与现有行匹配的新行,则会发生错误。对于所有引擎,UNIQUE
索引允许NULL
包含的列具有多个值NULL
。如果为UNIQUE
索引中的列指定前缀值,则列值在前缀长度内必须唯一。如果表的
PRIMARY KEY
或UNIQUE NOT NULL
索引由具有整数类型的单个列组成,则可以_rowid
用来引用SELECT
语句中的索引列,如Unique Indexes中所述。FULLTEXT
一个
FULLTEXT
指标是用于全文搜索索引的一种特殊类型。仅InnoDB
和MyISAM
存储引擎支持FULLTEXT
索引。他们只能从创建CHAR
,VARCHAR
和TEXT
列。索引总是在整个列上进行;不支持列前缀索引,并且如果指定,则将忽略任何前缀长度。有关操作的详细信息,请参见“全文搜索功能”。甲WITH PARSER
子句可以被指定为一个index_option
如果全文索引和搜索操作需要特殊处理,则将解析器插件与索引相关联的值。此子句仅对FULLTEXT
索引有效。InnoDB
并MyISAM
支持全文分析器插件。有关更多信息,请参见全文分析器插件和“编写全文分析器插件”。SPATIAL
您可以
SPATIAL
在空间数据类型上创建索引。只有InnoDB
和MyISAM
表格才支持空间类型,索引列必须声明为NOT NULL
。请参见“空间数据类型”。FOREIGN KEY
MySQL支持外键和外键约束,这些外键使您可以跨表交叉引用相关数据,外键约束则可以使这些扩展数据保持一致。有关定义和选项的信息,请参见
reference_definition
和reference_option
。使用
InnoDB
存储引擎的分区表不支持外键。有关更多信息,请参见“分区限制”。CHECK
该
CHECK
子句允许检查约束的创建,以检查表行中的数据值。请参见“检查约束”。key_part
key_part
规格可以与结束ASC
或DESC
以指定的索引值是否被存储在升序或降序排序。如果未指定订单说明符,则默认值为升序。由
length
属性定义的前缀,对于InnoDB
使用REDUNDANT
或COMPACT
行格式的表,最长可以为767个字节。对于InnoDB
使用DYNAMIC
或COMPRESSED
行格式的表,前缀长度限制为3072字节。对于MyISAM
表,前缀长度限制为1000个字节。前缀限制以字节为单位。然而,前缀长度为索引规范
CREATE TABLE
,ALTER TABLE
和CREATE INDEX
语句解释为非二进制串类型的字符数(CHAR
,VARCHAR
,TEXT
对于二进制串类型),并且字节数(BINARY
,VARBINARY
,BLOB
)。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。- 与MySQL 8.0.17开始,
expr
用于key_part
规范可以采取的形式来创建上的一个多值索引列。多值索引,提供有关多值索引的创建,使用以及限制和限制的详细信息。(CAST json_path AS type 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
表带有关联的全文分析器插件,则可以将该表转换为InnoDB
usingALTER TABLE
。COMMENT
在MySQL 8.0中,索引定义可以包含最多1024个字符的可选注释。
您可以
InnoDB
MERGE_THRESHOLD
使用该index_option
COMMENT
子句为单个索引设置值。请参见“为索引页配置合并阈值”。
有关允许
index_option
值的更多信息,请参见“ CREATE INDEX语句”。有关索引的更多信息,请参见“ MySQL如何使用索引”。reference_definition
有关
reference_definition
语法的详细信息和示例,请参见“外键约束”。InnoDB
和NDB
表支持检查外键约束。被引用表的列必须始终明确命名。无论ON DELETE
和ON UPDATE
外键的行动的支持。有关更多详细信息和示例,请参见“外键约束”。对于其他存储引擎,MySQL Server解析并忽略语句中的
FOREIGN KEY
andREFERENCES
语法CREATE TABLE
。请参见“外键约束差异”。重要
对于熟悉ANSI / ISO SQL标准的用户,请注意,包括在内的任何存储引擎都不会
InnoDB
识别或强制执行MATCH
引用完整性约束定义中使用的子句。使用显式MATCH
子句将不会具有指定的效果,并且还会导致ON DELETE
和ON UPDATE
子句被忽略。由于这些原因,MATCH
应避免指定。MATCH
SQL标准中的子句控制NULL
与主键进行比较时如何处理复合(多列)外键中的值。InnoDB
本质上实现了定义的语义MATCH SIMPLE
,允许外键全部或部分地NULL
。在这种情况下,允许插入包含此类外键的(子表)行,并且该行与引用的(父)表中的任何行都不匹配。使用触发器可以实现其他语义。另外,MySQL要求对引用的列进行索引以提高性能。但是,
InnoDB
不强制要求将引用的列声明为UNIQUE
或NOT NULL
。NULL
对于诸如UPDATE
或的操作,未很好地定义对非唯一键或包含值的键的外键引用的处理DELETE CASCADE
。建议您使用仅引用同时为UNIQUE
(或PRIMARY
)和的键的外键NOT NULL
。MySQL解析但忽略“内联
REFERENCES
规范”(如SQL标准所定义),其中引用被定义为列规范的一部分。MySQLREFERENCES
仅在作为单独FOREIGN KEY
规范的一部分指定时才接受子句。reference_option
有关信息
RESTRICT
,CASCADE
,SET NULL
,NO ACTION
,和SET DEFAULT
选项,请参阅第13.1.20.6,“外键约束”。
表格选项
表格选项用于优化表格的行为。在大多数情况下,您无需指定任何一个。除非另有说明,否则这些选项适用于所有存储引擎。不适用于给定存储引擎的选项可以被接受并记住作为表定义的一部分。如果您以后用于ALTER TABLE
将表转换为使用其他存储引擎,则将应用这些选项。
ENGINE
使用下表中显示的名称之一指定表的存储引擎。引擎名称可以不加引号或加引号。引用的名称
'DEFAULT'
可以识别但可以忽略。储存引擎 描述 InnoDB
具有行锁定和外键的事务安全表。新表的默认存储引擎。如果您具有MySQL经验但不熟悉,请参阅InnoDB存储引擎,尤其是“ InnoDB简介” InnoDB
。MyISAM
二进制便携式存储引擎,主要用于只读或以只读为主的工作负载。请参见“ MyISAM存储引擎”。 MEMORY
该存储引擎的数据仅存储在内存中。请参见“ MEMORY存储引擎”。 CSV
以逗号分隔值格式存储行的表。请参见“ CSV存储引擎”。 ARCHIVE
归档存储引擎。请参见“ ARCHIVE存储引擎”。 EXAMPLE
一个示例引擎。请参见“设置存储引擎”。 FEDERATED
访问远程表的存储引擎。请参见“FEDERATED存储引擎”。 HEAP
这是的同义词 MEMORY
。MERGE
MyISAM
用作一个表的表的集合。也称为MRG_MyISAM
。请参见“ MERGE存储引擎”。NDB
群集的,基于内存的容错表,支持事务和外键。也称为 NDBCLUSTER
。请参见MySQL NDB Cluster 8.0。默认情况下,如果指定了不可用的存储引擎,则该语句失败并显示错误。您可以通过
NO_ENGINE_SUBSTITUTION
从服务器SQL模式中删除(请参见“服务器SQL模式”)来覆盖此行为,以便MySQL允许将指定的引擎替换为默认的存储引擎。通常,在这种情况下,该值为InnoDB
,这是default_storage_engine
系统变量的默认值。当NO_ENGINE_SUBSTITUTION
被禁用,如果存储引擎规格不兑现出现警告。AUTO_INCREMENT
AUTO_INCREMENT
表的初始值。在MySQL 8.0,这个工程的MyISAM
,MEMORY
,InnoDB
,和ARCHIVE
表格。要为不支持该AUTO_INCREMENT
表选项的引擎设置第一个自动增量值,请在创建表后插入一个“哑”行,其值比期望值小一,然后删除该哑行。对于支持语句中的
AUTO_INCREMENT
table选项的引擎CREATE TABLE
,您还可以使用重置该值。该值不能设置为低于当前列中的最大值。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将允许表最大为65,536TB。[DEFAULT] CHARACTER SET
指定表的默认字符集。
CHARSET
是的同义词CHARACTER SET
。如果字符集名称为DEFAULT
,则使用数据库字符集。CHECKSUM
如果您希望MySQL维护所有行的实时校验和(即,当表更改时MySQL自动更新的校验和),请将其设置为1。这使表的更新速度稍慢,但也更容易找到损坏的表。该
CHECKSUM TABLE
语句报告校验和。(MyISAM
仅限)[DEFAULT] COLLATE
指定表的默认排序规则。
COMMENT
表格的注释,最多2048个字符。
您可以
InnoDB
MERGE_THRESHOLD
使用table_option
COMMENT
子句设置表的值。请参见“为索引页配置合并阈值”。设置NDB_TABLE选项。在该表注释
CREATE TABLE
创建一个NDB
表或ALTER TABLE
它会改变一个也可以用于指定一到四个声明NDB_TABLE
选项NOLOGGING
,READ_BACKUP
,PARTITION_BALANCE
,或FULLY_REPLICATED
为一组名称-值对的,用逗号分隔,如果需要的话,紧跟着NDB_TABLE=
以引号引起来的注释文本的字符串。此处显示了使用此语法的示例语句(强调文本):CREATE TABLE t1 ( c1 INT NOT NULLAUTO_INCREMENT PRIMARY KEY , c2 VARCHAR(100), c3 VARCHAR(100) )ENGINE =NDBCOMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";带引号的字符串中不允许有空格。字符串不区分大小写。
注释显示为的输出的一部分
SHOW CREATE TABLE
。注释的文本也可以作为MySQL Information SchemaTABLES
表的TABLE_COMMENT列使用。表的
ALTER TABLE
语句也支持此注释语法NDB
。请记住,与一起使用的表注释ALTER TABLE
将替换表以前可能具有的所有现有注释。MERGE_THRESHOLD
表不支持在表注释中设置选项NDB
(将被忽略)。有关完整的语法信息和示例,请参见“设置NDB_TABLE选项”。
COMPRESSION
用于
InnoDB
表的页面级压缩的压缩算法。支持的值包括Zlib
,LZ4
,和None
。该COMPRESSION
属性是通过透明页面压缩功能引入的。仅存InnoDB
在于每个表文件表空间中的表支持页面压缩,并且仅在支持稀疏文件和打孔的Linux和Windows平台上可用。有关更多信息,请参见“ InnoDB页面压缩”。CONNECTION
FEDERATED
表的连接字符串。注意
较旧的MySQL版本对
COMMENT
连接字符串使用了一个选项。DATA DIRECTORY
,INDEX DIRECTORY
对于
InnoDB
,该子句允许在数据目录之外创建表。必须启用该变量才能使用该子句。必须指定完整目录路径。有关更多信息,请参见“在外部创建表”。DATA DIRECTORY='directory'
innodb_file_per_table
DATA DIRECTORY
在创建
MyISAM
表时,可以使用该条款,该条款,或两者兼而有之。它们分别指定将表的数据文件和索引文件放在何处。与表不同的是,当使用或选项创建表时,MySQL不会创建与数据库名称相对应的子目录。在指定的目录中创建文件。DATA DIRECTORY='directory'
INDEX DIRECTORY='directory'
MyISAM
InnoDB
MyISAM
DATA DIRECTORY
INDEX DIRECTORY
您必须具有
FILE
使用DATA DIRECTORY
或INDEX DIRECTORY
表选项的特权。重要
对于分区表,将忽略表级
DATA DIRECTORY
和INDEX DIRECTORY
选项。错误32091)这些选项仅在不使用时才起作用
--skip-symbolic-links
。您的操作系统还必须有一个有效的线程安全realpath()
调用。有关更多完整信息,请参见“在Unix上为MyISAM表使用符号链接”。如果
MyISAM
创建的表没有DATA DIRECTORY
选项,.MYD
则在数据库目录中创建文件。默认情况下,如果在这种情况下MyISAM
找到现有.MYD
文件,它将覆盖它。这同样适用于.MYI
对没有创建的表文件INDEX DIRECTORY
选项。要抑制此行为,请使用--keep_files_on_create
选项启动服务器,在这种情况下,MyISAM
不会覆盖现有文件,而是返回错误。如果
MyISAM
使用DATA DIRECTORY
或INDEX DIRECTORY
选项创建表,并且找到现有文件.MYD
或.MYI
文件,则MyISAM始终返回错误。它不会覆盖指定目录中的文件。重要
您不能使用带有
DATA DIRECTORY
或的包含MySQL数据目录的路径名INDEX DIRECTORY
。这包括分区表和单个表分区。(请参阅Bug#32167。)DELAY_KEY_WRITE
如果要延迟表的密钥更新直到关闭表,请将其设置为1。请参见“服务器系统变量”中对
delay_key_write
系统变量的描述。(仅限)MyISAM
ENCRYPTION
该
ENCRYPTION
子句启用或禁用表的页面级数据加密InnoDB
。必须先安装和配置密钥环插件,然后才能启用加密。在MySQL 8.0.16之前,ENCRYPTION
只能在每个表文件表空间中创建表时指定该子句。从MySQL 8.0.16开始,ENCRYPTION
在常规表空间中创建表时也可以指定该子句。从MySQL 8.0.16开始,如果
ENCRYPTION
未指定子句,则表会继承默认的模式加密。如果table_encryption_privilege_check
启用了该变量,TABLE_ENCRYPTION_ADMIN
则需要特权来创建带有ENCRYPTION
不同于默认模式加密的子句设置的表。在常规表空间中创建表时,表和表空间的加密必须匹配。从MySQL 8.0.16开始,使用不支持加密的存储引擎时,请使用
ENCRYPTION
非'N'
或''
不允许的值指定子句。以前,该条款已被接受。有关更多信息,请参见“ InnoDB静态数据加密”。
INSERT_METHOD
如果要将数据插入
MERGE
表中,则必须指定INSERT_METHOD
要在其中插入行的表。INSERT_METHOD
是MERGE
仅对表有用的选项。使用FIRST
或的值LAST
使插入到第一个或最后一个表,或使用值NO
防止插入。请参见“ MERGE存储引擎”。KEY_BLOCK_SIZE
对于
MyISAM
表,KEY_BLOCK_SIZE
可以选择指定用于索引键块的字节大小。该值被视为提示;如有必要,可以使用其他大小。KEY_BLOCK_SIZE
为单个索引定义指定的值将覆盖表级KEY_BLOCK_SIZE
值。对于
InnoDB
表,KEY_BLOCK_SIZE
指定要用于压缩表的页面大小(以千字节为单位)。该值被视为提示;如有必要,可以使用其他大小。只能小于或等于该值。值0表示默认的压缩页面大小,是该值的一半。取决于,可能的值包括0、1、2、4、8和16。有关更多信息,请参见“ InnoDB表压缩”。InnoDB
KEY_BLOCK_SIZE
InnoDB
KEY_BLOCK_SIZE
innodb_page_size
innodb_page_size
innodb_page_size
KEY_BLOCK_SIZE
Oracle建议使
innodb_strict_mode
指定时KEY_BLOCK_SIZE
为InnoDB
表。当innodb_strict_mode
被启用,指定一个无效的KEY_BLOCK_SIZE
值返回一个错误。如果innodb_strict_mode
禁用KEY_BLOCK_SIZE
此KEY_BLOCK_SIZE
选项,则无效值将导致警告,并且该选项将被忽略。Create_options
响应的列将SHOW TABLE STATUS
报告KEY_BLOCK_SIZE
该表实际使用的列,也是如此SHOW CREATE TABLE
。InnoDB
仅KEY_BLOCK_SIZE
在表级别支持。KEY_BLOCK_SIZE
不支持32KB和64KBinnodb_page_size
值。InnoDB
表压缩不支持这些页面大小。InnoDB
KEY_BLOCK_SIZE
创建临时表时不支持该选项。MAX_ROWS
您计划在表中存储的最大行数。这不是硬性限制,而是对存储引擎的提示,即表必须至少能够存储这么多行。
重要
不建议使用
MAX_ROWS
withNDB
表控制表分区的数量。为了向后兼容,它在更高版本中仍受支持,但将来的版本中可能会删除它。改用PARTITION_BALANCE;请参阅设置NDB_TABLE选项。该
NDB
存储引擎将这个值作为最大。如果您打算创建非常大的NDB簇表(含数百万行),你应该使用这个选项,以确保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
定义存储行的物理格式。
在禁用
CREATE TABLE
了严格模式的情况下执行语句时,如果您指定了表所用存储引擎不支持的行格式,则会使用该存储引擎的默认行格式来创建表。对应于,表的实际行格式在Row_format
和Create_options
列中报告SHOW TABLE STATUS
。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
。有关与行格式相关的要求,请参见 DYNAMICDYNAMIC
行格式。 - 要为
InnoDB
表启用压缩,请指定ROW_FORMAT=COMPRESSED
。ROW_FORMAT=COMPRESSED
创建临时表时不支持该选项。有关与行格式相关的要求,请参见“ InnoDB表和页面压缩”COMPRESSED
。 - 仍然可以通过指定
REDUNDANT
行格式来请求旧版MySQL中使用的行格式。 - 当您指定非默认
ROW_FORMAT
子句时,请考虑同时启用innodb_strict_mode
配置选项。 ROW_FORMAT=FIXED
不支持。如果ROW_FORMAT=FIXED
在innodb_strict_mode
禁用时指定,则InnoDB
发出警告并假定ROW_FORMAT=DYNAMIC
。如果ROW_FORMAT=FIXED
在innodb_strict_mode
启用时指定if (默认),则InnoDB
返回错误。- 有关
InnoDB
行格式的更多信息,请参见“ InnoDB行格式”。
为
MyISAM
表,该选项值可以是FIXED
或DYNAMIC
为静态的或可变长度的行格式。myisampack将类型设置为COMPRESSED
。请参见“ MyISAM表存储格式”。对于
NDB
表格,默认ROW_FORMAT
值为DYNAMIC
。STATS_AUTO_RECALC
指定是否自动重新计算持续的统计数据为
InnoDB
表。该值DEFAULT
使表的持久统计信息设置由innodb_stats_auto_recalc
配置选项确定。1
当表中10%的数据已更改时,该值将导致重新计算统计信息。该值0
可防止对该表进行自动重新计算。使用此设置,ANALYZE TABLE
对表进行实质性更改后,发出一条语句以重新计算统计信息。有关持久统计信息功能的更多信息,请参见“配置持久性优化器统计参数”。STATS_PERSISTENT
指定是否为表启用持久统计信息
InnoDB
。该值DEFAULT
使表的持久统计信息设置由innodb_stats_persistent
配置选项确定。该值1
启用该表的持久统计信息,而该值0
关闭此功能。通过CREATE TABLE
或ALTER TABLE
语句启用永久统计ANALYZE TABLE
信息后,在将代表性数据加载到表中之后,发出一条语句来计算统计信息。有关持久统计信息功能的更多信息,请参见“配置持久性优化器统计参数”。STATS_SAMPLE_PAGES
估算索引列的基数和其他统计信息时(例如由所计算出的索引页),要采样的索引页数
ANALYZE TABLE
。有关更多信息,请参见“配置持久性优化器统计参数”。TABLESPACE
该
TABLESPACE
子句可用于在现有的常规表空间,每表文件表空间或系统表空间中创建表。CREATE TABLE tbl_name ...TABLESPACE [=] tablespace_name您指定的常规表空间在使用该
TABLESPACE
子句之前必须存在。有关常规表空间的信息,请参见“通用表空间”。tablespace_name
是区分大小写的标识符。它可以被引用或不被引用。不允许使用正斜杠字符(“/ ”)。以“ innodb_ ”开头的名称保留作特殊用途。要在系统表空间中创建表,请指定
innodb_system
为表空间名称。CREATE TABLE tbl_name ...TABLESPACE [=] innodb_system使用
TABLESPACE[=] innodb_system
,您可以将任何未压缩行格式的表放置在系统表空间中,而不管其innodb_file_per_table
设置如何。例如,您可以使用将一个表添加ROW_FORMAT=DYNAMIC
到系统表空间TABLESPACE[=] innodb_system
。要在每个表文件表空间中创建一个表,请指定
innodb_file_per_table
为表空间名称。CREATE TABLE tbl_name ...TABLESPACE [=] innodb_file_per_table注意
如果
innodb_file_per_table
启用,则无需指定TABLESPACE=innodb_file_per_table
创建InnoDB
每表文件表空间。启用InnoDB
时,默认情况下在每个表文件表空间中创建表innodb_file_per_table
。DATA DIRECTORY
允许该子句使用,CREATE TABLE ... TABLESPACE=innodb_file_per_table
但不支持与该TABLESPACE
子句组合使用。注意
自MySQL 8.0.13起,不再支持
TABLESPACE = innodb_file_per_table
和TABLESPACE = innodb_temporary
带有的子句,CREATE TEMPORARY TABLE
在将来的MySQL版本中将不再支持。该
STORAGE
表选项仅采用了与NDB
表。STORAGE
确定使用的存储类型(磁盘或内存),可以是DISK
或MEMORY
。TABLESPACE ... STORAGE DISK
将表分配给NDB群集磁盘数据表空间。该表空间必须已经使用创建CREATE TABLESPACE
。有关更多信息,请参见“ NDB群集磁盘数据表”。重要
没有
STORAGE
子句的CREATE TABLE
语句不能在语句中使用TABLESPACE
。UNION
用于访问一组相同的
MyISAM
表。这仅适用于MERGE
表。请参见“ MERGE存储引擎”。您必须具有
SELECT
,UPDATE
和DELETE
特权才能映射到MERGE
表。注意
以前,所有使用的表必须与
MERGE
表本身位于同一数据库中。此限制不再适用。
表分区
partition_options
可用于控制使用创建的表的分区CREATE TABLE
。
并非partition_options
本节开头的语法中显示的所有选项都不适用于所有分区类型。请参阅以下各个类型的清单以获取每种类型的特定信息,并请参阅分区,以获取有关MySQL中分区工作原理和用途的更完整信息,以及表创建的其他示例以及与之相关的其他语句到MySQL分区。
分区可以修改,合并,添加到表中以及从表中删除。有关完成这些任务的MySQL语句的基本信息,请参见“ ALTER TABLE语句”。有关更多详细的描述和示例,请参见“分区管理”。
PARTITION BY
如果使用,则
partition_options
子句以开头PARTITION BY
。此子句包含用于确定分区的函数;该函数返回一个整数值,范围是1到num
,其中num
是分区数。(一个表可能包含的用户定义的分区的最大数量为1024;此部分后面将讨论的子分区的数量已包括在该最大值中。)注意
子句中
expr
使用的表达式()PARTITION BY
不能引用不在所创建表中的任何列;明确不允许使用此类引用,这些引用会导致语句失败并出现错误。(缺陷#29444)HASH(expr)
哈希一个或多个列以创建用于放置和定位行的键。
expr
是使用一个或多个表列的表达式。这可以是产生单个整数值的任何有效MySQL表达式(包括MySQL函数)。例如,这些都是使用的有效CREATE TABLE
语句PARTITION BY HASH
:CREATE TABLE t1 (col1 INT, col2 CHAR(5))PARTITION BY HASH (col1);CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)PARTITION BY HASH ( YEAR(col3) );您不能将
VALUES LESS THAN
或VALUES IN
子句与一起使用PARTITION BY HASH
。PARTITION BY HASH
使用余数expr
除以分区数(即模数)。有关示例和更多信息,请参见“ HASH分区”。LINEAR
关键字需要稍微不同的算法。在这种情况下,作为一个或多个逻辑AND
运算的结果,计算存储行的分区的数量。有关线性哈希的讨论和示例,请参见“线性哈希分区”。KEY(column_list)
这与相似
HASH
,除了MySQL提供散列功能以确保均匀的数据分布。该column_list
参数仅是1个或更多表列的列表(最多16个)。此示例显示了一个简单的表,该表按键进行了分区,分为四个分区:CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)PARTITION BY KEY (col3)PARTITIONS 4;对于按键分区的表,可以通过使用
LINEAR
关键字来进行线性分区。与由分区的表具有相同的效果HASH
。也就是说,使用&
运算符而不是模数来找到分区号(有关详细信息,请参见“ LINEA HASH分区”和“ 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
表示服务器使用默认KEY
在MySQL 5.5及更高版本中为新分区表实现和使用的键哈希函数。(使用MySQL 5.5和更高版本中使用的键哈希函数创建的分区表不能被MySQL 5.1服务器使用。)不指定该选项与使用效果相同ALGORITHM=2
。此选项主要用于[LINEAR] KEY
在MySQL 5.1和更高版本的MySQL之间升级或降级分区表时,或用于创建由KEY
或LINEAR KEY
可以在MySQL 5.1服务器上使用的MySQL 5.5或更高版本的服务器上。有关更多信息,请参见“ 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中的更改。(如果将包含KEY
从MySQL 5.7(实际上是5.6.11或更高版本)服务器制作的分区表或子分区表的转储加载到MySQL 5.5.30或更早版本的服务器中,则在此找到的信息也适用。)同样在MySQL 5.6.11和更高版本中,
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 8.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 p0VALUES LESS THAN (1991),PARTITION p1VALUES LESS THAN (1995),PARTITION p2VALUES LESS THAN (1999),PARTITION p3VALUES LESS THAN (2002),PARTITION p4VALUES LESS THAN (2006),PARTITION p5VALUES LESS THAN MAXVALUE );PARTITION ... VALUES LESS THAN ...
陈述以连续的方式工作。VALUES LESS THAN MAXVALUE
用于指定大于其他指定最大值的“剩余”值。VALUES LESS THAN
子句以类似于块各case
部分switch ... case
(在许多编程语言,例如C,Java和PHP中都可以找到)的方式顺序工作。就是说,子句的排列方式必须使每个连续序列中指定的上限VALUES LESS THAN
大于上一个连续引用的上限,而引用的那个上限则排在MAXVALUE
列表的最后。RANGE COLUMNS(column_list)
此变体on
RANGE
有助于使用多个列上的范围条件(即具有诸如WHERE a = 1 AND b < 10
或条件WHERE a = 1 AND b = 10 AND c < 10
)对查询进行分区修剪。它使您可以通过使用COLUMNS
子句中的列列表和每个分区定义子句中的一组列值来指定多列中的值范围。(在最简单的情况下,此集合由一列组成。)和中可以引用的最大列数为16。PARTITION ... VALUES LESS THAN(value_list)
column_list
value_list
该
column_list
中使用的COLUMNS
条款可能只包含列的名称;列表中的每一列必须是以下MySQL数据类型之一:整数类型;字符串类型;和时间或日期列类型。列使用BLOB
,TEXT
,SET
,ENUM
,BIT
,或空间数据类型不允许;也不允许使用浮点数类型的列。您也不得在该COLUMNS
子句中使用函数或算术表达式。VALUES LESS THAN
分区定义中使用的子句必须为出现在该COLUMNS()
子句中的每一列指定一个文字值。也就是说,用于每个VALUES LESS THAN
子句的值列表必须包含与该COLUMNS
子句中列出的列相同数量的值。尝试在VALUES LESS THAN
子句中使用比该COLUMNS
子句中更多或更少的值会导致该语句失败,并出现错误:使用列列表进行分区...不一致。您无法使用NULL
中显示的任何值VALUES LESS THAN
。可以使用MAXVALUE
对于给定的列而不是第一列,不止一次,如本示例所示:CREATE TABLE rc ( a INT NOT NULL, b INT NOT NULL )PARTITION BY RANGE COLUMNS (a,b) (PARTITION p0VALUES LESS THAN (10,5),PARTITION p1VALUES LESS THAN (20,10),PARTITION p2VALUES LESS THAN (50,MAXVALUE ),PARTITION p3VALUES LESS THAN (65,MAXVALUE ),PARTITION p4VALUES LESS THAN (MAXVALUE ,MAXVALUE ) );VALUES LESS THAN
值列表中使用的每个值必须与相应列的类型完全匹配;没有进行任何转换。例如,您不能将字符串'1'
用于与使用整数类型的列匹配的值(必须1
改为使用数字),也不能将数字1
用于与使用字符串类型的列匹配的值(在这种情况下在这种情况下,必须使用带引号的字符串:)'1'
。有关更多信息,请参见“ RANGE分区”和“分区修剪”。
LIST(expr)
在基于表列分配有限的可能值集(例如州或国家/地区代码)时,这很有用。在这种情况下,可以将与某个州或国家/地区有关的所有行分配给单个分区,也可以为某个州或国家/地区的某个组保留分区。与相似
RANGE
,不同之处在于仅VALUES IN
可用于指定每个分区的允许值。VALUES IN
与要匹配的值列表一起使用。例如,您可以创建如下分区方案:CREATE TABLE client_firms ( id INT,name VARCHAR(35) )PARTITION BY LIST (id) (PARTITION r0VALUES IN (1, 5, 9, 13, 17, 21),PARTITION r1VALUES IN (2, 6, 10, 14, 18, 22),PARTITION r2VALUES IN (3, 7, 11, 15, 19, 23),PARTITION r3VALUES IN (4, 8, 12, 16, 20, 24) );使用列表分区时,必须使用至少定义一个分区
VALUES IN
。您不能VALUES LESS THAN
与一起使用PARTITION BY LIST
。注意
对于由分区的表
LIST
,与一起使用的值列表VALUES IN
必须仅由整数值组成。在MySQL 8.0中,您可以使用分区来克服此限制LIST COLUMNS
,本节稍后将对此进行介绍。LIST COLUMNS(column_list)
此变体on
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
有用于值列表之间的一个重要区别
VALUES IN
有PARTITION BY LIST COLUMNS
,而不是当它与使用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 p0VALUES IN ( (0,0), (NULL,NULL) ),PARTITION p1VALUES IN ( (0,1), (0,2), (0,3), (1,1), (1,2) ),PARTITION p2VALUES IN ( (1,0), (2,0), (2,1), (3,0), (3,1) ),PARTITION p3VALUES IN ( (1,3), (2,2), (2,3), (3,2), (3,3) ) );PARTITIONS num
分区数量可以选择用子句指定,其中是分区数量。如果同时使用此子句和任何子句,则必须等于使用子句声明的任何分区的总数。
PARTITIONS num
num
PARTITION
num
PARTITION
注意
PARTITIONS
在创建由RANGE
或分区的表时,无论是否使用子句LIST
,都必须PARTITION VALUES
在表定义中至少包括一个子句(请参见下文)。SUBPARTITION BY
分区可以可选地划分为多个子分区。这可以通过使用可选
SUBPARTITION BY
子句来指示。可以通过HASH
或进行子分区KEY
。这些可能是LINEAR
。这些工作方式与前面针对等效分区类型所述的方式相同。(不可能用LIST
或细分RANGE
。)可以使用
SUBPARTITIONS
关键字后跟一个整数值来指示子分区的数量。严格检查
PARTITIONS
orSUBPARTITIONS
子句中使用的值,并且该值必须遵循以下规则:- 该值必须是一个非零的正整数。
- 不允许前导零。
- 该值必须是整数文字,并且不能为表达式。例如,
PARTITIONS 0.2E+01
即使0.2E+01
计算为,也不允许2
。错误15890)
partition_definition
每个分区可以使用
partition_definition
子句单独定义。组成此子句的各个部分如下:PARTITION partition_name
指定分区的逻辑名称。
VALUES
对于范围分区,每个分区必须包含一个
VALUES LESS THAN
子句;对于列表分区,必须VALUES IN
为每个分区指定一个子句。这用于确定哪些行要存储在此分区中。见分区类型的讨论分区,语法的例子。[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
必须是绝对系统路径名。您必须具有
FILE
使用DATA DIRECTORY
或INDEX DIRECTORY
分区选项的特权。例:
CREATE TABLE th (id INT,name VARCHAR(30), adate DATE)PARTITION BY LIST (YEAR(adate)) (PARTITION p1999VALUES IN (1995, 1999, 2003)DATA DIRECTORY = '/var/appdata/95/data'INDEX DIRECTORY = '/var/appdata/95/idx',PARTITION p2000VALUES IN (1996, 2000, 2004)DATA DIRECTORY = '/var/appdata/96/data'INDEX DIRECTORY = '/var/appdata/96/idx',PARTITION p2001VALUES IN (1997, 2001, 2005)DATA DIRECTORY = '/var/appdata/97/data'INDEX DIRECTORY = '/var/appdata/97/idx',PARTITION p2002VALUES 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
。每个分区可以指定一个数据目录和一个索引目录。如果未指定,则默认情况下,数据和索引存储在表的数据库目录中。
在
DATA DIRECTORY
和INDEX DIRECTORY
选项将被忽略创建分区表是否NO_DIR_IN_CREATE
有效。MAX_ROWS
和MIN_ROWS
可以分别用来指定要存储在分区中的最大和最小行数。为价值观
max_number_of_rows
和min_number_of_rows
必须为正整数。与具有相同名称的表级选项一样,它们仅充当服务器的“建议”,而不是硬性限制。TABLESPACE
通过指定可用于指定
InnoDB
分区的每表文件表空间TABLESPACE `innodb_file_per_table`
。所有分区必须属于同一存储引擎。不支持将
InnoDB
表分区放在共享InnoDB
表空间中。共享表空间包括InnoDB
系统表空间和常规表空间。
subpartition_definition
分区定义可以选择包含一个或多个
subpartition_definition
子句。每一个都至少由组成,其中是子分区的标识符。除了使用替换关键字外,子分区定义的语法与分区定义的语法相同。SUBPARTITION name
name
PARTITION
SUBPARTITION
子分区必须由
HASH
或进行KEY
,并且只能在RANGE
或LIST
分区上进行。请参见“子分区”。
按生成的列进行分区
允许按生成的列进行分区。例如:
CREATE TABLE t1 ( s1 INT, s2 INTAS (EXP(s1))STORED )PARTITION BY LIST (s2) (PARTITION p1VALUES IN (1) );
分区会将生成的列视为常规列,从而启用了一些变通办法,以解决不允许分区的功能受到限制的问题(请参见“与分区相关函数的限制”)。前面的示例演示了此技术:EXP()
不能在PARTITION BY
子句中直接使用,但是EXP()
允许使用定义的生成列。