关于mysql 递归查找父节点的所有子节点改进版

作者：pany

时间：2019-3-12 21:36

“收获之前你一定得先付出，这是铁律”

一、背景

在项目中，需要查询父节点下所有的子节点，我的同事是利用递归查询的，我发现他是参考网上的例子写的。

地址：https://www.cnblogs.com/rainydayfmb/p/8028868.html（其实这个作者写的还算比较仔细）

我在重构他的模块时，发现这个递归查询并不能将所有的字节点都查询出来，起初我是怀疑是sql的问题，于是按照上面的案例写了一遍，发现sql能够查询出所有子节点，也就证明sql没问题。于是我怀疑数据有问题，于是将数据仔细梳理好几遍，发现数据也没有问题。这下我凌乱了，sql 和数据都没问题，到底是什么造成了这么诡异的事情呢？

之所以走了这么多弯路，还是自己想图省事，懒得去分析sql，走了这么多弯路之后，我不能忍受了，于是我决定好好分析下案例中的sql。

二、基于第一组数据分析（类似于案例中的数据）

表结构：（由于演示，表结构创建的比较随意啊）

DROP TABLE IF EXISTS `re_menu`;

CREATE TABLE `re_menu` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',

`parent_id` int(11) NULL DEFAULT NULL COMMENT '父节点id',

`menu_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称',

`menu_url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜单路径',

`status` tinyint(3) NULL DEFAULT 1 COMMENT '菜单状态 1-有效；0-无效',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 12223 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

数据：

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (1, 0, '菜单1', '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (11, 1, '菜单11', '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (111, 11, '菜单111', '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (122, 11, '菜单122', '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (1111, 111, '菜单12211', '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (1221, 122, '菜单1221', '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (12221, 1221, NULL, '', 1);

INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (12222, 1221, NULL, '', 1);

## sql 分析：

select

t3. id

from (

select

t1.id,

if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat( @pids, ',' , t1.id), 0) as ischild

from

(

select t.id, t.parent_id from re_menu t where t.status = 1 order by t.parent_id, t.id

) t1,

(select @pids:= '目标节点id' id) t2

) t3 where t3.ischild != '0'

先关注 t1 ,它是元数据，查询的是菜单表状态是'1'的所有数据，如下图；

然后再看t2,它是给变量@pids 赋值 '目标节点id'；我们再看

if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat( @pids, ',' , t1.id), 0) as ischild

第一条记录，判断 @pids（也就是目标节点的id，我们假如它是1）是否包含第一条记录的 parent_id（也就是0），显然不包括，IF(expr1,expr2,expr3)判断如果expr1 条件不满足，执行expr3，也就是0；

再看第二条记录，此时的@pids 仍然是1，判断@pids 是否包含第二条记录的parent_id（也就是1），显然包含，那么执行expr2， @pids:= concat( @pids, ',' , t1.id)，@pids 拼上' ,'再拼上第二条记录的id，也就是11，结果是"1,11 "，赋值给@pids，后面的依次这样分析，确实能把所有的子节点都查询出来，如下图：

（注意： = 和 := 的区别， = 只是在set和update的时候才是赋值，而 := 除了在set和update时是赋值，select 的时候也是）

三、基于第二组数据分析

表结构：

DROP TABLE IF EXISTS `re_menu_new`;

CREATE TABLE `re_menu_new` (

`id` varchar(111) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜单id',

`parent_id` varchar(111) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父节点id',

`menu_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称',

`menu_url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜单路径',

`status` tinyint(3) NULL DEFAULT 1 COMMENT '菜单状态 1-有效；0-无效',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

数据：
INSERT INTO `re_menu_new` VALUES ('2001655aa8054fa4841aa192baf45c6b', 'ROOT_FUNCTION_1', '子功能', '', 1);

INSERT INTO `re_menu_new` VALUES ('4cfe206fe97711e88e78fa163e66a663', 'ROOT_FUNCTION_0', '子功能', '', 1);

INSERT INTO `re_menu_new` VALUES ('7a0d9c0d6b164d128dc4e9fa60261169', '4cfe206fe97711e88e78fa163e66a663', '子功能', '', 1);

INSERT INTO `re_menu_new` VALUES ('8cc6b43beae311e88e78fa163e66a663', 'ROOT_FUNCTION_0', '子功能', '', 1);

INSERT INTO `re_menu_new` VALUES ('ROOT', '', '根节点', '', 1);

INSERT INTO `re_menu_new` VALUES ('ROOT_AREA_MENU', 'ROOT', '菜单节点', '', 1);

INSERT INTO `re_menu_new` VALUES ('ROOT_FUNCTION_0', 'ROOT_AREA_MENU', '功能1', '', 1);

INSERT INTO `re_menu_new` VALUES ('ROOT_FUNCTION_1', 'ROOT_AREA_MENU', '功能2', '', 1);

## sql 分析：

select

t3. id

from (

select

t1.id,

if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat( @pids, ',' , t1.id), 0) as ischild

from

(

select t.id, t.parent_id from re_menu_new t where t.status = 1 order by t.parent_id, t.id

) t1,

(select @pids:= 'ROOT' id) t2

) t3 where t3.ischild != '0'

跟上面一样，我们先看t1，如下图；

然后再看t2,它是给变量@pids 赋值 'ROOT'；我们再看

if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat( @pids, ',' , t1.id), 0) as ischild

