MySQL 外键使用详解

1、MySQL 外键约束语法

MySQL 支持外键，允许在表之间进行相关数据的交叉引用，并有助于保持相关数据的一致性。

一个外键关系涉及到一个父表，该父表保存初始列值，和一个子表，子表的列值引用父表的列值。外键约束定义在子表上。

定义外键约束的基本语法包括以下部分：

[CONSTRAINT [symbol]] FOREIGN KEY[index_name] (col_name, ...)REFERENCES tbl_name (col_name,...)[ON DELETE reference_option][ON UPDATE reference_option]

其中 reference_option 可以是： RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

2、标识符

外键约束命名遵循以下规则：

• 如果定义了 CONSTRAINT 符号值，则使用该值。
• 如果未定义 CONSTRAINT 符号子句，或者在 CONSTRAINT 关键字后未包含符号，则会自动生成约束名。
• 在 MySQL 8.0.16 及更高版本中，如果未定义 CONSTRAINT 符号子句，或者在 CONSTRAINT 关键字后未包含符号，InnoDB 和 NDB 存储引擎将忽略 FOREIGN_KEY index_name。
• 如果定义了 CONSTRAINT 符号值，它必须在数据库中是唯一的。重复的符号会产生类似于：ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121) 的错误。

3、条件和限制

外键约束受到以下条件和限制：

• 父表和子表必须使用相同的存储引擎，且不能定义为临时表。
• 创建外键约束需要在父表上具有 REFERENCES 权限。
• 外键中对应的列和被引用的键必须具有类似的数据类型。固定精度类型如 INTEGER 和 DECIMAL 的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。
• MySQL 支持在表内的一列与另一列之间的外键引用（某列不能对自己进行外键引用）。在这些情况下，“子表记录”指的是在同一表中的依赖记录。
• MySQL 需要在外键和引用键上创建索引，以便外键检查可以快速完成并且不需要扫描表。在引用表中，必须存在一个索引，其中外键列被列为相同顺序的第一列。如果不存在此类索引，则会自动在引用表上创建此索引。如果您稍后创建了另一个可用于强制执行外键约束的索引，则可能会静默删除此索引。如果给出了 index_name，则按照前面的描述使用它。

4、引用操作

当更新或删除操作影响父表中具有在子表中匹配行的键值时，结果取决于 FOREIGN KEY 子句的 ON UPDATE 和 ON DELETE 子句中指定的引用操作。引用操作包括：

• CASCADE：从父表中删除或更新行，并自动删除或更新子表中的匹配行。支持 ON DELETE CASCADE 和 ON UPDATE CASCADE。在两个表之间，不要为在父表或子表的同一列上起作用的几个 ON UPDATE CASCADE 子句定义几个 ON UPDATE CASCADE 子句。
• SET NULL：从父表中删除或更新行，并将子表中的外键列或列设置为 NULL。支持 ON DELETE SET NULL 和 ON UPDATE SET NULL 子句。
• RESTRICT：拒绝对父表的删除或更新操作。指定 RESTRICT（或 NO ACTION）与省略 ON DELETE 或 ON UPDATE 子句相同。
• NO ACTION：来自标准 SQL 的关键字。对于 InnoDB，这等效于 RESTRICT；如果在引用的表中存在相关外键值，则立即拒绝对父表的删除或更新操作。NDB 支持延迟检查，NO ACTION 指定延迟检查；当使用此选项时，直到提交时间才执行约束检查。注意，对于 NDB 表，这会导致对父表和子表进行的所有外键检查都被推迟。
• SET DEFAULT：此操作由 MySQL 解析器识别，但 InnoDB 和 NDB 都会以 ON DELETE SET DEFAULT 或 ON UPDATE SET DEFAULT 子句拒绝表定义。

外键检查

从 MySQL 8.0.16 开始，可以使用 FOREIGN_KEY_CHECKS 配置选项来启用或禁用外键约束检查。当该选项设置为 1 或者未指定时，MySQL 将对外键约束进行检查。当该选项被设置为 0 时，MySQL 将禁用所有外键约束检查。

SET FOREIGN_KEY_CHECKS=0;

使用此选项可以大幅度提高含有大量外键约束的数据库表的数据导入速度。

锁定

当执行涉及外键约束的更新、删除或插入操作时，InnoDB 存储引擎会尝试获取必要的行级共享 (S) 锁定。如果无法立即获取这些锁定，InnoDB 可能会产生等待锁定的情况。

外键错误

在操作涉及到外键约束的表时，可能会遇到一些常见的错误，比如：

• ERROR 1005 (HY000): Can't create table '...' (errno: 150)：MySQL 无法创建表，因为外键约束无法正确地创建。这可能是由于数据类型不匹配或者被参照的键不存在。
• ERROR 1215 (HY000): Cannot add foreign key constraint：MySQL 无法添加外键约束。同样可能是因为数据类型不匹配或者被参照的键不存在。
• ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails：试图插入或更新一行，但是外键约束失败。这通常意味着你正在尝试插入一个不存在于父表的值。