MySQL 多删除 2 个可能相互引用的表中的行

Question

我有 2 个 MySQL 表，它们的 (InnoDB) 外键相互关联。例如，

-- Adminer 4.2.3 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `null_or_b_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `null_or_b_id` (`null_or_b_id`),
  CONSTRAINT `a_ibfk_1` FOREIGN KEY (`null_or_b_id`) REFERENCES `b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `a` (`id`, `null_or_b_id`) VALUES
(1, NULL),
(2, 2),
(4, 3),
(3, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8);

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `null_or_a_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `null_or_a_id` (`null_or_a_id`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`null_or_a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `b` (`id`, `null_or_a_id`) VALUES
(1, NULL),
(8, NULL),
(2, 2),
(4, 3),
(3, 4),
(5, 6),
(6, 7),
(7, 8);

-- 2016-02-03 06:45:07

我想做的是从a中删除id为1、2、3和5的记录，并删除由于外键约束而需要在a和b中删除的任何记录。我试过:

delete from a where a.id in (1,2,3,5);

delete a,b from a left join b on b.null_or_a_id = a.id where a.id in (1,2,3,5);

以上都给出了同样的错误:

Error in query (1451): Cannot delete or update a parent row: a foreign key constraint fails (`test/multi_delete_with_references`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`null_or_a_id`) REFERENCES `a` (`id`))

即使删除 a 中定义的 b 的外键约束，我也会得到同样的错误。

我不能做的事情:

• 禁用外键检查:因为这两个表也被其他表引用，我不希望这些表有孤立行，如果此删除将导致此类孤立行，我需要此删除失败。<
• 首先从子表中删除:因为正如您所见，两个表中的 id 为 2 的行，它们相互引用，因此如果没有另一个，则不能删除一个，对于 id 为 2 的行也是如此两个表中的 3 和 4，它们形成了一个自引用链。

我看过答案here它对我不起作用，因为两个表相互引用。

有没有办法解决这个问题？

顺便说一句，我也尝试过动态生成复杂的嵌套查询，但结果是没完没了:

delete from a where id in (1,2,3,5);
delete from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where id in (1,2,3,5)) x);
delete from a where null_or_b_id is not null and null_or_b_id in (select * from (select id from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where id in (1,2,3,5)) x)) x);
delete from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where null_or_b_id is not null and null_or_b_id in (select * from (select id from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where id in (1,2,3,5)) x)) x)) x)
...

Answer 1

您的问题与 this one 非常相似和 same solution适用:首先通过将要删除的那些行中的引用列设置为 NULL 来删除引用。然后删除。