关于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))


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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 214,658评论 6赞 496
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,482评论 3赞 389
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 160,213评论 0赞 350
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,395评论 1赞 288
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,487评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,523评论 1赞 293
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,525评论 3赞 414
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,300评论 0赞 270
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,753评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,048评论 2赞 330
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,223评论 1赞 343
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,905评论 5赞 338
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,541评论 3赞 322
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,168评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,417评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,094评论 2赞 365
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,088评论 2赞 352