前言
在关系型数据库中，数据通常存储为二维表格（rows 和 columns）。然而，在实际业务中，很多场景下我们需要处理树状结构的数据，例如：
- 公司组织架构：从某个部门开始，查询其下属部门或员工。
- 商品分类：查询某个大类下的所有子类。
- 权限系统：根据某个角色，查询其子角色或权限继承关系。
- 评论区：查找某个评论的所有子评论。
树状数据的查询通常需要递归逻辑来处理，我看到很多人在业务代码中进行递归查询，如果数据量过大，会导致查询的次数过多，产生不必要的网络开销，而 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 ...UNION ALL-- 递归步骤：递归逻辑，用于迭代查询SELECT ...
)
SELECT ...
FROM cte_name;
下面是实现递归查询的SQL语句：
WITH RECURSIVE `temp` AS (SELECTid,parent_id FROM`comment` WHEREid = 1UNION ALLSELECT`comment`.id,`comment`.parent_id FROM`comment`,`temp` WHERE`comment`.parent_id = `temp`.id
)
SELECTid
FROMtemp;
具体逻辑如下
1. WITH RECURSIVE temp AS (...)
这里定义了一个临时表 temp，它将递归地包含我们想要查询的结果。通过 RECURSIVE 关键字，允许SQL查询自身，即可以自引用。
2. 初始查询
SELECTid,parent_id
FROM`comment`
WHEREid = 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. 最终查询
SELECTid
FROMtemp;
最后，我们从递归生成的 temp 表中提取出所有的评论 id，这些 id 就是给定评论及其所有子评论的ID。
递归查询的工作流程
- 基准步骤：首先查询ID为1的评论。
- 递归步骤：接着查询其所有的子评论，并不断递归查询每个子评论的子评论，直到没有更多的子评论为止。
性能影响
递归查询是能够正常走索引的，但在查询大规模数据时，可能会带来性能问题，因为每次递归都是一次新的查询，但不管怎么样，它都好过于直接在业务中进行递归查询。
这是因为在查询相同数据时，SQL递归只需要一次网络io，但业务逻辑里进行递归查询SQL时，每一次递归都包含一次网络io。
支持的数据库
递归 CTE (WITH RECURSIVE) 对于某些老版本的数据库，可能并不适用。各个数据库支持的情况如下：
- MySQL：8.0 及以上版本支持。
- PostgreSQL：8.4 及以上版本支持。
- SQL Server：2005 及以上版本支持，但语法稍有不同。
- SQLite：3.8.3 及以上版本支持。
- Oracle：11gR2 及以上版本支持（也支持
CONNECT BY语法）。 - MariaDB：10.2.2 及以上版本支持。
总结
这是一种简洁且强大的方法，值得在需要处理层次结构数据时进行应用。
如果你需要处理类似的树状数据，递归查询绝对是你应该掌握的利器！希望本文对你理解递归查询有所帮助。
你可以尝试在自己的数据库中执行类似的查询，并根据实际情况调整递归逻辑。欢迎留言讨论你的想法和问题！
