As in this question, I've been reading PoEAA and wondering if it's possible to defer referential integrity checks until commit in MySQL.
在这个问题中，我一直在阅读PoEAA，并想知道是否可以将引用完整性检查推迟到在MySQL中提交。
I've run into this problem when wanting to insert a bunch of products and related products in the same commit. Even within a transaction, I get constraint errors when I try to insert into the related_products join table.
当我想在同一个提交中插入一系列产品和相关产品时，我遇到了这个问题。即使在事务中，当我试图插入到related_products连接表时，也会出现约束错误。
If it helps, I'm using PHP PDO for database connections.
如果有帮助，我将使用PHP PDO进行数据库连接。
I'd appreciate any help you could offer.
如果你能提供任何帮助，我将不胜感激。
3 个解决方案
#1
52
Looks like my answer is here...
看来我的答案在这里……
Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.
与一般的MySQL类似，在插入、删除或更新许多行的SQL语句中，InnoDB逐行检查唯一和外键约束。在执行外键检查时，InnoDB将在子或父记录上设置共享的行级锁。InnoDB立即检查外键约束;检查不会延迟到事务提交。根据SQL标准，默认行为应该延迟检查。也就是说，只有在处理完整个SQL语句之后才检查约束。在InnoDB实现延迟约束检查之前，有些事情是不可能的，比如删除使用外键引用自己的记录。
Back to the drawing board.
回到绘图板上。
#2
14
If you are asking if MySQL supports the DEFERRABLE attribute for foreign keys (including the option INITIALLY DEFERRED) then the answer is a clear no.
如果您问MySQL是否支持外键的可延迟属性(包括最初延迟的选项)，那么答案显然是否定的。
You can't defer constraint checking until commit time in MySQL.
在MySQL中提交时间之前，不能延迟约束检查。
And - as you have already pointed out - they are always evaluated at "row level" not on "statement level".
而且——正如您已经指出的那样——它们总是在“行级”而不是“语句级”进行评估。
#3
9
You may handle this limitation of innodb engine, by temporarily disabling foreign key checks by setting server variable:
您可以通过设置服务器变量暂时禁用外键检查来处理innodb引擎的这个限制:
set foreign_key_checks=0;
From MySQL manual:
从MySQL手册:
mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.
mysqldump还生成转储文件中表的正确定义，并且不会忘记外键。
To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:
为了更容易地重新加载具有外键关系的表的转储文件，mysqldump会自动在转储输出中包含一条语句，将foreign_key_check设置为0。这避免了在重新加载转储时必须按特定顺序重新加载表的问题。也可以手动设置该变量:
mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;
#1
52
Looks like my answer is here...
看来我的答案在这里……
Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.
与一般的MySQL类似，在插入、删除或更新许多行的SQL语句中，InnoDB逐行检查唯一和外键约束。在执行外键检查时，InnoDB将在子或父记录上设置共享的行级锁。InnoDB立即检查外键约束;检查不会延迟到事务提交。根据SQL标准，默认行为应该延迟检查。也就是说，只有在处理完整个SQL语句之后才检查约束。在InnoDB实现延迟约束检查之前，有些事情是不可能的，比如删除使用外键引用自己的记录。
Back to the drawing board.
回到绘图板上。
#2
14
If you are asking if MySQL supports the DEFERRABLE attribute for foreign keys (including the option INITIALLY DEFERRED) then the answer is a clear no.
如果您问MySQL是否支持外键的可延迟属性(包括最初延迟的选项)，那么答案显然是否定的。
You can't defer constraint checking until commit time in MySQL.
在MySQL中提交时间之前，不能延迟约束检查。
And - as you have already pointed out - they are always evaluated at "row level" not on "statement level".
而且——正如您已经指出的那样——它们总是在“行级”而不是“语句级”进行评估。
#3
9
You may handle this limitation of innodb engine, by temporarily disabling foreign key checks by setting server variable:
您可以通过设置服务器变量暂时禁用外键检查来处理innodb引擎的这个限制:
set foreign_key_checks=0;
From MySQL manual:
从MySQL手册:
mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.
mysqldump还生成转储文件中表的正确定义，并且不会忘记外键。
To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:
为了更容易地重新加载具有外键关系的表的转储文件，mysqldump会自动在转储输出中包含一条语句，将foreign_key_check设置为0。这避免了在重新加载转储时必须按特定顺序重新加载表的问题。也可以手动设置该变量:
mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;