Mysql怎么實(shí)現(xiàn)向上遞歸查找父節(jié)點(diǎn)并返回樹結(jié)構(gòu)

這篇“Mysql怎么實(shí)現(xiàn)向上遞歸查找父節(jié)點(diǎn)并返回樹結(jié)構(gòu)”文章的知識(shí)點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價(jià)值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“Mysql怎么實(shí)現(xiàn)向上遞歸查找父節(jié)點(diǎn)并返回樹結(jié)構(gòu)”文章吧。

通過mysql 8.0以下版本實(shí)現(xiàn)，一個(gè)人多角色id,一個(gè)角色對應(yīng)某個(gè)節(jié)點(diǎn)menu_id，根節(jié)點(diǎn)的父節(jié)點(diǎn)存儲(chǔ)為NULL, 向上遞歸查找父節(jié)點(diǎn)并返回樹結(jié)構(gòu)。如果只有葉子，剔除掉; 如果只有根，只顯示一個(gè)禿頂?shù)母?；如果既有葉子又有根則顯示葉子與根。如果 傳入角色I(xiàn)D 5，15，25，26，則只查找5，15的所有父節(jié)點(diǎn)，因?yàn)?5，26無根節(jié)點(diǎn)

需求：通過mysql 8.0以下版本實(shí)現(xiàn)，一個(gè)人多角色id,一個(gè)角色對應(yīng)某個(gè)節(jié)點(diǎn)menu_id，根節(jié)點(diǎn)的父節(jié)點(diǎn)存儲(chǔ)為NULL, 向上遞歸查找父節(jié)點(diǎn)并返回樹結(jié)構(gòu)。

如果只有葉子，剔除掉; 如果只有根，只顯示一個(gè)禿頂?shù)母?；如果既有葉子又有根則顯示葉子與根。測試數(shù)據(jù)：

如果 傳入角色I(xiàn)D【auth_id】: 5，15，25，26，則只查找5，15的所有父節(jié)點(diǎn)，因?yàn)?5，26無根節(jié)點(diǎn)

測試數(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;

　方法一：純存儲(chǔ)過程實(shí)現(xiàn)

-- 純存儲(chǔ)過程實(shí)現(xiàn)
DELIMITER //
-- 如果只有葉子，剔除掉; 如果只有根，只顯示一個(gè)禿頂?shù)母?nbsp;；如果既有葉子又有根則顯示
DROP PROCEDURE if EXISTS  query_menu_by_authid;
CREATE PROCEDURE query_menu_by_authid(IN roleIds varchar(1000))

BEGIN
-- 用于判斷是否結(jié)束循環(huán)
declare done int default 0;
-- 用于存儲(chǔ)結(jié)果集
declare menuid bigint;
declare temp_menu_ids VARCHAR(3000);
declare temp_sup_menus VARCHAR(3000);
declare return_menu_ids VARCHAR(3000);

-- 定義游標(biāo)
declare idCur cursor for select menu_id from Menu where  FIND_IN_SET(auth_id,roleIds) ;
-- 定義 設(shè)置循環(huán)結(jié)束標(biāo)識(shí)done值怎么改變 的邏輯
declare continue handler for not FOUND set done = 1;


open idCur ;
FETCH idCur INTO menuid;
-- 臨時(shí)變量存儲(chǔ)menu_id集合
SET temp_menu_ids = '';
-- 返回存儲(chǔ)menu_id集合
SET return_menu_ids = '';

WHILE done<> 1 DO
--  只查找 單個(gè) auth_id  相關(guān)的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
       -- 保存當(dāng)前節(jié)點(diǎn)。（從葉節(jié)點(diǎn)往根節(jié)點(diǎn)找，@r 保存當(dāng)前到哪個(gè)位置了）。@r 初始為要找的節(jié)點(diǎn)。
       -- _menu_id 當(dāng)前節(jié)點(diǎn)
       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,
       -- 保存當(dāng)前的Level
       @l := @l + 1 AS level
       FROM
       ( SELECT @r := menuid, @l := 0
       ) vars, Menu AS temp
        -- 如果該節(jié)點(diǎn)沒有父節(jié)點(diǎn)，則會(huì)被置為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é)點(diǎn)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ù)+存儲(chǔ)過程實(shí)現(xiàn)