第一条记录，判断 @pids（ROOT）是否包含第一条记录的 parent_id（无），显然不包括，IF(expr1,expr2,expr3)判断如果expr1 条件不满足，执行expr3，也就是0；

再看第二条记录，此时的@pids 仍然是ROOT，判断@pids 是否包含第二条记录的parent_id（也就是4cfe206fe97711e88e78fa163e66a663），显然不包含，那么执行expr3，返回0，而 where t3.ischild != '0' 这里看到 0是被排除的，被认为不是子节点，但是从关系可以看出id是7a0d9c0d6b164d128dc4e9fa60261169 的节点很明显是ROOT下的叶子节点。

问题在哪呢？

由于执行 select t.id, t.parent_id from re_menu_new t where t.status = 1 order by t.parent_id, t.id 的排序导致了问题，因为排序之后，我们parent_id的顺序不在是从ROOT依次向下排列了，而是中间多了一个4cfe206fe97711e88e78fa163e66a663。所以这个sql的成败关键在于元数据的排序。如果我们将第一组数据的元素顺序改成倒序，如下：

select t.id, t.parent_id from re_menu t where t.status = 1 order by t.parent_id DESC

我们再执行查询子节点的sql，也会出错，如下：

我总结这么多，其实就像说明这个sql的成败关键，如果你的id和pid的关系是序列这种，使用上面的sql完全没问题，但是如果类似第二种数据，可能就不适合了。我说的可能有点啰嗦，非常抱歉，因为我以前当过老师，喜欢讲东西多举例子。

那遇到这类问题是不是没有解决方案了呢？并不是，还有其他的写法，网上也有很多例子。

四、解决方案

其实还有其他的方案，主要我们根据具体环境去用，有些环境下，我们是不能随便添加存储过程的。

下面的解决方案是利用函数的：(注意函数的创建用户要根据具体的改下，DEFINER=`root`@`% 这个root是指定的用户，小白要注意)

DELIMITER //

CREATE DEFINER=`root`@`%` FUNCTION `getChildLst`(rootId varchar(50)) RETURNS varchar(1000) CHARSET utf8

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempChd VARCHAR(1000);

SET sTemp = '$';

SET sTempChd =rootId;

WHILE sTempChd is not null DO

SET sTemp = concat(sTemp,',',sTempChd);

SELECT group_concat(id) INTO sTempChd FROM re_menu_new where parent_id<>id and FIND_IN_SET(parent_id,sTempChd)>0;

END WHILE;

RETURN sTemp;

END//

创建完成之后，使用下面的查询：

select

r. id,r.parent_id

from re_menu_new r

where FIND_IN_SET(r.id,(select `getChildLst`('ROOT') as id))

所有的结果都查询出来了。