自关联表递归查找所有子节点[MySQL]

背景                                                                                                      

项目中遇到一个需求，要求查处菜单节点的所有节点，在网上查了一下，大多数的方法用到了存储过程，由于线上环境不能随便添加存储过程，

因此在这里采用类似递归的方法对菜单的所有子节点进行查询。

准备                                                                                                        

创建menu表：

 

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `parent_id` int(11) DEFAULT NULL COMMENT '父节点id',
  `menu_name` varchar(128) DEFAULT NULL COMMENT '菜单名称',
  `menu_url` varchar(128) DEFAULT '' COMMENT '菜单路径',
  `status` tinyint(3) DEFAULT '1' COMMENT '菜单状态 1-有效；0-无效',  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `menu` VALUES ('0', null, '菜单0', ' ', '1');INSERT INTO `menu` VALUES ('1', '0', '菜单1', '', '1');INSERT INTO `menu` VALUES ('11', '1', '菜单11', '', '1');INSERT INTO `menu` VALUES ('12', '1', '菜单12', '', '1');INSERT INTO `menu` VALUES ('13', '1', '菜单13', '', '1');INSERT INTO `menu` VALUES ('111', '11', '菜单111', '', '1');INSERT INTO `menu` VALUES ('121', '12', '菜单121', '', '1');INSERT INTO `menu` VALUES ('122', '12', '菜单122', '', '1');INSERT INTO `menu` VALUES ('1221', '122', '菜单1221', '', '1');INSERT INTO `menu` VALUES ('1222', '122', '菜单1222', '', '1');INSERT INTO `menu` VALUES ('12211', '1222', '菜单12211', '', '1');

 得到的目录结构如下图所示：

 

查询                                                                                                           

 先贴出sql语句：

SELECT
    *
FROM
    menu
WHERE
    id IN (
        SELECT
            id
        FROM
            (
                SELECT
                    t1.id,

                IF (
                    find_in_set(parent_id, @pids) > 0,
                    @pids := concat(@pids, ',', id),
                    0
                ) AS ischild
                FROM
                    (
                        SELECT
                            id,
                            parent_id
                        FROM
                            menu t
                        WHERE
                            t. STATUS = 1
                        ORDER BY
                            parent_id,
                            id
                    ) t1,
                    (SELECT @pids := 12) t2
            ) t3
        WHERE
            ischild != 0
    )