-- 函數(shù)+存儲(chǔ)過程實(shí)現(xiàn)
-- 根據(jù)葉子節(jié)點(diǎn)查找所有父節(jié)點(diǎn)及其本身節(jié)點(diǎn)。如果只有葉子，剔除掉; 如果只有根，只顯示一個(gè)禿頂?shù)母?nbsp;；如果既有葉子又有根則顯示.
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
        -- 判斷是否是第一個(gè)，不加的話第一個(gè)會(huì)為空
        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 //
-- 如果只有葉子，剔除掉; 如果只有根，只顯示一個(gè)禿頂?shù)母?nbsp;；如果既有葉子又有根則顯示
DROP PROCEDURE if EXISTS  select_menu_by_authids ;
CREATE PROCEDURE select_menu_by_authids(IN roleIds varchar(3000))

BEGIN
-- 用于判斷是否結(jié)束循環(huán)
declare done int default 0;
-- 用于存儲(chǔ)結(jié)果集
declare menuid varchar(255);
declare set_menu_ids VARCHAR(3000);
--  檢查是否單葉子節(jié)點(diǎn) 單葉子節(jié)點(diǎn) sup_menu is not null
-- sup_menu 是否為null
declare _sup_menu int default -1;

-- 定義游標(biāo)
declare idCur cursor for select menu_id from Menu where  FIND_IN_SET(auth_id,roleIds) ;
-- 定義 設(shè)置循環(huán)結(jié)束標(biāo)識(shí)done值怎么改變 的邏輯
declare continue handler for not FOUND set done = 1;

OPEN idCur ;
FETCH idCur INTO menuid;
-- 臨時(shí)變量存儲(chǔ)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;

-- 查找指定角色對應(yīng)的menu_id ，sup_menu is null 則說明有根，則進(jìn)行拼接
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ù)實(shí)現(xiàn)

-- 根據(jù)葉子節(jié)點(diǎn)查找所有父節(jié)點(diǎn)及其本身節(jié)點(diǎn)。如果只有葉子，剔除掉; 如果只有根，只顯示一個(gè)禿頂?shù)母?nbsp;；如果既有葉子又有根則顯示.
DROP FUNCTION  IF EXISTS `getParentLists`;
-- 參數(shù)1角色id 字符串逗號(hào)隔開； 參數(shù)2 角色id 個(gè)數(shù)
CREATE FUNCTION `getParentLists`(in_roleIds varchar(1000),count_roleIds INT)
RETURNS VARCHAR(3000)
BEGIN
    -- 臨時(shí)存放通過單個(gè)角色查找的單個(gè)menu_id
        DECLARE sMenu_id_by_roleId VARCHAR(1000);
    -- 臨時(shí)存放通過單個(gè)角色查找的多個(gè)menu_id
    DECLARE sMenu_ids_by_roleId VARCHAR(1000);
        -- 臨時(shí)存放通過多個(gè)角色查找的多個(gè)menu_id
    DECLARE sMenu_ids_by_roleIds VARCHAR(1000);
        -- 函數(shù)返回的menu_id 集合
        DECLARE sReturn_menu_ids VARCHAR(3000);
        -- 當(dāng)前角色
    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

                        -- 判斷是否是第一個(gè)，不加的話第一個(gè)會(huì)為空
                        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é)點(diǎn)，重點(diǎn)拼接根節(jié)點(diǎn)，根節(jié)點(diǎn)置為字符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é)點(diǎn)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;

             -- 清空通過單個(gè)角色查到的多個(gè)menu_id, 避免重復(fù)拼接
             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;

以上就是關(guān)于“Mysql怎么實(shí)現(xiàn)向上遞歸查找父節(jié)點(diǎn)并返回樹結(jié)構(gòu)”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對大家有幫助，若想了解更多相關(guān)的知識(shí)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道。