在 MySQL 中，我可以将参照完整性检查推迟到提交吗

In MySQL, can I defer referential integrity checks until commit(在 MySQL 中，我可以将参照完整性检查推迟到提交吗)

问题描述

如这个问题，我一直在阅读 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 中，我可以将参照完整性检查推迟到提交吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！