如何在MySQL中删除带子的多级菜单项？

Question

我在MySQL中有无限的级别菜单结构，其中父元素和子元素与列p_id连接。

+----+------+------+----------------+
| id | p_id | sort | name_en        |
+----+------+------+----------------+
|  1 |    0 |    1 | menu-1         |
|  2 |    0 |    2 | menu-2         |
|  3 |    0 |    6 | menu-3         |
|  4 |    2 |    3 | sub-menu-2-1   |
|  5 |    2 |    4 | sub-menu-2-2   |
|  6 |    5 |    5 | sub-menu-2-2-1 |
+----+------+------+----------------|

删除根菜单元素和子菜单元素的最佳实践是什么？

我可以使用PHP递归来实现它，但是它会导致一些查询。

因此，我需要找出是否有可能用一个MySQL查询来完成这个任务。

Answer 1

既然你有name_en，你不能用它来删除行吗？例如,

DELETE FROM `table` WHERE `id` = 2 OR `name_en` LIKE 'sub-menu-2-%'

新方法：

您可以使用带有约束的外键。我创造了你的桌子叫它树列表，

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

然后我添加了一些测试数据，你在问题中得到的数据，

INSERT INTO `treelist` (`item_id`, `parent_id`, `name_en`) VALUES (NULL, NULL, 'Menu 1'), (NULL, NULL, 'Menu 2'), (NULL, NULL, 'Menu 3'), (NULL, 2, 'Sub Menu 2-1'), (NULL, 2, 'Sub Menu 2-2'), (NULL, 5, 'Sub Menu 2-2-1');

现在，当您删除一行时，例如

DELETE FROM `treelist` WHERE `item_id` = 2

它也将删除所有的孩子，大孩子等等。后来这张桌子看起来像，

+----+------+----------------+ 
| id | p_id | name_en        |
+----+------+----------------+ 
|  1 | NULL | Menu 1         | 
|  3 | NULL | Menu 3         |
+----+------+----------------+

Answer 2

删除一行，这是直接子女(而不是孙辈)：

DELETE FROM tablename where id = 1 or p_id = 1;

更新1:

如果您可以自由地向表中添加列，则可以添加root_id并在此基础上轻松地执行删除操作。这不会破坏系统中的任何当前查询，只需运行一次简单脚本即可添加初始数据。

DELETE FROM tablename where id = 1 or root_id = 1;

更新2: --一个非常棒的选项是允许使用相同表的外键。因此，您可以向p_id添加一个引用id的外键，使用on delete cascade，当您删除根目录时，所有的后代也将被删除。我创建了一个测试表，这对我来说非常有用。添加on update cascade也可能是有益的。请记住，p_id和id都需要未签名才能正常工作。

ALTER TABLE tablename ADD CONSTRAINT fk_tablename_id FOREIGN KEY (p_id) references tablename(id) ON DELETE CASCADE;

Answer 3

只有第一个参数中的类别id或父id：

function delete_parent_and_child_subchild($category_id,$all_cate=array())
        {
            if(!is_array($category_id))
            {           
                $this->db->where('parent_id', $category_id);
                $all_cate[]=$category_id;
            }
            else
            {           
                $this->db->where_in('parent_id', $category_id);
            }        
            $get_categories= $this->db->get('newcategory'); 

            if($get_categories->num_rows()>0)
            {
                $categories_vales=$get_categories->result(); 
                $new_subcat = array();
                foreach($categories_vales as $cate_val)
                {
                    $category_id=$cate_val->category_id;
                    array_push($new_subcat,$category_id);
                }
                $all_cate = array_merge($all_cate,$new_subcat);         
                if(count($new_subcat)>0)
                {
                    $this->delete_parent_and_child_subchild($new_subcat,$all_cate);
                }   

            }   
            $this->db->where_in('category_id', $all_cate)->delete('newcategory');
            return true;
        }