需求：通过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;
欢迎大家提出更优解决方案。谢谢。