如何清空一个自引用的MySQL表格是最佳方式？

Question

我有一张带有递归 parent_id 的自引用 MySQL 表：

CREATE TABLE `recursive` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `data_categorysource_parent_id` (`parent_id`),
  CONSTRAINT `parent_id_refs_id_627b4293`
    FOREIGN KEY (`parent_id`) REFERENCES `data_categorysource` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

在测试期间，我想要清空它，但 TRUNCATE 操作失败了：

TRUNCATE `recursive` 
/* SQL Error: Cannot delete or update a parent row: a foreign key
constraint fails...

目前我需要手动删除所有记录，从树的底部开始向上工作。 即使是对于小树，这也很繁琐。

有没有简单的解决办法？ 我不能轻易地DROP表并重新创建它，因为其他表引用了它（我已经截断了那些表，因此在这里不应该有数据完整性问题）。

- Mat

---

如果您执行"DELETE FROM recursive ORDER BY id"，它将工作正常。然而，如果您有自引用行，那么您几乎没有优雅的选择：http://bugs.mysql.com/bug.php?id=7412。暂时禁用外键是最好的选择。 - Andras Gyomrey

在 Oracle 中做得更好的少数情况之一是，你可以将检查约束条件的责任推迟到事务提交时。 - Ian Ringrose

Answer 1

为什么不这样做：

UPDATE 'recursive' SET 'parent_id' = NULL WHERE 'parent_id' IS NOT NULL;
DELETE FROM 'recursive';

?

- Craig Stuntz

---

“什么是清空自引用MySQL表的最佳方法？”或类似问题在许多互联网站点上出现，甚至在SO上多次复制，但很少有问题有这个简单、优雅且正确的答案。+1 - Barzee

Answer 2

如果您只是想为了测试目的而清空整个东西，可以使用以下方法：

SET FOREIGN_KEY_CHECKS = 0;

// Execute Query

SET FOREIGN_KEY_CHECKS = 1;

这将完全绕过任何外键检查。

- gabriel1836

Answer 3

嗯，你可以暂时在FOREIGN KEY定义中添加ON DELETE CASCADE。这将允许您通过首先删除引用行来截断表。

还有其他ON DELETE类型；默认值为ON DELETE NO ACTION。

- R. Bemrose

Answer 4

或者只需删除（递归）外键约束，然后截断表，最后重新添加约束。

- BradC

Answer 5

重复选择不出现为父级的行并删除它们，直到表为空。（假设没有循环...）

- Assaf

Answer 6

从table_1表中删除日期早于(select T.t_Date from (select max(date(table_1_TIME)) as t_Date from table_1 ) as T)的记录

- BAPU SHINDE

---

请更清晰地格式化代码，并引用原问题的标识符。 - gwaigh