在MySQL中，我可以将参照完整性检查推迟到提交之前 [英] In MySQL, can I defer referential integrity checks until commit
问题描述
如这个问题，我一直在阅读PoEAA，想知道是否有可能将参照完整性检查推迟到在MySQL中提交.
As in this question, I've been reading PoEAA and wondering if it's possible to defer referential integrity checks until commit in MySQL.
我想在同一提交中插入一堆产品和相关产品时遇到了这个问题.即使在事务中，尝试插入related_products联接表时也会遇到约束错误.
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.
如果有帮助，我正在使用PHP PDO进行数据库连接.
If it helps, I'm using PHP PDO for database connections.
非常感谢您能提供的帮助.
I'd appreciate any help you could offer.
推荐答案
Looks like my answer is here...
与MySQL一般而言，在插入，删除或更新许多行的SQL语句中，InnoDB逐行检查UNIQUE和FOREIGN KEY约束.在执行外键检查时，InnoDB在必须查看的子记录或父记录上设置共享的行级锁. InnoDB立即检查外键约束； .根据SQL标准，默认行为应为延迟检查.也就是说，仅在处理了整个SQL语句之后才检查约束.在InnoDB实现延迟约束检查之前，某些事情将是不可能的，例如删除使用外键引用其自身的记录.
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中，我可以将参照完整性检查推迟到提交之前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
