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

通过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 menu_id
and FIND_IN_SET(menu_id,sTempPar)
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;

定义游标
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 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 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)

END WHILE;
SET current_roleId_rows=current_roleId_rows+1;

满足必须要有根节点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 实现 向上递归查找父节点并返回树结构的文章就介绍到这了,更多相关Mysql递归查找父节点内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 Mysql 实现向上递归查找父节点并返回树结构的示例代码