【JAVA秒会技术之搞定数据库递归树】Mysql快速实现递归树状查询

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: Mysql快速实现递归树状查询 【前言】今天一个好朋友问我的这个问题,以前也没有用到过,恰好有时间,就帮他研究了一下,纯属“现学现卖”,正好在过程中,自己也能学习一下!个人感觉,其实一点也不难,不过是“闻道有先后”,我们是“后”罢了。按照我的习惯,学完东西,总要总结一下嘛,也当做一个备忘录了。   具体需求就不描述了,简而言之,归结为两个: 1.如何通过子节点(cid)加载出所

Mysql快速实现递归树状查询

【前言】今天一个好朋友问我的这个问题,以前也没有用到过,恰好有时间,就帮他研究了一下,纯属“现学现卖”,正好在过程中,自己也能学习一下!个人感觉,其实一点也不难,不过是“闻道有先后”,我们是“后”罢了。按照我的习惯,学完东西,总要总结一下嘛,也当做一个备忘录了。

 

具体需求就不描述了,简而言之,归结为两个:

1.如何通过子节点(cid)加载出所有的父节点(pid)?

2.如何通过父节点(pid)加载出所有的子节点(cid)?

废话不多说,直接上简易教程:

1.创建一个测试表

CREATE TABLE treeNodes
(
 id INT PRIMARY KEY,
     nodename VARCHAR(20),
 pid INT
);

2.编写测试数据

INSERT INTO treeNodes VALUES
(1,'A',0),(2,'B',1),(3,'C',1),
(4,'D',2),(5,'E',2),(6,'F',2),
(7,'G',3),(8,'H',6),(9,'I',0),
(10,'J',8),(11,'K',8),(12,'L',8),
(13,'M',9),(14,'N',12),(15,'O',12),
(16,'P',15),(17,'Q',15);

3.实际树型结构

 

 1:A
  +-- 2:B
  |    +-- 4:D
  |    +-- 5:E
  |    +-- 6:F
  |    |    +-- 8:H
  |    |    |    +-- 10:J
  |    |    |    +-- 11:K 
  |    |    |    +-- 12:L
  |    |    |    |    +-- 14:N
  |    |    |    |    +-- 15:O
  |    |    |    |    |    +-- 16:P
  |    |    |    |    |    +-- 17:Q
  +-- 3:C
  |    +-- 7:G

 9:I
  +-- 13:M


4.创建通过子节点(cid)加载出所有的父节点(pid)的存储函数

DELIMITER //    
CREATE FUNCTION `getParentList`(rootId INT) 
     RETURNS CHAR(255) 
     BEGIN 
		 DECLARE fid INT DEFAULT 1;
		 DECLARE str CHAR(255) DEFAULT rootId;
	     WHILE rootId IS NOT NULL DO 
			SET fid=(SELECT pid FROM treenodes  WHERE rootId=id); 
			 IF fid > 0 THEN  
				 SET str=CONCAT(str,',',fid);   
				 SET rootId=fid;  
			 ELSE 
				SET rootId=fid;  
			 END IF;  
		END WHILE;
		RETURN str;
     END  //

5.测试(找出id=7的所有父节点)  

SELECT getParentList(7); 【结果:7,3,1】

6.创建 通过子节点(cid)加载出所有的父节点(pid)的存储函数  

DELIMITER // 
CREATE FUNCTION `getChildList`(rootId varchar(100)) 
	RETURNS varchar(2000)
	BEGIN 
		DECLARE str varchar(2000);
		DECLARE cid varchar(100); 
		SET str = '$'; 
		SET cid = rootId; 
		WHILE cid is not null DO 
			SET str = concat(str, ',', cid); 
			SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(pid, cid) > 0; 
		END WHILE; 
		RETURN str; 
	END //

7.测试(找出id=1的所有子节点)

SELECT getChildList(1); 【结果:$,1,2,3,4,5,6,7,8,10,11,12,14,15,16,17】

8.补充:以上一组简单的教程,主要也是总结于网上的各种资料,其实我个人觉得,对于程序员来说“复制,粘贴”没有什么好不耻的,重点是在于:“复制,粘贴”别人的东西后,要学会分析与进一步的思考,并且能够举一反三,最好之后还能认真的总结一下。这样,别人的东西,才变成了你的东西。并且,你可能比之前那个人做的更好;如此下去,才是一个良性循环!

下面我们就简单分析一下这个存储函数的结构:

 

之后,我又根据朋友的特殊需求,进行了修改,主要是我朋友想查出的并不是各种id,而是希望通过一个子节点的id,直接查出所有父级的名称,便于展示。

DELIMITER // 
CREATE FUNCTION `getParentList`(rootId VARCHAR(100)) 
	RETURNS VARCHAR(1000) 
BEGIN 
	DECLARE parentId VARCHAR(100) DEFAULT ''; 
	DECLARE str VARCHAR(1000) DEFAULT ''; 
	DECLARE parentName VARCHAR(100) DEFAULT ''; 
	SET str = (SELECT budget_account_name FROM pms_budget_account WHERE id = rootId);
	WHILE rootId IS NOT NULL  DO 
		SET parentId =(SELECT parent_id FROM pms_budget_account WHERE id = rootId);
		IF parentId IS NOT NULL THEN 		
			SET parentName = (SELECT budget_account_name FROM pms_budget_account WHERE id = parentId);
			
			SET str = CONCAT(str, ',', parentName); 
			SET rootId = parentId; 
		ELSE 
			SET rootId = parentId; 
		END IF; 
	END WHILE; 
	RETURN str;
END	 //
最后进行测试,达到了朋友想要的预习效果:

 

至此,才是一个完整的学习过程,也是我常用的学习方式:

碰到难题

---> 心态放平,不要怕,暗示自己“一定能解决

---> 各种渠道获取能解决问题的资源(Google/百度,找项目中类似问题参考)

---> 看懂学会别人的东西

---> 深度分析研究实质性原理(一系列连锁知识的快速串烧)

---> 结合自身实际,进行优化变成自己的东西

---> 总结,写出来(使知识更加系统化,回顾加深印象,备忘)

 

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6天前
|
SQL 安全 Java
MyBatis-Plus条件构造器:构建安全、高效的数据库查询
MyBatis-Plus 提供了一套强大的条件构造器(Wrapper),用于构建复杂的数据库查询条件。Wrapper 类允许开发者以链式调用的方式构造查询条件,无需编写繁琐的 SQL 语句,从而提高开发效率并减少 SQL 注入的风险。
9 1
MyBatis-Plus条件构造器:构建安全、高效的数据库查询
|
2天前
|
存储 缓存 固态存储
怎么让数据库查询更快
【10月更文挑战第28天】
7 2
|
4天前
|
存储 缓存 关系型数据库
怎么让数据库查询更快
【10月更文挑战第25天】通过以上综合的方法,可以有效地提高数据库查询的速度,提升应用程序的性能和响应速度。但在优化过程中,需要根据具体的数据库系统、应用场景和数据特点进行合理的调整和测试,以找到最适合的优化方案。
|
5天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
6天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
38 2
|
5天前
|
存储 关系型数据库 MySQL
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
96 1
|
6天前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
40 1
|
9天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
38 4
|
6天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
18 1
|
3天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
18 0