PHP MySQL删除父行和子行 [英] PHP MySQL Delete parent and child rows

问题描述

我有1个MySQL表.看起来像这样:

I have 1 MySQL Table. It looks like this:

+---------+-------------+--------+
| item_id |  parent_id  |  Name  |
+---------+-------------+--------+
|   1     |     0       |  Home  |
+---------+-------------+--------+
|   2     |     1       |  Sub   |
+---------+-------------+--------+
|   3     |     2       | SubSub |
+---------+-------------+--------+

如果我删除item_id 1，我也想删除其余的子项，但是我该怎么做呢?

If I DELETE item_id 1, I want to delete the rest of the sub also but how can I do it?

我已经尝试过外键，但是只有在您有2个表的情况下才可以使用?

I have tried the Foreign Key but it works only if you have 2 tables??

我希望有人可以在MySQL中帮助我，也许是PHP?

I hope someone can help me in MySQL maybe PHP?

推荐答案

您绝对可以在MySQL中使用自引用外键(不需要多个表).但是，对于任何类型的外键支持，您都需要使用 MyISAM引擎.

You can, most definitely, use self-referencing foreign keys with MySQL (you don't need multiple tables). However, for any kind of foreign key support, you need to use the InnoDB engine. And my guess is, that you are using the MyISAM engine.

使用InnoDB，您可以创建一个与现有表类似的表，包括自引用外键，如下所示:

With InnoDB you could create a table, similar to what you have already, including the self-referencing foreign key, like this:

CREATE TABLE  `yourTable` (
  `item_id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned default NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY  (`item_id`),
  KEY `FK_parent_id` (`parent_id`),
  CONSTRAINT `FK_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `yourTable` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后，当您发出DELETE语句时，例如:

Then, when you issue a DELETE statement, like:

DELETE FROM `yourTable` WHERE `item_id` = 1;

...它将删除每个parent_id为1的子"行.如果这些子"行中的任何一个都有自己的子项，则它们也将被删除，依此类推(这就是ON DELETE CASCADE的意思).

... it would delete each 'child' row, that has a parent_id of 1 as well. If any of those 'child' rows have children of their own, they'd be deleted too, etc. (that's what the ON DELETE CASCADE means).

这篇关于PHP MySQL删除父行和子行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！