Mysql树形表的两种查询方案（递归与自连接）

Love The Way You Lie 2023-10-15 19:49 28阅读 0赞

你有没有遇到过这样一种情况：  
**一张表就实现了一对多的关系，并且表中每一行数据都存在“爷爷-父亲-儿子-…”的联系，这也就是所谓的树形结构**  
![在这里插入图片描述][896001887c0549b6954c5bf194cb8719.png]  
对于这样的表很显然想要通过查询来实现价值绝对是不能只靠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

也是规规矩矩的就查出一整棵树  
![在这里插入图片描述][74243575a320467db5662f14896ae5b0.png]  
这种查询的原则就是通过parentId去实现，“爷爷找爸爸，爸爸找儿子，儿子找孙子”,下面来逐帧慢放：  
**1.one**![在这里插入图片描述][83cbbd3dcfff4e2990e85b81387465bf.png]  
**2.one,two**  
![在这里插入图片描述][1e72e0a67a554095bf593d511aa4e7f4.png]  
**3.one,two,three**  
![在这里插入图片描述][aac9842f193e4b108446c9eaec15af06.png]  
可以看到，只有在树的层级确定的情况下我才能选择性的去自连接子表，某种意义上来讲这种方法存在弊端，我要是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

![在这里插入图片描述][5e5db8852c814ed484d295de5d1269d7.png]  
该怎么理解这每一步呢？  
**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

下面我们逐帧分析：  
![在这里插入图片描述][8fad73ef5492479ca9349d72d5dd951a.png]  
其实关键的地方就在于第三步，在树根的基础上去找叶子：  
神之一手：  
**`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连接就搞定了全部

[896001887c0549b6954c5bf194cb8719.png]: https://img-blog.csdnimg.cn/896001887c0549b6954c5bf194cb8719.png
[74243575a320467db5662f14896ae5b0.png]: https://img-blog.csdnimg.cn/74243575a320467db5662f14896ae5b0.png
[83cbbd3dcfff4e2990e85b81387465bf.png]: https://img-blog.csdnimg.cn/83cbbd3dcfff4e2990e85b81387465bf.png
[1e72e0a67a554095bf593d511aa4e7f4.png]: https://img-blog.csdnimg.cn/1e72e0a67a554095bf593d511aa4e7f4.png
[aac9842f193e4b108446c9eaec15af06.png]: https://img-blog.csdnimg.cn/aac9842f193e4b108446c9eaec15af06.png
[5e5db8852c814ed484d295de5d1269d7.png]: https://img-blog.csdnimg.cn/5e5db8852c814ed484d295de5d1269d7.png
[8fad73ef5492479ca9349d72d5dd951a.png]: https://img-blog.csdnimg.cn/8fad73ef5492479ca9349d72d5dd951a.png