服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql 实现向上递归查找父节点并返回树结构的示例代码

Mysql 实现向上递归查找父节点并返回树结构的示例代码

2022-11-10 16:34HiEagle Mysql

通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构，今天通过本文给大家介绍Mysql递归查找父节点并返回树结构，感兴趣的朋友一起看看吧

通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示叶子与根。如果 传入角色ID 5，15，25，26，则只查找5，15的所有父节点，因为25，26无根节点

需求：通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构。

如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示叶子与根。测试数据：

Mysql 实现向上递归查找父节点并返回树结构的示例代码

如果 传入角色ID【auth_id】: 5，15，25，26，则只查找5，15的所有父节点，因为25，26无根节点

Mysql 实现向上递归查找父节点并返回树结构的示例代码

测试数据：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for Menu
-- ----------------------------
DROP TABLE IF EXISTS `Menu`;
CREATE TABLE `Menu` (
  `menu_id` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '0',
  `sup_menu` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `auth_id` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
 
-- ----------------------------
-- Records of Menu
-- ----------------------------
BEGIN;
INSERT INTO `Menu` VALUES ('1', NULL, '1');
INSERT INTO `Menu` VALUES ('11', NULL, '11');
INSERT INTO `Menu` VALUES ('12', '11', '12');
INSERT INTO `Menu` VALUES ('13', '11', '13');
INSERT INTO `Menu` VALUES ('14', '12', '14');
INSERT INTO `Menu` VALUES ('15', '12', '15');
INSERT INTO `Menu` VALUES ('16', '13', '16');
INSERT INTO `Menu` VALUES ('17', '13', '17');
INSERT INTO `Menu` VALUES ('2', '1', '2');
INSERT INTO `Menu` VALUES ('22', '21', '26');
INSERT INTO `Menu` VALUES ('25', '22', '25');
INSERT INTO `Menu` VALUES ('3', '1', '3');
INSERT INTO `Menu` VALUES ('4', '2', '4');
INSERT INTO `Menu` VALUES ('5', '2', '5');
INSERT INTO `Menu` VALUES ('6', '3', '6');
INSERT INTO `Menu` VALUES ('7', '3', '7');
COMMIT;
 
SET FOREIGN_KEY_CHECKS = 1;

　方法一：纯存储过程实现

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
-- 纯存储过程实现
DELIMITER //
-- 如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示
DROP PROCEDURE if EXISTS  query_menu_by_authid;
CREATE PROCEDURE query_menu_by_authid(IN roleIds varchar(1000))
 
BEGIN
-- 用于判断是否结束循环
declare done int default 0;
-- 用于存储结果集
declare menuid bigint;
declare temp_menu_ids VARCHAR(3000);
declare temp_sup_menus VARCHAR(3000);
declare return_menu_ids VARCHAR(3000);
 
-- 定义游标
declare idCur cursor for select menu_id from Menu where  FIND_IN_SET(auth_id,roleIds) ;
-- 定义 设置循环结束标识done值怎么改变 的逻辑
declare continue handler for not FOUND set done = 1;
 
 
open idCur ;
FETCH idCur INTO menuid;
-- 临时变量存储menu_id集合
SET temp_menu_ids = '';
-- 返回存储menu_id集合
SET return_menu_ids = '';
 
WHILE done<> 1 DO
--  只查找 单个 auth_id  相关的menu_id
-- 通过authid, 查找出menu_id, sup_menu is null
 
SELECT
GROUP_CONCAT(T2._menu_id) as t_menu_id,
GROUP_CONCAT(T2._sup_menu) as t_sup_menu
into temp_menu_ids,temp_sup_menus
FROM
     (
       SELECT
       -- 保存当前节点。（从叶节点往根节点找，@r 保存当前到哪个位置了）。@r 初始为要找的节点。
       -- _menu_id 当前节点
       DISTINCT @r as _menu_id,
             (
           SELECT
             CASE
                        WHEN sup_menu IS NULL THEN @r:= 'NULL'
                        ELSE @r:= sup_menu
             END
             FROM Menu
             WHERE  _menu_id = Menu.menu_id
             ) AS _sup_menu,
       -- 保存当前的Level
       @l := @l + 1 AS level
       FROM
       ( SELECT @r := menuid, @l := 0
       ) vars, Menu AS temp
        -- 如果该节点没有父节点，则会被置为0
        WHERE  @r <> 0
        ORDER BY @l DESC
       ) T2
      INNER JOIN Menu T1
    ON T2._menu_id = T1.menu_id
 ORDER BY T2.level DESC ;
 
 -- 满足必须要有根节点NULL字符，则表明有根，否则不拼接给返回值
 IF FIND_IN_SET('NULL',temp_sup_menus) > 0  THEN
 SET return_menu_ids = CONCAT(temp_menu_ids,',',return_menu_ids);
 END IF;
 
FETCH idCur INTO menuid;
END WHILE;
CLOSE  idCur;
 
-- 返回指定menu_id 的数据集合
select Menu.menu_id,Menu.sup_menu,Menu.auth_id
FROM Menu
WHERE FIND_IN_SET(menu_id,return_menu_ids)
ORDER BY Menu.menu_id*1 ASC ;
 
END;
//
DELIMITER;
 
CALL  query_menu_by_authid('5,15,25,26');
CALL  query_menu_by_authid('5,17');
CALL  query_menu_by_authid('5,11');

方法二：函数+存储过程实现

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- 函数+存储过程实现
-- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示.
DROP FUNCTION  IF EXISTS `getParentList`;
CREATE FUNCTION `getParentList`(in_menu_id varchar(255))
RETURNS varchar(3000)
BEGIN
    DECLARE sTemp VARCHAR(3000);
    DECLARE sTempPar VARCHAR(3000);
    SET sTemp = '';
    SET sTempPar = in_menu_id;
 
    -- 循环递归
    WHILE sTempPar is not null DO
        -- 判断是否是第一个，不加的话第一个会为空
        IF sTemp != '' THEN
            SET sTemp = concat(sTemp,',',sTempPar);
        ELSE
            SET sTemp = sTempPar;
        END IF;
        SET sTemp = concat(sTemp,',',sTempPar);
        SELECT group_concat(sup_menu)
                INTO sTempPar
                FROM Menu
                where sup_menu<>menu_id
                and FIND_IN_SET(menu_id,sTempPar) > 0;
    END WHILE;
    RETURN sTemp;
END;
 
 
DELIMITER //
-- 如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示
DROP PROCEDURE if EXISTS  select_menu_by_authids ;
CREATE PROCEDURE select_menu_by_authids(IN roleIds varchar(3000))
 
BEGIN
-- 用于判断是否结束循环
declare done int default 0;
-- 用于存储结果集
declare menuid varchar(255);
declare set_menu_ids VARCHAR(3000);
--  检查是否单叶子节点 单叶子节点 sup_menu is not null
-- sup_menu 是否为null
declare _sup_menu int default -1;
 
-- 定义游标
declare idCur cursor for select menu_id from Menu where  FIND_IN_SET(auth_id,roleIds) ;
-- 定义 设置循环结束标识done值怎么改变 的逻辑
declare continue handler for not FOUND set done = 1;
 
OPEN idCur ;
FETCH idCur INTO menuid;
-- 临时变量存储menu_id集合
SET set_menu_ids = '';
 
WHILE done<> 1 DO
SELECT  sup_menu
INTO _sup_menu
FROM Menu
WHERE FIND_IN_SET(menu_id,getParentList(menuid))
ORDER BY sup_menu ASC
LIMIT 1;
 
-- 查找指定角色对应的menu_id ，sup_menu is null 则说明有根，则进行拼接
IF _sup_menu is NULL THEN
SELECT  CONCAT(set_menu_ids, GROUP_CONCAT(menu_id),',') INTO set_menu_ids
FROM Menu
where FIND_IN_SET(menu_id,getParentList(menuid)) ;
END IF;
 
FETCH idCur INTO menuid;
END WHILE;
CLOSE  idCur;
 
-- 返回指定menu_id 的数据集合
SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id
FROM Menu
WHERE FIND_IN_SET(menu_id,set_menu_ids)
ORDER BY Menu.menu_id*1 ASC  ;
 
END ;
//
DELIMITER ;
 
CALL  select_menu_by_authids('5,15,25,26');
CALL  select_menu_by_authids('5,17');
CALL  select_menu_by_authids('5,11');

方法三：纯函数实现

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示.
DROP FUNCTION  IF EXISTS `getParentLists`;
-- 参数1角色id 字符串逗号隔开； 参数2 角色id 个数
CREATE FUNCTION `getParentLists`(in_roleIds varchar(1000),count_roleIds INT)
RETURNS VARCHAR(3000)
BEGIN
    -- 临时存放通过单个角色查找的单个menu_id
        DECLARE sMenu_id_by_roleId VARCHAR(1000);
    -- 临时存放通过单个角色查找的多个menu_id
    DECLARE sMenu_ids_by_roleId VARCHAR(1000);
        -- 临时存放通过多个角色查找的多个menu_id
    DECLARE sMenu_ids_by_roleIds VARCHAR(1000);
        -- 函数返回的menu_id 集合
        DECLARE sReturn_menu_ids VARCHAR(3000);
        -- 当前角色
    DECLARE current_roleId_rows INT DEFAULT 0;
 
        SET sMenu_id_by_roleId = '';
    SET sMenu_ids_by_roleIds = '';
        SET sReturn_menu_ids = '';
 
         -- 循环多角色
        WHILE current_roleId_rows < count_roleIds DO
 
                -- 依次按角色取1条menu_id
                SELECT menu_id
                INTO sMenu_id_by_roleId
                FROM Menu
                WHERE FIND_IN_SET(auth_id, in_roleIds)
                ORDER BY menu_id DESC
                LIMIT current_roleId_rows, 1 ;
 
                SET sMenu_ids_by_roleId = sMenu_id_by_roleId;
        WHILE sMenu_ids_by_roleId IS NOT NULL DO
 
                        -- 判断是否是第一个，不加的话第一个会为空
                        IF sMenu_ids_by_roleIds != ''  THEN
                                SET sMenu_ids_by_roleIds = CONCAT(sMenu_ids_by_roleIds,',',sMenu_ids_by_roleId);
                        ELSE
                                SET sMenu_ids_by_roleIds = sMenu_ids_by_roleId;
                        END IF;
 
                        -- 通过角色id 拼接 所有的父节点，重点拼接根节点，根节点置为字符NULL，用于后面判断是否有根
                        SELECT
                        GROUP_CONCAT(
                        CASE
                        WHEN sup_menu IS NULL THEN  'NULL'
                        ELSE sup_menu
                        END
                        )
                        INTO sMenu_ids_by_roleId
                        FROM Menu
                        WHERE FIND_IN_SET(menu_id,sMenu_ids_by_roleId) > 0;
 
       END WHILE;
             SET current_roleId_rows=current_roleId_rows+1;
 
             -- 满足必须要有根节点NULL字符，则表明有根，否则不拼接给返回值
             IF  FIND_IN_SET('NULL',sMenu_ids_by_roleIds) > 0 THEN
                         SET sReturn_menu_ids = CONCAT(sReturn_menu_ids,',',sMenu_ids_by_roleIds);
             END IF;
 
             -- 清空通过单个角色查到的多个menu_id, 避免重复拼接
             SET sMenu_ids_by_roleIds = '';
   END WHILE;
 
   RETURN sReturn_menu_ids;
END;
 
SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id
FROM Menu
WHERE FIND_IN_SET(menu_id, getParentLists('15,25,5,26',4))
ORDER BY Menu.menu_id+0 ASC;
 
SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id
FROM Menu
WHERE FIND_IN_SET(menu_id, getParentLists('17,5',2))
ORDER BY Menu.menu_id*1 ASC;
 
SELECT Menu.menu_id,Menu.sup_menu,Menu.auth_id
FROM Menu
WHERE FIND_IN_SET(menu_id, getParentLists('11,5',2))
ORDER BY Menu.menu_id*2 ASC;

到此这篇关于Mysql 实现 向上递归查找父节点并返回树结构的文章就介绍到这了,更多相关Mysql递归查找父节点内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/zjp8023/p/16684032.html

  • mysql
  • 查找
  • 递归
  • 树结构

    • 延伸 · 阅读

    精彩推荐
    • Mysql与MSSQL对比学习MYSQL的心得（二）--显示宽度

      与MSSQL对比学习MYSQL的心得（二）--显示宽度

      MYSQL中的整数型数据类型都可以指定显示宽度，而SQLSERVER不行 ...

      hebedich1862020-04-03
    • Mysql如何解决mysql重装失败方法介绍

      如何解决mysql重装失败方法介绍

      相信大家使用MySQL都有过重装的经历，要是重装MySQL基本都是在最后一步通不过，除非重装操作系统，究其原因就是系统里的注册表没有删除干净 ...

      MYSQL教程网3852019-12-11
    • Mysql如何修改MySQL字符集

      如何修改MySQL字符集

      这篇文章主要介绍了如何修改MySQL字符集，帮助大家更好的理解和使用MySQL数据库，感兴趣的朋友可以了解下...

      Noah9968922021-02-03
    • Mysql理解MySQL存储过程和函数

      理解MySQL存储过程和函数

      这篇文章主要帮助大家学习理解MySQL存储过程和函数，感兴趣的小伙伴们可以参考一下 ...

      pursuer.chen4782020-06-02
    • Mysqlmysql将bin-log日志文件转为sql文件的方法

      mysql将bin-log日志文件转为sql文件的方法

      这篇文章主要介绍了mysql将bin-log日志文件转为sql文件的方法，本文给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下...

      下班闪电去打卡6452020-12-28
    • Mysql详解mysql集群：一主多从架构实现

      详解mysql集群：一主多从架构实现

      这篇文章主要介绍了mysql集群一主多从架构实现，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面...

      zzqit5522020-09-24
    • Mysql浅析mysql union和union all

      浅析mysql union和union all

      union 是对数据进行并集操作，不包括重复行，同时进行默认排序而Union all 是对数据进行并集操作，包括重复行，不进行排序，下面给大家详细介绍mysql un...

      动力节点5342020-08-15
    • MysqlMySQL高级查询之与Group By集合使用介绍

      MySQL高级查询之与Group By集合使用介绍

      在MySQL中,你可以获取表达式组合的连接值。你可以使用DISTINCT删去重复值。假若你希望多结果值进行排序，则应该使用 ORDER BY子句 ...

      MYSQL教程网5202020-01-08
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1093 　 1786 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图