如何在邻接列表模型中删除父级及其子级？

+--------+---------+-----------+
|   id   | title   | parent_id |
+--------+---------+-----------+
|    1   | Lvl-1   |   null    |
+--------+---------+-----------+
|    2   | Lvl-11  |     1     |
+--------+---------+-----------+
|    3   | Lvl-111 |     2     |
+--------+---------+-----------+
|    4   | Lvl-12  |     4     |
+--------+---------+-----------+

What I'm trying to do is, when I delete the row with id 1, it will delete all its child rows (the rows with id 2 and 3 in the example table). The row with ID 2 should be deleted because its parent_id is 1, and the row with ID 3 should be deleted because its parent_id is 2.

我要做的是，当我删除id为1的行时，它将删除其所有子行（示例表中id为2和3的行）。应删除ID为2的行，因为其parent_id为1，应删除ID为3的行，因为其parent_id为2。

I'm using the MyISAM engine. Is it possible to delete the row and all child rows with just one query?

我正在使用MyISAM引擎。是否可以仅使用一个查询删除行和所有子行？

2 个解决方案

#1

No, not with myISAM. If you set this up with constraints in InnoDB, it may work.

不，不是myISAM。如果你在InnoDB中使用约束进行设置，它可能会起作用。

But by doing this you're using a table-oriented data store to manage a hierarchical data structure. You would be wise to handle this kind of thing explicitly in your application program.

但通过这样做，您将使用面向表的数据存储来管理分层数据结构。在应用程序中明确地处理这种事情是明智的。

#2

~~What you need is a DELETE TRIGGER, see 12.1.11. CREATE TRIGGER Syntax from the MySQL Documentation.~~

你需要的是DELETE TRIGGER，见12.1.11。从MySQL文档中创建TRIGGER语法。

Triggers can't perform operations on the same table that it's triggering the event on. I'd recommend you either use a scheduled event, create a stored procedure, or you handle it in the application handling the MySQL.

触发器无法在触发事件的同一个表上执行操作。我建议您使用预定事件，创建存储过程，或者在处理MySQL的应用程序中处理它。

#1