Mysql 實現 向上遞迴查詢父節點並返回樹結構
2022-09-11 18:04:59
需求：通過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;
歡迎大家提出更優解決方案。謝謝。