Mysql 實現(xiàn)向上遞歸查找父節(jié)點并返回樹結構的示例代碼
更新時間：2022年09月11日 15:11:24 作者：HiEagle
通過mysql 8.0以下版本實現(xiàn)，一個人多角色id,一個角色對應某個節(jié)點menu_id，根節(jié)點的父節(jié)點存儲為NULL, 向上遞歸查找父節(jié)點并返回樹結構，今天通過本文給大家介紹Mysql遞歸查找父節(jié)點并返回樹結構，感興趣的朋友一起看看吧
通過mysql 8.0以下版本實現(xiàn)，一個人多角色id,一個角色對應某個節(jié)點menu_id，根節(jié)點的父節(jié)點存儲為NULL, 向上遞歸查找父節(jié)點并返回樹結構。如果只有葉子，剔除掉; 如果只有根，只顯示一個禿頂?shù)母?；如果既有葉子又有根則顯示葉子與根。如果 傳入角色ID 5，15，25，26，則只查找5，15的所有父節(jié)點，因為25，26無根節(jié)點
需求：通過mysql 8.0以下版本實現(xiàn)，一個人多角色id,一個角色對應某個節(jié)點menu_id，根節(jié)點的父節(jié)點存儲為NULL, 向上遞歸查找父節(jié)點并返回樹結構。
如果只有葉子，剔除掉; 如果只有根，只顯示一個禿頂?shù)母?；如果既有葉子又有根則顯示葉子與根。測試數(shù)據(jù)：
如果 傳入角色ID【auth_id】: 5，15，25，26，則只查找5，15的所有父節(jié)點，因為25，26無根節(jié)點
測試數(shù)據(jù)：
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;方法一：純存儲過程實現(xiàn)
-- 純存儲過程實現(xiàn)
DELIMITER //
-- 如果只有葉子，剔除掉; 如果只有根，只顯示一個禿頂?shù)母?；如果既有葉子又有根則顯示
DROP PROCEDURE if EXISTS query_menu_by_authid;
CREATE PROCEDURE query_menu_by_authid(IN roleIds varchar(1000))
BEGIN
-- 用于判斷是否結束循環(huán)
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) ;
-- 定義 設置循環(huán)結束標識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
-- 保存當前節(jié)點。（從葉節(jié)點往根節(jié)點找，@r 保存當前到哪個位置了）。@r 初始為要找的節(jié)點。
-- _menu_id 當前節(jié)點
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
-- 如果該節(jié)點沒有父節(jié)點，則會被置為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 ;
-- 滿足必須要有根節(jié)點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 的數(shù)據(jù)集合
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');方法二：函數(shù)+存儲過程實現(xiàn)
-- 函數(shù)+存儲過程實現(xiàn)
-- 根據(jù)葉子節(jié)點查找所有父節(jié)點及其本身節(jié)點。如果只有葉子，剔除掉; 如果只有根，只顯示一個禿頂?shù)母?；如果既有葉子又有根則顯示.
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;
-- 循環(huán)遞歸
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 //
-- 如果只有葉子，剔除掉; 如果只有根，只顯示一個禿頂?shù)母?；如果既有葉子又有根則顯示
DROP PROCEDURE if EXISTS select_menu_by_authids ;
CREATE PROCEDURE select_menu_by_authids(IN roleIds varchar(3000))
BEGIN
-- 用于判斷是否結束循環(huán)
declare done int default 0;
-- 用于存儲結果集
declare menuid varchar(255);
declare set_menu_ids VARCHAR(3000);
-- 檢查是否單葉子節(jié)點 單葉子節(jié)點 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) ;
-- 定義 設置循環(huán)結束標識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 的數(shù)據(jù)集合
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');方法三：純函數(shù)實現(xiàn)
-- 根據(jù)葉子節(jié)點查找所有父節(jié)點及其本身節(jié)點。如果只有葉子，剔除掉; 如果只有根，只顯示一個禿頂?shù)母?；如果既有葉子又有根則顯示.
DROP FUNCTION IF EXISTS `getParentLists`;
-- 參數(shù)1角色id 字符串逗號隔開； 參數(shù)2 角色id 個數(shù)
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);
-- 函數(shù)返回的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 = '';
-- 循環(huán)多角色
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 拼接 所有的父節(jié)點，重點拼接根節(jié)點，根節(jié)點置為字符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;
-- 滿足必須要有根節(jié)點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 實現(xiàn) 向上遞歸查找父節(jié)點并返回樹結構的文章就介紹到這了,更多相關Mysql遞歸查找父節(jié)點內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
win10下安裝mysql8.0.23 及 “服務沒有響應控制功能”問題解決辦法
這篇文章主要介紹了win10下安裝mysql8.0.23 及 “服務沒有響應控制功能”問題解決辦法,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
這篇文章主要介紹了Mysql select語句設置默認值的方法,需要的朋友可以參考下2014-05-05
這篇文章主要介紹了MySQL無法啟動的解決辦法的相關資料,希望通過本文大家能解決數(shù)據(jù)庫不能啟動的問題，需要的朋友可以參考下2017-09-09
全面解析MySQL?Explain如何優(yōu)化SQL查詢性能
在?MySQL?中，EXPLAIN?關鍵字可以幫助我們分析查詢執(zhí)行計劃，從而優(yōu)化查詢性能，所以本文就來和大家詳細講講Explain是如何優(yōu)化SQL查詢性能的2023-05-05
有些時候，子查詢可以被更有效的連接替代，方法僅供參考，需要的朋友可以了解下。2017-10-10
在本篇內容里小編給各位分享的是一篇關于mysql不是內部命令的錯誤解決方案，需要的朋友們可以學習下。2020-08-08
MySQL 綠色版安裝方法教程，需要的朋友可以參考下。2012-01-01
MySQL數(shù)據(jù)庫優(yōu)化之索引實現(xiàn)原理與用法分析
這篇文章主要介紹了MySQL數(shù)據(jù)庫優(yōu)化之索引實現(xiàn)原理與用法,結合實例形式分析了mysql數(shù)據(jù)庫優(yōu)化操作的索引原理、具體實現(xiàn)與相關操作注意事項,需要的朋友可以參考下2020-01-01
這篇文章主要為大家介紹了解決Mysql?Binlog文件太大導致無法解析問題，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-11-11
這篇文章主要介紹了mysql 詳解隔離級別操作過程(cmd)的相關資料,需要的朋友可以參考下2017-01-01