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) > 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;
到此这篇关于Mysql 实现 向上递归查找父节点并返回树结构的文章就介绍到这了,更多相关Mysql递归查找父节点内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
原文出处:https://www.cnblogs.com/zjp8023/p/16684032.html
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 这篇文章主要用实例讲解C#递归算法的概念以及用法,文中代码非常详细,帮助大家更好的参考和学习,感兴趣的朋友可以了解下...2020-06-25
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11