清空自引用MySQL表的最佳方法是什么? [英] What is the best way to empty a self-referential MySQL table?

问题描述

我有一个带有递归parent_id的自引用MySQL表:

I have a self-referential MySQL table with a recursive parent_id:

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失败:

During testing, I want to empty it but TRUNCATE fails:

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

我目前必须手动删除所有记录，从树的底部开始向上操作.即使有小树，这也很麻烦.

I currently have to manually delete all records, starting at the bottom of the tree working upwards. This gets onerous even with small trees.

有没有解决这个问题的简单方法?我无法DROP将该表轻松地重新创建，就像其他表引用该表一样(我已经将其截断了，所以那里应该没有数据完整性问题).

Is there an easy way around this? I can't DROP the table and re-create it easily as other tables reference it (I have already truncated those so there should be no data integrity issues there).

推荐答案

为什么不呢?

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

?

这篇关于清空自引用MySQL表的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！