如何删除具有递归结构（MySQL）的表的所有行？

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

我的数据库中有一个表，其中每一行都有一个父ID，它是表中另一行的id（表代表树状结构）。我想清空桌子。但是当我表演时

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.

澄清：我想删除整个表的内容，而不是表本身。

5 个解决方案

#1

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

编辑：这里有一个非常好的参考：http：//en.wikipedia.org/wiki/Foreign_key

#2

This should do the trick:

这应该是诀窍：

TRUNCATE table_name;

#3

If you can't change the ON DELETE behavior, you can do this repeatedly until the table is empty:

如果无法更改ON DELETE行为，则可以重复执行此操作，直到表为空：

DELETE FROM table_name WHERE id NOT IN (SELECT parent_id FROM table_name)

#4

First delete the rows that have no children.

首先删除没有子节点的行。

So if the id foreign key is parent_id, do something like:

因此，如果id外键是parent_id，请执行以下操作：

DELETE FROM table_name WHERE parent_id IS NOT NULL;

Then delete the rest:

然后删除其余的：

DELETE FROM table_name;

#5

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.

我遇到了同样的问题，最后在执行delete语句之前将父列设置为null。

UPDATE table_name SET parent_id=null WHERE true;

DELETE FROM table_name WHERE true;

#1