Mysql树形表的两种查询方案(递归与自连接)
作者:mmseoamin日期:2023-12-13

你有没有遇到过这样一种情况:

一张表就实现了一对多的关系,并且表中每一行数据都存在“爷爷-父亲-儿子-…”的联系,这也就是所谓的树形结构

Mysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第1张

对于这样的表很显然想要通过查询来实现价值绝对是不能只靠select * from table 来实现的,下面提供两种解决方案:

1.自连接

inner join 关键可以实现多种分类的查询,其实SQL很简单

SELECT
	one.id one_id,
	one.label one_label,
	two.id two_id,
	two.label two_label
FROM
	course_category one
	INNER JOIN course_category two ON two.parentid=one.id
	INNER JOIN course_category three ON three.parentid=two.id
	WHERE one.id='1' AND one.is_show='1' AND two.is_show='1'
	ORDER BY one.orderby,two.orderby

也是规规矩矩的就查出一整棵树

Mysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第2张

这种查询的原则就是通过parentId去实现,“爷爷找爸爸,爸爸找儿子,儿子找孙子”,下面来逐帧慢放:

1.oneMysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第3张

2.one,two

Mysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第4张

3.one,two,three

Mysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第5张

可以看到,只有在树的层级确定的情况下我才能选择性的去自连接子表,某种意义上来讲这种方法存在弊端,我要是insert进去层级更低的新子节点那我的sql就得改变,从而就造成了一个“动一发而牵全身”的硬编码问题,实在是不够稳妥!

2.递归!

向上递归

首先声明,如果mysql的版本低于8是不支持递归查询的函数的!

下面来看一下如何用递归优雅的实现,从树根查到树顶:

先来看一个简单的Demo

	with RECURSIVE t1 AS(
		SELECT 1 AS n
		union all
		SELECT n+1 FROM t1 WHERE n<5
	)
	SELECT * from t1

Mysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第6张

该怎么理解这每一步呢?

WITH RECURSIVE t1 AS:

这是递归查询的开始,创建了一个名为t1的递归表。

SELECT 1 AS n:

在t1表中,插入了一个初始行,值为1,命名为n。

UNION ALL:

使用UNION ALL运算符将初始行和递归查询结果合并,形成递归步骤。这也就是下次递归的起点表

SELECT n+1 FROM t1 WHERE n<5:

递归部分的查询,从t1表中选择n加1的结果,当n小于5时进行递归。

SELECT * FROM t1:

最终查询,返回t1表的所有行。

其实在使用递归的过程只需要注意要去避免死龟就好!

如何去查开头的那张树形表呢?这样就好:

with recursive temp as (
select * from  course_category p where  id= '1'
 union all
select t.* from course_category t inner join temp on temp.id = t.parentid
)
select *  from temp order by temp.id, temp.orderby

下面我们逐帧分析:

Mysql树形表的两种查询方案(递归与自连接),在这里插入图片描述,第7张

其实关键的地方就在于第三步,在树根的基础上去找叶子:

神之一手:

select t.* from course_category t inner join temp on temp.id = t.parentid

这就是递归相较于第一种方式可以无视层级inner jion的关键,因为这个动作已经被递归自动完成了,递归巧妙地一点就在这里

向下递归

基于向上递归父找子的思想,向下递归则是子找父,即在叶子基础上union all之后去找根

子的parentId=父的id

with recursive temp as (
select * from  course_category p where  id= '1-1-1'
 union all
select t.* from course_category t inner join temp on temp.parentid = t.id  
//temp表是下次递归的基础
)
select *  from temp order by temp.id, temp.orderby

值得注意的是Mysql为了避免无限递归递归次数为1000次,也可以人为来设置cte_max_recursion_depth和max_execution_time来自定义递归深度和执行时间

使用递归的好处无需言语,一次io连接就搞定了全部