Mysql 实现 向上递归查找父节点并返回树结构

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

如果只有叶子,剔除掉; 如果只有根,只显示一个秃顶的根 ;如果既有叶子又有根则显示叶子与根。
测试数据:
如果 传入角色ID【auth_id】:   5,15,25,26,则只查找5,15的所有父节点,因为25,26无根节点

测试数据:

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

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

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

方法三:纯函数实现

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

 欢迎大家提出更优解决方案。谢谢。

posted @ 2022-09-11 15:02  HiEagle  阅读(1540)  评论(4编辑  收藏  举报