Mysql 实现向上递归查找父节点并返回树结构的示例代码

数据运维 2023-04-15 向阳逐梦手机阅读

通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构。如果只有叶子，剔除掉; 如果只有根，只显示一

通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示叶子与根。如果传入角色ID 5，15，25，26，则只查找5，15的所有父节点，因为25，26无根节点

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

　方法一：纯存储过程实现

-- 纯存储过程实现 DELIMITER // -- 如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示 DROP PROCEDURE if EXISTS query_menu_by_authid; CREATE PROCEDURE query_menu_by_authid(IN roleIds varchar(1000))

BEGIN -- 用于判断是否结束循环 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) ; -- 定义设置循环结束标识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 -- 保存当前节点。（从叶节点往根节点找，@r 保存当前到哪个位置了）。@r 初始为要找的节点。 -- _menu_id 当前节点 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 -- 如果该节点没有父节点，则会被置为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 ;

-- 满足必须要有根节点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 的数据集合 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');

方法二：函数+存储过程实现

-- 函数+存储过程实现 -- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示. 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;

-- 循环递归
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&lt;&gt;menu_id
            and FIND_IN_SET(menu_id,sTempPar) &gt; 0;
END WHILE;
RETURN sTemp;

END;

DELIMITER // -- 如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示 DROP PROCEDURE if EXISTS select_menu_by_authids ; CREATE PROCEDURE select_menu_by_authids(IN roleIds varchar(3000))

BEGIN -- 用于判断是否结束循环 declare done int default 0; -- 用于存储结果集 declare menuid varchar(255); declare set_menu_ids VARCHAR(3000); -- 检查是否单叶子节点单叶子节点 sup_menu is not null -- sup_menu 是否为null declare _sup_menu int default -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 的数据集合 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');

方法三：纯函数实现

-- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示. DROP FUNCTION IF EXISTS getParentLists; -- 参数1角色id 字符串逗号隔开；参数2 角色id 个数 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); -- 函数返回的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 = '';

     -- 循环多角色
    WHILE current_roleId_rows &lt; 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 拼接 所有的父节点，重点拼接根节点，根节点置为字符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) &gt; 0;

   END WHILE;
         SET current_roleId_rows=current_roleId_rows+1;

         -- 满足必须要有根节点NULL字符，则表明有根，否则不拼接给返回值
         IF  FIND_IN_SET('NULL',sMenu_ids_by_roleIds) &gt; 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 实现向上递归查找父节点并返回树结构的文章就介绍到这了,更多相关Mysql递归查找父节点内容请搜索每日运维以前的文章或继续浏览下面的相关文章希望大家以后多多支持每日运维！

                        【本文来源：香港将军澳机房 http://www.558idc.com/hk.html 欢迎留下您的宝贵建议】

Mysql&nbsp;实现向上递归查找父节点并返回树结构的示例代码

如何使用Redis和Go实现事件溯源功能

Redis在工作中的十大黄金场景

28.3.9 INFORMATION_SCHEMA COLUMNS_EXTENSIONS 表

MySQL 实现双向复制的方法指南

mysql如何在win10中配置环境变量

Mysql 实现向上递归查找父节点并返回树结构的示例代码