在MySQL中，我可以将引用完整性检查推迟到提交时吗？

Question

如同这个问题所述，我正在阅读 PoEAA 并想知道在 MySQL 中是否能够推迟参照完整性检查到提交时。

当我想要在同一次提交中插入一堆产品及其相关产品时，我遇到了这个问题。即使在事务内部，当我尝试插入到 related_products 连接表时，也会出现约束错误。

如果可以的话，我正在使用PHP PDO进行数据库连接。

我将非常感激您提供的任何帮助。

- Ross McFarlane

Answer 1

看起来我回答的内容在这里...

和MySQL一样，在执行插入、删除或更新多行的SQL语句时，InnoDB会逐行检查唯一性和外键约束。当进行外键检查时，InnoDB会在需要查看的子或父记录上设置共享行级锁。InnoDB立即检查外键约束；该检查不会被推迟到事务提交。根据SQL标准，应默认使用推迟检查的行为。也就是说，只有在整个SQL语句处理完后才检查约束。在InnoDB实现推迟约束检查之前，有些事情将不可能实现，例如删除引用自身使用外键的记录。

回到起点。

- Ross McFarlane

---

@rossmcf +1 因为我一直在查找这句话的文档 :D - Songo

如果此功能对您很重要，您可以考虑迁移到支持延迟约束的PostgreSQL（包括自9.0起支持延迟唯一约束），而且您还将获得许多其他方便的功能（递归查询、窗口函数、检查约束等）。 - user330315

这方面的功能请求自2004年以来就一直存在：https://bugs.mysql.com/bug.php?id=7529 现在应该很快就会有了... - mae

该语言似乎已经转移到此处的第3点。 - jswolf19

@jswolf19 如果我没记错的话，那就是他在回答中引用的文字。 - Brian Leishman

Answer 2

如果你想知道MySQL是否支持外键的DEFERRABLE属性（包括选项INITIALLY DEFERRED），那么答案是明确的不支持。

在MySQL中，无法延迟约束检查到提交时间。

正如你已经指出的那样，它们始终在“行级别”而不是“语句级别”上进行评估。

- user330315

---

我仍然找不到任何相关的信息。答案仍然是否定的吗？出于种种好的原因，我正在考虑转换到postgresql。 - akki

@akki：是的，答案仍然是否定的。MySQL不支持可延迟约束（或任何其他现代SQL功能）。 - user330315

Answer 3

你可以通过暂时禁用外键检查来处理 innodb 引擎的这个限制，方法是设置服务器变量：

set foreign_key_checks=0;

来自MySQL手册：

mysqldump还会在转储文件中生成正确的数据表定义，并且不会忘记外键。

为了方便重新加载具有外键关系的转储文件，mysqldump会自动在输出中包含一条语句，将foreign_key_checks设置为0。这避免了在重新加载转储时需要按特定顺序重新加载表格的问题。也可以手动设置此变量：

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;

- Thava

---

我不确定更新后是否仍会检查约束条件，这正是我所要寻找的。 - Ross McFarlane

不，它并没有，因此选项 foreign_key_checks=0 和类似的禁用唯一性检查的选项非常非常危险。如果这么容易，那么MySQL可能也已经实现了延迟约束。这些选项仅用于在您确定不会违反任何约束时加快备份和恢复速度。您不能将此选项用作MySQL缺少延迟约束的解决方法。如果重新启用该选项，则不会重复检查。特别是，如果违反约束，则MySQL可能会显示未定义的行为。 - user2690527