MySQL 多重删除。是否可以多次删除引用的行？
如果我有一个父表和一个子表，是否可以在没有“ON DELETE CASCADE”约束的情况下多次删除其中的行？
在此示例中：
create table a(id int primary key);
create table b(id int primary key, a_id int,
constraint fkb foreign key (a_id) references a(id));
是否可以执行类似的操作来删除表 a 和 b 中的行？ :-(
delete a, b
from b
inner join a on a.id = b.a_id
where a.id = ?;
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails
(`erasmusu6`.`b`, CONSTRAINT `fkb` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`))
我想多次删除行，但不要设置“ON DELETE CASCADE”约束。此外，我还需要过滤 DELETE 命令带有 WHERE 子句，这可能吗？或者我应该在多重删除中创建与表一样多的 DELETE ？
If I have a parent table and a child table, is it possible to multi-delete the rows in them without having a "ON DELETE CASCADE" constraint?
In this example:
create table a(id int primary key);
create table b(id int primary key, a_id int,
constraint fkb foreign key (a_id) references a(id));
Is it not possible to do something like this in order to delete rows in tables a and b? :-(
delete a, b
from b
inner join a on a.id = b.a_id
where a.id = ?;
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails
(`erasmusu6`.`b`, CONSTRAINT `fkb` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`))
I would like to multidelete rows but not to set a "ON DELETE CASCADE" constraint. Also I need to filter the DELETE command with a WHERE clause. Is this possible or should I have to make as many DELETEs as tables in the multidelete?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我通过在 DELETE 命令中指定确切的连接顺序，使用优化器提示解决了问题：
由于优化器提示 STRAIGHT_JOIN，MySQL 将首先删除 b 行。
I solve the problem with optimizer hints, by specifying the exact join order in the DELETE command:
MySQL will DELETE b rows first thanks to the optimizer hint STRAIGHT_JOIN.
这是 mysql 文档页面 (http://dev.mysql.com/doc/refman/5.0/en/delete.html) 中的注释：
“如果使用涉及 InnoDB 的多表 DELETE 语句对于存在外键约束的表，MySQL 优化器可能会按照与其父/子关系不同的顺序处理表，在这种情况下，语句会失败并回滚，您应该从单个表中删除并回滚。依靠ON InnoDB 提供的 DELETE 功能会导致其他表相应地被修改。”
因此，这意味着您被迫不要使用多重删除选项！
希望有帮助..
This is the note from mysql documentation page (http://dev.mysql.com/doc/refman/5.0/en/delete.html):
"If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly."
So, this implies that you are forced not to use multi delete option!
Hope that helps..