MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (2)

数据库杂货铺 2021-04-12

763

索引、外键和 CHECK 约束

有几个关键字应用于索引、外键和 CHECK 约束的创建。

● CONSTRAINT symbol

CONSTRAINT symbol 子句用于指定约束名。如果没有给出这个子句，或者 CONSTRAINT 关键字后面没有 symbol，MySQL 会自动生成一个约束名称，但下面提到的例外情况除外。symbol 值（如果使用）对于每个模式（数据库）和每个约束类型必须是唯一的。重复的 symbol 会引发错误。

注意

如果外键定义中没有给出 CONSTRAINT symbol 子句，或者 CONSTRAINT 关键字后面没有 symbol，MySQL 在 8.0.15 版之前使用外键索引名，这个版本之后自动生成一个约束名。

SQL标准指定所有类型的约束（主键、唯一索引、外键、检查）都属于同一个命名空间。在MySQL中，每个模式的每个约束类型都有自己的命名空间。因此，每种约束类型的名称对于每个模式都必须是唯一的，但是不同类型的约束可以具有相同的名称。

● PRIMARY KEY

一个唯一索引，其中所有键列都必须定义为 NOT NULL。如果它们没有显式声明为 NOT NULL，MySQL 会隐式地（静默地）声明。一个表只能有一个 PRIMARY KEY。PRIMARY KEY 的名称总是 PRIMARY，因此不能用作任何其他类型索引的名称。

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

在 InnoDB 表中，请保持 PRIMARY KEY 尽量短以最小化二级索引的存储开销。每个二级索引项包含对应行的主键列的副本。

在创建的表中，首先放置 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 tbl_name 语句来查看表的索引名称。

● KEY | INDEX

KEY 通常来说是 INDEX 的同义词。当在一个列的定义中给出键属性 PRIMARY KEY 时，也可以将其仅指定为 KEY。这是为了与其他数据库系统兼容而实现的。

● UNIQUE

UNIQUE 索引创建一个约束，使索引中的所有值都必须是不同的。如果尝试添加与现有行匹配的键值的新行，则会引发错误。对于所有引擎，UNIQUE 索引允许包含 NULL 的列有多行 NULL 值。如果为 UNIQUE 索引中的列指定前缀值，则列值在前缀长度内必须是唯一的。

如果表具有 PRIMARY KEY 或 UNIQUE NOT NULL 索引，且该索引由具有整数类型的单个列组成，则可以在 SELECT 语句中使用 _rowid 引用索引列，

● FULLTEXT

FULLTEXT 索引是用于全文搜索的一种特殊类型的索引。仅有 MyISAM 和 MyISAM 存储引擎支持 FULLTEXT 索引。只能对 CHAR、VARCHAR 和 TEXT 列创建全文索引。索引始终发生在整个列上；不支持列前缀索引，并且如果指定了前缀长度，会被忽略。如果全文索引和搜索操作需要特殊处理，可以将 WITH PARSER 子句指定为 index_option 值，将解析器插件与索引关联起来。此子句仅对 FULLTEXT 索引有效。InnoDB 和 MyISAM 支持全文解析器插件。

● SPATIAL

可以在空间数据类型上创建 SPATIAL 索引。只有 InnoDB 和 MyISAM 表才支持空间类型，索引列必须声明为 NOT NULL。

● FOREIGN KEY

MySQL支持外键，外键允许跨表交叉引用相关数据，外键约束有助于保持分散数据的一致性。

使用InnoDB存储引擎的分区表不支持外键。

● CHECK

CHECK 子句允许创建约束，用于检查表行中数据值。

● key_part

■ key_part 可以用 ASC 或 DESC 结尾，以指定索引值是按升序还是降序存储。如果没有指定顺序说明符，则默认值为升序。

■ 对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表，length 属性定义的前缀最长可达767字节。对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，前缀长度限制为3072字节。对于 MyISAM 表，前缀长度限制为1000字节。

■ 前缀限制以字节为单位。但是，CREATE TABLE、ALTER TABLE 和 CREATE INDEX 语句中索引规范的前缀长度，对于非二进制字符串类型（CHAR、VARCHAR、TEXT）解释为字符数，对于二进制字符串类型（BINARY、VARBINARY、BLOB）解释为字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑这一点。

■ 从MySQL 8.0.17开始，key_part 的 expr 可以采用 (CAST json_path AS type ARRAY) 的形式在 JSON 列上创建多值索引。

● 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 值。

■ WITH PARSER

WITH PARSER 选项只能用于 FULLTEXT 索引。如果全文索引和搜索操作需要特殊处理，WITH PARSER 将解析器插件与索引相关联。InnoDB 和 MyISAM 支持全文解析器插件。如果您有一个 MyISAM 表和相关的全文解析器插件，那么可以使用 ALTER TABLE 语句将该表的存储引擎转换为 InnoDB。

■ COMMENT

在MySQL 8.0中，索引定义可以包含最多1024个字符的可选注释。

可以使用 index_option COMMENT 子句为单个索引设置 InnoDB MERGE_THRESHOLD 值。

■ ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 选项（从MySQL 8.0.21开始提供）用于指定主存储引擎和辅助存储引擎的索引属性。这些选项保留供将来使用。

● reference_definition

InnoDB 和 NDB 表支持检查外键约束。被引用表的列必须始终显式命名。支持外键上的 ON DELETE 和 ON UPDATE 操作。

对于其他存储引擎，MySQL Server 解析并忽略 CREATE TABLE 语句中的 FOREIGN KEY 和 REFERENCES 语法。

重要

对于熟悉 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）且不为 NULL 的键的外键。

MySQL解析但忽略“内联 REFERENCES 规范”（如SQL标准中所定义），其中引用被定义为列规范的一部分。只在被指定为独立 FOREIGN KEY 规范的一部分时 MySQL 才接受 REFERENCES 子句。

● reference_option

官方文档：

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

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (2)

评论