MySQL多删除。是否有可能多删除引用的行？ [英] MySQL Multi-Delete. Is it possible to multi-delete referenced rows?

问题描述

如果我有一个父表和一个子表，是否有可能在没有ON DELETE CASCADE约束的情况下多删除其中的行？

在这个例子中：

  create table a（id int primary key）; 
创建表b（id int主键，a_id int，
约束fkb外键（a_id）引用a（id））; 
  

为了删除表a和b中的行，不可能这样做吗？ ： - （

 删除a，b 
 from b 
内部加入a a.id = b .a_id 
其中a.id =？; 
 
错误代码：1451.无法删除或更新父行：外键约束失败
（`erasmusu6`.`b `，CONSTRAINT`fkb` FOREIGN KEY（`a_id`）引用`a`（`id`））
  

<我想多行删除，但不要设置ON DELETE CASCADE约束。我还需要过滤删除命令与一个 WHERE 子句这是可能的，或者我应该做很多删除 s作为multidelete中的表？

解决方案我通过指定确切的连接顺序来解决优化器提示的问题在DELETE命令中：

 删除a，b 
 from b 
 STRAIGHT_JOIN a on a.id = b.a_id 
其中a.id =？; 
  

MySQL会先删除b行谢谢到优化器提示STRAIGHT_JOIN。 p>

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?

解决方案

I solve the problem with optimizer hints, by specifying the exact join order in the DELETE command:

delete a, b
from b
STRAIGHT_JOIN a on a.id = b.a_id
where a.id = ?;

MySQL will DELETE b rows first thanks to the optimizer hint STRAIGHT_JOIN.

这篇关于MySQL多删除。是否有可能多删除引用的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！