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

吾爱主题阅读：124 2022-11-10 16:34:00 评论：0

通过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无根节点

测试数据：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 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 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 -- 纯存储过程实现 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' );

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 -- 函数+存储过程实现 -- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示. 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) > 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; -- 定义游标 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' );

方法三：纯函数实现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 -- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示. 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) > 0; 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递归查找父节点内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/zjp8023/p/16684032.html

可以去百度分享获取分享代码输入这里。

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。