清空自引用 MySQL 表的最佳方法是什么？
我有一个带有递归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该表并在其他表引用它时轻松地重新创建它（我已经截断了这些表，因此那里应该不存在数据完整性问题）。
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
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.
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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
为什么不：
？
Why not:
?
如果您只想清空整个内容以进行测试，请使用：
这完全绕过了任何外键检查。
If you just want to empty the whole thing for testing purposes use:
This totally bypasses any foreign key checks.
好吧，您可以将
ON DELETE CASCADE添加到FOREIGN KEY定义中......至少是暂时的。 这将允许您通过首先删除引用的行来截断表。还有其他
ON DELETE类型也是如此； 默认值为ON DELETE NO ACTION。Well, you could add an
ON DELETE CASCADEto theFOREIGN KEYdefinition... at least temporarily. That would allow you to truncate the table by removing the referenced rows first.There are other
ON DELETEtypes as well; the default isON DELETE NO ACTION.或者只是删除（递归）外键约束，然后截断表，然后重新添加约束。
Or just remove the (recursive) foreign key constraint, then truncate the table, then re-add the contraint.
重复选择不作为父项出现的行并将其删除，直到表为空。 （假设没有循环...）
Repeatedly select the rows that do not appear as parents and delete them, until the table is empty. (Assuming there are no cycles...)
从 table_1 中删除，其中 date(table_1_TIME) < (选择 T.t_Date from (select max(date(table_1_TIME)) as t_Date from table_1 ) as T)
delete from table_1 where date(table_1_TIME) < (select T.t_Date from (select max(date(table_1_TIME)) as t_Date from table_1 ) as T)