如何删除具有递归结构的表（MySQL）的所有行？
我的数据库中有一个表，其中每一行都有一个父 id，它是表中另一行的 id（该表表示树状结构）。我想清空桌子。但是当我执行时
DELETE FROM table_name WHERE true;
出现错误（外键约束）。我该如何清空桌子呢？
澄清：我想删除整个表的内容，而不是表本身。
I have a table in my DB, in which every row has a parent id which is the id of another row in the table (the table represents a tree-like structure). I would like to empty the table. But when I perform
DELETE FROM table_name WHERE true;
I get an error (foreign key constraint). How do I empty the table anyway?
Clarification: I want to delete the whole table's contents, not the tables themselves.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
创建外键关系时，您需要指定
on删除级联。编辑：这里有一个很好的参考：http://en.wikipedia.org/wiki/Foreign_key
When you create your foreign key relationships, you need to specify
on delete cascade.EDIT: There's a pretty good reference right here: http://en.wikipedia.org/wiki/Foreign_key
这应该可以解决问题：
This should do the trick:
如果您无法更改 ON DELETE 行为，您可以重复执行此操作，直到表为空：
If you can't change the ON DELETE behavior, you can do this repeatedly until the table is empty:
首先删除没有子项的行。
因此，如果 id 外键是parent_id，请执行以下操作：
然后删除其余部分：
First delete the rows that have no children.
So if the id foreign key is parent_id, do something like:
Then delete the rest:
一个旧线程，但我会发布我的答案只是为了帮助任何人阅读这个问题。
我遇到了同样的问题，最终在执行删除语句之前将父列设置为 null。
An old thread but I'll post my answer just to help anyone reading this question.
I had the same issue and I ended up setting the parent column to null before executing the delete statement.