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

技术分享 4年前 (2022-09-12) 0 999+

关注

需求：通过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;