使用SQL递归查询树状结构，又可以跟同事吹牛了！

前言

在关系型数据库中，数据通常存储为二维表格（rows 和 columns）。然而，在实际业务中，很多场景下我们需要处理树状结构的数据，例如：

1. 公司组织架构：从某个部门开始，查询其下属部门或员工。
2. 商品分类：查询某个大类下的所有子类。
3. 权限系统：根据某个角色，查询其子角色或权限继承关系。
4. 评论区：查找某个评论的所有子评论。

树状数据的查询通常需要递归逻辑来处理，我看到很多人在业务代码中进行递归查询，如果数据量过大，会导致查询的次数过多，产生不必要的网络开销，而 SQL 中的递归查询（Recursive Query）正是为了解决这类问题。

本文将通过一个具体的 SQL 递归查询示例，来解释如何使用递归 CTE（Common Table Expression）来遍历和查询树状结构数据。

场景背景

假设我们有一个存储用户评论的数据库表

comment

，其中包含以下字段：

• id：评论的唯一标识
• parent_id：父评论的ID（如果评论是根评论，则 parent_id 为空）

我们需要编写一个SQL查询，从某个特定的评论开始，递归地查询所有的子评论。比如，给定评论ID为

1

，我们想要找到该评论的所有子评论以及其子评论的子评论。

SQL语句解析

先简单介绍一下 MySQL 递归查询的语法格式：

WITH RECURSIVE cte_name AS(-- 递归基：非递归部分，通常是初始化数据SELECT...UNIONALL-- 递归步骤：递归逻辑，用于迭代查询SELECT...)SELECT...FROM cte_name;

下面是实现递归查询的SQL语句：

WITH RECURSIVE `temp`AS(SELECT
        id,
        parent_id 
    FROM`comment`WHERE
        id =1UNIONALLSELECT`comment`.id,`comment`.parent_id 
    FROM`comment`,`temp`WHERE`comment`.parent_id =`temp`.id
)SELECT
    id 
FROMtemp;

具体逻辑如下

1.

WITH RECURSIVE temp AS (...)

这里定义了一个临时表

temp

，它将递归地包含我们想要查询的结果。通过

RECURSIVE

关键字，允许SQL查询自身，即可以自引用。

2. 初始查询

SELECT
    id,
    parent_id 
FROM`comment`WHERE
    id =1

该部分是递归查询的基准情况，表示首先从评论表中选取ID为

1

的那条评论作为查询起点，找到这条评论的

id

和

parent_id

。

temp

表中的初始数据就来自于这里。

3. 递归部分

SELECT`comment`.id,`comment`.parent_id 
FROM`comment`,`temp`WHERE`comment`.parent_id =`temp`.id

temp

表包含了前一步获取的评论记录。在这一步中，SQL会从

comment

表中找到所有

parent_id

等于

temp.id

的评论，即所有子评论，并将它们再次加入

temp

表中。

递归会持续执行，直到没有更多匹配的子评论为止。

4. 最终查询

SELECT
    id 
FROMtemp;

最后，我们从递归生成的

temp

表中提取出所有的评论

id

，这些

id

就是给定评论及其所有子评论的ID。

递归查询的工作流程

• 基准步骤：首先查询ID为1的评论。
• 递归步骤：接着查询其所有的子评论，并不断递归查询每个子评论的子评论，直到没有更多的子评论为止。

性能影响

递归查询是能够正常走索引的，但在查询大规模数据时，可能会带来性能问题，因为每次递归都是一次新的查询，但不管怎么样，它都好过于直接在业务中进行递归查询。

这是因为在查询相同数据时，SQL递归只需要一次网络io，但业务逻辑里进行递归查询SQL时，每一次递归都包含一次网络io。

限制递归深度

如果树的层数较高，同时也为了防止递归时产生死循环，最好在查询时限制递归深度：

WITH RECURSIVE `temp`AS(SELECT
        id,
        parent_id,1as depth
    FROM`comment`WHERE
        id =1UNIONALLSELECT`comment`.id,`comment`.parent_id,`temp`.depth +1FROM`comment`,`temp`WHERE`comment`.parent_id =`temp`.id 
        and`temp`.depth <5-- 限制递归深度为5 )SELECT
    id 
FROMtemp;

支持的数据库

递归 CTE (

WITH RECURSIVE

) 对于某些老版本的数据库，可能并不适用。各个数据库支持的情况如下：

• MySQL：8.0 及以上版本支持。
• PostgreSQL：8.4 及以上版本支持。
• SQL Server：2005 及以上版本支持，但语法稍有不同。
• SQLite：3.8.3 及以上版本支持。
• Oracle：11gR2 及以上版本支持（也支持 CONNECT BY 语法）。
• MariaDB：10.2.2 及以上版本支持。

总结

这是一种简洁且强大的方法，值得在需要处理层次结构数据时进行应用。

如果你需要处理类似的树状数据，递归查询绝对是你应该掌握的利器！希望本文对你理解递归查询有所帮助。

你可以尝试在自己的数据库中执行类似的查询，并根据实际情况调整递归逻辑。欢迎留言讨论你的想法和问题！