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

分享到

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

Mysql 来源：互联网作者：佚名发布时间：2022-09-14 22:58:46 人浏览

摘要

通过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<>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');

方法三：纯函数实现

-- 根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示.

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;

您可能感兴趣的文章 :

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

Tag : mysql(199)递归(10)

深入了解MySQL中的慢查询

一、什么是慢查询什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。具体耗费多久算慢查询呢？这其实因人而异，有些公司慢
MySQL中with rollup的用法及说明

MySQL with rollup的用法当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关
mysql分组统计并求出百分比的方法

mysql分组统计并求出百分比 1、mysql 分组统计并列出百分比 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 SELECT point_id, pname_cn, play_
30种SQL语句优化的方法总结

1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2）应尽量避免在 where 子句中使用!=或操作符
达梦数据库获取SQL实际执行计划的方法

环境说明：操作系统：银河麒麟V10 数据库：DM8 相关关键字：DM数据库、SQL实际执行计划一、set autotrace trace disql下执行set autotrace trace开启
MySQL数据库约束的介绍

基本介绍约束用于确保数据库的数据满足特定的商业规则在mysql中，约束包括：not null，unique，primary key，foreign key 和check5种 1.primary key(主键
MySQL索引的介绍

1. MySQL 索引的最左前缀原则左前缀原则是联合索引在使用时要遵循的原则，查询索引可以使用联合索引的一部分，但是必须从最左侧开始。
windows下Mysql多实例部署的操作方法

当存在多个项目的时候，需要同时部署时，且只有一台服务器时，哪么就需要部署Mysql多个实例，原理很简单，多个mysql服务运行使用不同的
MySQL客户端/服务器运行架构介绍

之前对MySQL的认知只限于会写些SQL，本篇开始进行对MySQL进行深入的学习，记录和整理下自己对MySQL不熟悉的地方。如果有需要可以关注我的
mysql8.0主从复制搭建与配置方案

mysql主从搭建环境：ubuntu20.04.1，mysql：8.0.22。主：192.168.87.3 备：192.168.87.6 安装数据库 1 2 3 sudo apt-get install mysql-server sudo apt-get install mysql