1. 从一次“树状组织架构”查询说起：为什么需要递归？
最近在做一个内部系统的权限模块，需要根据一个员工的ID，查出他所在部门的所有上级部门，一直到公司根节点。表结构很简单，大概是这样：
CREATE TABLE department ( id INT PRIMARY KEY, name VARCHAR(50), parent_id INT, FOREIGN KEY (parent_id) REFERENCES department(id) );数据也很直观：parent_id指向上一级部门的id，如果为NULL则表示这是顶级部门（比如总公司）。当我想查某个基层员工“张三”的所有上级部门链时，直觉告诉我这应该是个循环或迭代的过程：先找到张三的部门A，再找A的上级部门B，接着找B的上级部门C……直到某个部门的parent_id为NULL。
在程序代码里，这很简单，一个while循环或者递归函数就能搞定。但问题来了：能不能直接在数据库里用一条SQL语句查出来？毕竟，把数据全拉到应用层再处理，不仅网络IO开销大，代码也显得臃肿。这就是SQL递归查询（Recursive Query）要解决的经典问题——处理具有层次结构或树形结构的数据。
在MySQL 8.0之前，这个需求确实有点棘手，通常得用存储过程或者应用程序多次查询来实现。但自MySQL 8.0起，它正式引入了Common Table Expressions (CTE)的递归功能，这让在单条SQL语句中遍历树形结构变成了可能。今天，我就结合自己踩过的坑和实战心得，带你彻底搞懂MySQL中递归CTE的用法、原理、性能陷阱以及那些官方手册里不会写的细节。
2. 递归CTE的核心语法拆解：WITH RECURSIVE 到底在做什么？
递归CTE的语法骨架看起来有点唬人，但拆开看就清晰了。它的标准结构如下：
WITH RECURSIVE cte_name (column_list) AS ( -- 1. 锚点成员 (Anchor Member) SELECT ... FROM ... WHERE ... UNION ALL -- 2. 递归成员 (Recursive Member) SELECT ... FROM cte_name, other_tables WHERE ... ) SELECT * FROM cte_name;你可以把它理解为一个具有迭代能力的临时视图。执行过程是分步的：
第一步：执行锚点成员 (Anchor Member)。这是递归的起点，相当于初始化第一层数据。比如在我们查部门链的例子中，锚点就是找到“张三”所在的初始部门。
第二步：执行递归成员 (Recursive Member)。这是递归的核心。它会引用CTE自身（cte_name），将上一步迭代产生的结果作为输入，生成下一层数据。关键点在于，递归成员是从CTE的“上一次迭代结果”中查询，而不是从CTE的所有累积结果中查询。这个过程会反复执行。
第三步：合并与循环判断。将递归成员产生的新结果，通过UNION ALL追加到总结果集中。然后，检查递归成员是否产生了新行：
- 如果产生了新行，则用这些新行作为输入，跳回第二步，开始下一次迭代。
- 如果没有产生新行（即结果集为空），则递归终止。
第四步：最终输出。将锚点成员和所有次迭代中递归成员产生的结果，通过UNION ALL合并，作为CTE的最终结果集，供外部查询使用。
这里有一个至关重要的细节：递归成员必须包含一个连接条件，这个条件能驱动迭代向“深层”或“上层”推进，并且必须有一个终止条件来避免无限循环。通常，这个终止条件就是当递归成员查询不到任何新数据时，循环自然结束。
让我们用一个最简单的数字序列生成例子来感受一下这个过程，这比直接看树形结构更直观：
WITH RECURSIVE number_sequence (n) AS ( -- 锚点成员：从1开始 SELECT 1 UNION ALL -- 递归成员：每次在上一个数字基础上+1 SELECT n + 1 FROM number_sequence WHERE n < 5 -- 终止条件 ) SELECT * FROM number_sequence;执行过程模拟：
- 锚点：
SELECT 1-> 结果{1}。 - 第一次递归：输入
{1}，执行SELECT 1+1 FROM ... WHERE 1<5-> 结果{2}，合并后总结果{1, 2}。 - 第二次递归：输入
{2}，执行SELECT 2+1 FROM ... WHERE 2<5-> 结果{3}，合并后总结果{1, 2, 3}。 - ... 依次类推，直到输入
{5}时，WHERE 5 < 5条件为假，递归成员返回空集，递归终止。 - 最终输出：
{1, 2, 3, 4, 5}。
注意：
UNION ALL和UNION DISTINCT在递归CTE中有不同意义。UNION ALL允许重复值，效率更高，是递归中的常见选择。而UNION DISTINCT会在每次合并时去重，这可能影响递归逻辑（例如，在生成路径时可能导致意外终止），除非你明确需要去重，否则优先使用UNION ALL。
3. 实战场景一：自底向上查询——查找所有祖先节点
回到开头的部门问题。假设“张三”在id = 10的部门，我们要找到他所有的上级部门，直到公司顶层。这就是一个典型的“自底向上”遍历。
我们先准备一些测试数据：
INSERT INTO department (id, name, parent_id) VALUES (1, '集团公司', NULL), (2, '技术研发中心', 1), (3, '产品部', 2), (4, '前端开发组', 3), (5, '后端开发组', 3), (10, 'Java开发小组', 5); -- 张三所在的部门现在，写出递归CTE：
WITH RECURSIVE dept_chain AS ( -- 锚点成员：找到起始部门（Java开发小组） SELECT id, name, parent_id, 1 AS level FROM department WHERE id = 10 -- 从id=10开始 UNION ALL -- 递归成员：根据当前部门的parent_id，找到它的上级部门 SELECT d.id, d.name, d.parent_id, dc.level + 1 FROM department d INNER JOIN dept_chain dc ON d.id = dc.parent_id -- 终止条件隐含在JOIN中：当dc.parent_id找不到对应的d.id时，递归结束 ) SELECT id, name, parent_id, level FROM dept_chain;关键点解析：
- 锚点：
WHERE id = 10确定了递归的起点。 - 递归推进：
INNER JOIN dept_chain dc ON d.id = dc.parent_id是灵魂。它意味着：从上一轮迭代结果（dept_chain，别名为dc）中，取出每个部门的parent_id，去关联department表，找到对应的上级部门记录。 - 层级计算：
level字段是一个计数器，在锚点中初始化为1，每次递归时加1，直观地展示了“第几级上级”。 - 终止条件：这里没有显式的
WHERE终止条件，因为当dc.parent_id为NULL（顶级部门）或找不到匹配的d.id时，INNER JOIN自然会产生空结果集，递归随之终止。
执行上述查询，你会得到类似下面的结果，清晰地展示了从“Java开发小组”到“集团公司”的完整汇报链：
| id | name | parent_id | level |
|---|---|---|---|
| 10 | Java开发小组 | 5 | 1 |
| 5 | 后端开发组 | 3 | 2 |
| 3 | 产品部 | 2 | 3 |
| 2 | 技术研发中心 | 1 | 4 |
| 1 | 集团公司 | NULL | 5 |
踩坑心得：无限循环与循环检测如果数据中不幸出现了循环引用（例如A的上级是B，B的上级又是A），这个递归就会陷入死循环。MySQL默认的递归最大深度是cte_max_recursion_depth（默认1000次），达到后报错“Recursive query aborted after 1001 iterations”。在生产环境中，对于不可信的数据源，建议采取防御措施：
- 在递归成员中增加显式深度限制：
WHERE dc.level < 50。 - 或者，在会话中临时设置一个安全的深度：
SET SESSION cte_max_recursion_depth = 100;。
4. 实战场景二：自顶向下查询——查找所有子孙节点
与“找上级”相反，“找下级”是另一个高频需求。例如，我想知道“技术研发中心”（id=2）下属的所有部门和子部门。这是一个“自顶向下”的遍历。
WITH RECURSIVE sub_depts AS ( -- 锚点：找到根部门（技术研发中心） SELECT id, name, parent_id, 0 AS level, CAST(id AS CHAR(255)) AS path FROM department WHERE id = 2 UNION ALL -- 递归：找到当前部门的所有直接下级部门 SELECT d.id, d.name, d.parent_id, sd.level + 1, CONCAT(sd.path, '->', d.id) FROM department d INNER JOIN sub_depts sd ON d.parent_id = sd.id -- 终止条件：当没有部门以当前部门为parent_id时，递归结束 ) SELECT id, name, parent_id, level, path FROM sub_depts ORDER BY level, id;关键点解析：
- 递归方向：注意
JOIN条件变成了ON d.parent_id = sd.id。意思是：在department表里，找那些parent_id等于上一轮结果中部门id的记录。这正是“查找子节点”的逻辑。 - 路径追踪：这里我引入了一个
path字段（使用CAST初始化类型，CONCAT追加），它记录了从根节点到当前节点的ID路径（如2->3->5->10）。这在分析树形结构、生成面包屑导航或调试递归过程时非常有用。 - 层级与排序：
level表示深度，ORDER BY level, id可以让我们按层级和顺序查看结果，更清晰。
查询结果会列出“技术研发中心”下的整个子树：
| id | name | parent_id | level | path |
|---|---|---|---|---|
| 2 | 技术研发中心 | 1 | 0 | 2 |
| 3 | 产品部 | 2 | 1 | 2->3 |
| 4 | 前端开发组 | 3 | 2 | 2->3->4 |
| 5 | 后端开发组 | 3 | 2 | 2->3->5 |
| 10 | Java开发小组 | 5 | 3 | 2->3->5->10 |
性能陷阱与优化思路自顶向下查询在深层或宽广的树中可能产生巨大结果集。如果parent_id上没有索引，每次递归的JOIN都会导致全表扫描，性能呈指数级恶化。务必在parent_id字段上建立索引：
CREATE INDEX idx_department_parent ON department(parent_id);这个索引能极大加速递归成员中ON d.parent_id = sd.id的查找速度。
5. 实战场景三：复杂条件过滤与聚合
递归CTE的强大之处在于，它产生的临时结果集可以像普通表一样被任意查询、过滤和聚合。我们来看两个更复杂的例子。
场景A：统计每个部门下的总人数（包括所有子部门）假设我们还有一张员工表employee(dept_id, name)。我们需要一个报表，显示每个部门及其所有子孙部门的员工总数。
思路：先为每个部门生成其所有子孙部门的列表（包括自己），然后关联员工表进行分组统计。
WITH RECURSIVE dept_tree AS ( -- 锚点：每个部门都是自己树的根 SELECT id AS root_dept_id, id, parent_id FROM department UNION ALL -- 递归：向下扩展子树 SELECT dt.root_dept_id, d.id, d.parent_id FROM department d INNER JOIN dept_tree dt ON d.parent_id = dt.id ), dept_employee_count AS ( SELECT dt.root_dept_id, COUNT(e.id) AS total_employees FROM dept_tree dt LEFT JOIN employee e ON dt.id = e.dept_id GROUP BY dt.root_dept_id ) SELECT d.name AS department_name, dec.total_employees FROM department d JOIN dept_employee_count dec ON d.id = dec.root_dept_id ORDER BY d.id;这个查询稍微复杂些：
dept_treeCTE为原始部门表中的每一个部门（锚点）都生成了一棵以它为根的完整子树。root_dept_id列始终保持为这棵树的根部门ID。- 然后，将
dept_tree与employee表左连接，按root_dept_id分组统计，得到每个根部门对应的总员工数。 - 最后，关联回
department表获取部门名称。
场景B：查找特定层级或满足条件的节点比如，我想找出“技术研发中心”下所有第三级的部门。
WITH RECURSIVE sub_depts AS ( SELECT id, name, parent_id, 0 AS level FROM department WHERE id = 2 UNION ALL SELECT d.id, d.name, d.parent_id, sd.level + 1 FROM department d INNER JOIN sub_depts sd ON d.parent_id = sd.id ) SELECT id, name, level FROM sub_depts WHERE level = 3; -- 直接对递归CTE的结果进行过滤一个重要的提醒：过滤条件的位置你可以像上面那样在外部查询中过滤（WHERE level = 3），也可以在递归成员内部过滤。两者有本质区别：
- 在外部过滤：会先完整生成整棵树，再从结果中筛选出
level=3的节点。如果树很大，这会产生不必要的中间结果，可能影响性能。 - 在递归成员内部过滤（如
WHERE sd.level < 3）：会在递归过程中提前终止向更深层的探索，只生成到第三层为止的数据。但这会改变递归逻辑！如果你在递归成员里加了WHERE sd.level < 3，那么递归在level=2之后就会停止，你根本得不到level=3的节点作为结果。所以，要根据你的目的谨慎选择过滤位置。如果只是想要最终结果的某个子集，在外部过滤通常更安全直观；如果想控制递归深度，则在递归成员内部加条件。
6. 性能调优与避坑指南
递归CTE虽然方便，但用不好就是性能杀手。下面是我在实际项目中总结的几个关键点。
1. 索引是生命线如前所述，递归查询的核心是JOIN操作。无论是ON d.id = dc.parent_id（找上级）还是ON d.parent_id = sd.id（找下级），都需要对关联字段进行快速查找。
- 必备索引：在
parent_id字段上建立索引。对于“找上级”的查询，如果起点id不是主键，也需要在id上建立索引（通常主键已有）。 - 复合索引考虑：如果递归查询中经常附带其他过滤条件（如
status = 'active'），可以考虑建立(parent_id, status)这样的复合索引。
2. 控制递归深度与结果集大小MySQL有@@cte_max_recursion_depth系统变量控制最大迭代次数。对于已知深度的数据（如组织架构通常不超过10层），可以将其设为一个合理值，既防止无限循环，也避免深度过大导致性能骤降或内存溢出。
SET SESSION cte_max_recursion_depth = 50;对于“自顶向下”查询广阔树（如分类目录），结果集可能爆炸。务必评估数据量，考虑在业务层进行分页或懒加载，而不是一次性拉取整棵树。
3. 避免在递归成员中使用聚合或窗口函数递归成员在每次迭代中都会执行。如果其中包含GROUP BY、SUM()或ROW_NUMBER()等操作，会导致每次迭代都进行全量聚合/排序，性能极差。通常的解决方案是将递归CTE的结果存入一个临时表或子查询，再在其上进行聚合分析。
4. 递归CTE的“一次生成，多次引用”特性一个WITH子句中的CTE可以被随后的多个CTE或主查询引用。但要注意，递归CTE在每次被引用时都会重新执行吗？答案是：不会。MySQL会物化（Materialize）递归CTE的结果。这意味着，即使你在多个地方引用同一个递归CTE，它也只计算一次。这通常是好事（提高性能），但如果你期望引用时得到动态变化的数据（比如在存储过程中），需要注意这个特性。
5. 调试技巧：使用LIMIT和path字段当递归查询结果不符合预期或陷入循环时，调试起来可能比较困难。我的常用方法是：
- 在递归成员中添加一个
path字段（如上面的例子），直观看到遍历路径。 - 在外部查询中使用
LIMIT 20查看前几轮迭代的结果，判断递归逻辑是否正确。 - 单独执行锚点成员和手动模拟一次递归成员的执行，验证
JOIN条件。
7. 不止于树：递归CTE的其他妙用
递归CTE并非只能用于父子关系。任何需要基于前一次结果进行迭代计算的场景，都可以考虑它。
场景一：生成连续的数字序列或日期序列这在生成报表、补全缺失日期数据时非常有用。
-- 生成1到100的数字序列 WITH RECURSIVE numbers AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 100 ) SELECT n FROM numbers; -- 生成最近7天的日期 WITH RECURSIVE dates AS ( SELECT CURDATE() AS dt UNION ALL SELECT DATE_SUB(dt, INTERVAL 1 DAY) FROM dates WHERE dt > DATE_SUB(CURDATE(), INTERVAL 6 DAY) ) SELECT dt FROM dates ORDER BY dt;场景二：展开分层数据或字符串解析例如，有一个逗号分隔的字符串'a,b,c,d'，想把它拆分成多行。
WITH RECURSIVE split_string AS ( SELECT 'a,b,c,d' AS str, 1 AS start_pos, LOCATE(',', 'a,b,c,d') AS comma_pos UNION ALL SELECT str, comma_pos + 1, LOCATE(',', str, comma_pos + 1) FROM split_string WHERE comma_pos > 0 ) SELECT SUBSTRING( str, start_pos, IF(comma_pos > 0, comma_pos - start_pos, LENGTH(str)) ) AS item FROM split_string;这个例子稍复杂，它利用LOCATE函数迭代查找逗号位置，并通过SUBSTRING截取出每个元素。这展示了递归CTE处理序列化数据的潜力。
递归CTE是MySQL 8.0带给开发者的强大武器，它将许多原本需要在应用层处理的复杂逻辑下推到了数据库层，简化了代码，并在某些场景下提升了性能。掌握它的核心在于理解“锚点-递归-合并”的迭代过程，并时刻警惕数据循环与性能边界。下次当你面对树形数据、序列生成或层次化计算时，不妨先想想：能不能用一句WITH RECURSIVE优雅地解决？