做业务系统的人迟早会遇到一类数据结构树形。组织架构、商品分类、多级菜单、评论楼层、区域划分本质都是一个父节点下面挂一堆子节点子节点下面再挂孙子节点层数还不固定。以前在 MySQL 里处理这种数据要么写存储过程循环要么在应用层写递归方法一遍一遍查库要么干脆用 N 次 LEFT JOIN 写死层级哪个方案都谈不上优雅。MySQL 8.0 引入 WITH RECURSIVE 之后这个局面才真正被改变一句 SQL 就能把整棵子树捞出来还能顺便算层级、拼路径。这篇笔记我按自己的实战经验整理从“递归查询到底解决了什么痛点”讲起再拆解语法里的锚点成员和递归成员如何配合然后带几个最常见的业务场景向下查所有下级、向上查所有上级、生成连续日期序列补全报表空缺。最后把我在生产环境里踩过的坑、调过的参数、做过的优化都列出来包括 8.0 之前旧版本里的替代方案。适合正在用或打算用 MySQL 8.0 的开发同学也适合面试前突击准备“MySQL 递归查询”这道题的读者。1. 递归查询到底解决了什么问题以及 8.0 之前为什么那么费劲1.1 先看一个真实场景查某个部门下所有子部门我之前做过一个企业内部系统组织架构表结构大概是这样的字段类型说明idINT部门ID主键nameVARCHAR(64)部门名称parent_idINT上级部门ID顶级部门为 0数据长这样研发中心底下挂基础架构组、后端组、前端组后端组底下又挂数据组、接口组。需求是给用户一个部门选择器选中“研发中心”之后要把下面所有层级的子部门全部查出来用于权限范围判断。这个需求听着简单但难点在于层级深度不可控今天是两级明年可能就是四级、五级。用 MySQL 5.7 及其之前版本处理时我第一版是在应用层写递归方法先查 parent_id X 的一批节点再拿这批节点的 id 作为 parent_id 去查下一批直到查不到为止。数据量小的时候没问题但每层一次网络往返层级多时延迟叠得很难看还要小心翼翼地做循环次数限制防止脏数据导致死循环把应用层服务拖挂。第二版改成存储过程加临时表虽然减少了一点网络往返但存储过程的维护成本高DBA 评审时总盯着临时表的大小和事务里的锁调试起来也没那么直观。1.2 固定深度 JOIN 的方案为什么只能算“权宜之计”如果你在网上搜“MySQL 树形查询”大概率会看到一个三表或四表 LEFT JOIN 的写法SELECT a.id AS lv1_id, b.id AS lv2_id, c.id AS lv3_id FROM department a LEFT JOIN department b ON b.parent_id a.id LEFT JOIN department c ON c.parent_id b.id WHERE a.id 1;这个写法在层级固定为 3 层时可以跑但实际业务里很难保证永远只有 3 层。一旦需求变成 4 层或者某条路径只有 2 层查询逻辑就崩了。我当时在这个方案上吃的亏记忆犹新部门调整了一个中间层结果权限范围立刻漏了一片。这也是为什么递归 CTE 出来后我第一时间把旧代码全部重构掉了。1.3 MySQL 8.0 的 CTE 给了什么东西MySQL 8.0 引入了公用表表达式Common Table ExpressionCTE其中的递归形式 WITH RECURSIVE 可以直接在一个查询里自我引用逐层迭代直到结果集不再扩大。它把以前靠存储过程或应用层循环做的事收敛成一个纯 SQL 的声明式写法逻辑清晰、可读性强而且不需要维护额外的存储过程对象。一句话概括原理先给一个初始结果集然后用这个结果集作为输入去查询下一层把下一层的结果并进来再继续查询直到某一次迭代没有产生新行查询结束。这个机制就是整篇文章的核心后面所有例子都是围绕它展开的。2. 核心语法拆解锚点成员与递归成员怎么配合2.1 第一个能跑的最小示例生成 1 到 100 的数字序列如果你从来没写过递归查询我建议先用这个最简单例子找感觉WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 100 ) SELECT n FROM seq;执行结果就是 1 到 100 共 100 行。把这段 SQL 拆开看SELECT 1 AS n是锚点成员Anchor Member定义递归的起点只执行一次。SELECT n 1 FROM seq WHERE n 100是递归成员Recursive Member它引用了 CTE 自身seq每轮迭代都基于上一轮的结果继续计算。UNION ALL负责把每一次迭代产生的新行追加到结果集里。当某次迭代计算出的行数变成 0递归就自动停止。这个例子看起来很朴素但背后是递归查询的核心套路锚点决定起点递归成员决定怎么从上一层推导出下一层WHERE n 100既是业务条件也是终止条件。2.2 锚点成员整个查询的“第一次”锚点成员是一个普通 SELECT它不引用 CTE 自身作用是确定初始行。在树形数据的场景里锚点成员通常就是你要查询的根节点。比如查 id 5 的部门及其所有子部门锚点就是SELECT * FROM department WHERE id 5。这里容易出现一个误解锚点只能选一行吗不是。锚点可以返回多行。比如你想同时看两个部门的所有子部门锚点就写WHERE id IN (5, 8)这样两个部门会分别从各自的起点往下探索结果集自然合并在一起。2.3 递归成员每一次迭代都在“顺着边往下走”递归成员必须包含对 CTE 名称自身的引用并且要负责把“上一轮结果”和“业务表”做关联找出下一个要加入的行。最常见的写法是这样的WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id FROM department WHERE id 5 UNION ALL SELECT d.id, d.name, d.parent_id FROM department d JOIN dept_tree t ON d.parent_id t.id ) SELECT * FROM dept_tree;递归成员里的JOIN dept_tree t ON d.parent_id t.id就是“顺着父节点找子节点”的动作。每次迭代MySQL 都拿当前结果集里新增的那些id去 department 表里找parent_id匹配的行找到就作为下一轮的行。这个过程会重复执行直到没有新行产生。2.4 UNION 与 UNION ALL 的区别不是性能问题是业务问题递归 CTE 里可以使用UNION也可以使用UNION ALL区别在于是否自动去重。树形数据里如果节点结构没有环A 的父节点是 BB 的父节点又是 A用哪种都一样但一旦数据脏了存在循环引用UNION的去重机制会在某一轮发现“这个节点我见过了”从而终止这条路径而UNION ALL会一直循环下去直到触发递归深度限制。从性能角度UNION要做去重比较天然比UNION ALL慢。所以我个人的习惯是能确定数据没有循环引用时优先用UNION ALL如果对上游数据质量没信心或者业务上确实需要保证路径不重复再考虑UNION。2.5 递归查询和普通自连接的本质区别普通自连接一次只能固定查询一层父子关系比如你 JOIN 一次拿到两级JOIN 两次拿到三级层数写死在 SQL 里。递归查询则是把“JOIN 自己”这个动作交给数据库循环执行层数由数据深度决定SQL 文本不用变。这也是递归查询在动态层级场景下不可替代的原因。3. 真实业务场景实操从查下级到查上级再到路径统计3.1 场景一查某个部门下的所有子部门向下递归这是最常见的需求。我用一张简单的部门表演示假设表结构如下CREATE TABLE department ( id INT PRIMARY KEY, name VARCHAR(64) NOT NULL, parent_id INT NOT NULL DEFAULT 0 ); INSERT INTO department (id, name, parent_id) VALUES (1, 研发中心, 0), (2, 基础架构组, 1), (3, 后端组, 1), (4, 前端组, 1), (5, 数据组, 3), (6, 接口组, 3), (7, 数据库组, 5), (8, 中间件组, 5);现在要查“后端组”即 id 3 下所有的子部门包括子部门的子部门。SQL 如下WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id FROM department WHERE id 3 UNION ALL SELECT d.id, d.name, d.parent_id FROM department d JOIN dept_tree t ON d.parent_id t.id ) SELECT id, name, parent_id FROM dept_tree;执行结果idnameparent_id3后端组15数据组36接口组37数据库组58中间件组5可以看到结果集自动把三层结构后端组 - 数据组 - 数据库组全部展开了SQL 本身没有写死层级。这个查询在你需要做权限继承、批量导出子树、级联统计节点数时改动成本极低。3.2 场景二查某个节点的所有上级向上递归向下递归是拿d.parent_id t.id找孩子向上递归则反过来拿t.parent_id d.id找父亲。还是同一张表我想查“数据库组”即 id 7 的所有上级一直追到顶级部门WITH RECURSIVE dept_ancestors AS ( SELECT id, name, parent_id FROM department WHERE id 7 UNION ALL SELECT d.id, d.name, d.parent_id FROM department d JOIN dept_ancestors t ON d.id t.parent_id ) SELECT id, name, parent_id FROM dept_ancestors;执行结果idnameparent_id7数据库组55数据组33后端组11研发中心0这个场景在做面包屑导航、上级审批链查询、组织层级校验时非常实用。我个人做过一个数据权限模块用户只能看自己部门以及上级部门的数据就是用这个 SQL 反向查出所有上级部门 ID再放到WHERE dept_id IN (...)里过滤。3.3 场景三带上层级字段和路径顺便做树形展示很多前端组件需要level和path字段来渲染树。递归查询里可以用一个初始值为 1 的字段在递归成员里不断加 1 来记录深度路径则可以用CONCAT拼接。下面是一个带层级的查询WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 1 AS level, CAST(id AS CHAR(100)) AS path FROM department WHERE id 1 UNION ALL SELECT d.id, d.name, d.parent_id, t.level 1, CONCAT(t.path, ,, d.id) FROM department d JOIN dept_tree t ON d.parent_id t.id ) SELECT id, name, parent_id, level, path FROM dept_tree ORDER BY path;结果里level表示当前部门离根节点有几层path则是从根节点到当前节点的完整 ID 路径比如1,3,5,7。前端拿到这个结果之后不需要再做一次递归处理可以直接通过level控制缩进通过path生成展开链。这里要注意CAST(id AS CHAR(100))的写法目的是把初始 ID 转成字符串否则后续CONCAT过程中类型不一致容易报错。3.4 场景四生成连续日期序列补全报表缺失日期递归查询不只用来处理树形数据。生成连续日期是我在数据报表里用得特别多的另一个场景。很多统计表只记录有数据的日期直接查出来的结果日期是断的。要补全可以先在 SQL 里生成一段连续日期序列再左关联业务数据WITH RECURSIVE date_range AS ( SELECT DATE(2024-01-01) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_range WHERE d DATE(2024-01-31) ) SELECT d FROM date_range;这段 SQL 会生成 2024-01-01 到 2024-01-31 的每一天。实际使用时再把业务表的统计字段LEFT JOIN到这个日期序列上WITH RECURSIVE date_range AS ( SELECT DATE(2024-01-01) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_range WHERE d DATE(2024-01-31) ) SELECT r.d, COALESCE(SUM(o.amount), 0) AS total_amount FROM date_range r LEFT JOIN orders o ON o.order_date r.d GROUP BY r.d ORDER BY r.d;没有订单的日期就会显示为 0而不是在报表里缺席。这个技巧在按月出报表、统计日活、做趋势图时非常常用也是我面试候选人时喜欢问的一个点给你一个只有有数据日期的表你怎么补齐中间的空洞。能想到递归日期序列的人通常对 SQL 的理解不会太浅。4. 递归查询常见坑、报错与性能优化4.1 死循环与 cte_max_recursion_depth 限制递归查询最怕的是死循环。如果业务表里存在环状引用比如 A 的上级是 BB 的上级是 A那么向下递归时每一轮都能产出新行查询会永远跑下去。MySQL 的保护机制是cte_max_recursion_depth系统变量默认值是 1000超过后直接报错ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing cte_max_recursion_depth to a larger value.看到这个报错第一反应不该是“把参数调大”而是先检查数据里有没有循环引用。我之前在一个活动页面的层级配置表里就出过这种问题运营在后台把两个节点互相设置成了父子关系线上查询直接打到 1000 层都没有终止数据库 CPU 被打到 100%。排查后删掉脏数据并把参数调成一个业务可接受的上限才算解决。如果业务确实需要更深的递归可以临时调整会话级参数SET SESSION cte_max_recursion_depth 100000;注意这个变量在 MySQL 8.0.19 之前叫max_recursive_iterations从 8.0.19 开始更名为cte_max_recursion_depth。如果你看到旧文档或者老手写的笔记里提到旧名字不要奇怪。4.2 递归成员必须出现在 UNION ALL 右侧MySQL 对递归 CTE 有一个硬性限制递归成员必须写在UNION ALL或UNION的右侧锚点成员只能在左侧。如果写反了会报错ERROR 3577 (HY000): In recursive query, recursive member must not be on the left side of UNION ALL.这个错误不算难排查但新手经常犯。记住一句话先跑初始结果再跑反复迭代的部分第一次的查询放左边引用自身的查询放右边。4.3 别忘了终止条件但也可以主动控制递归深度有些人在写递归成员时只写关联条件不写任何终止条件。如果数据本身没有环MySQL 会在没有新行时自动停止所以不写终止条件也能运行。但风险在于一旦数据里有环没有终止条件就是死循环。所以我的习惯是哪怕数据很干净也会加上一个层级保护比如WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 1 AS level FROM department WHERE id 3 UNION ALL SELECT d.id, d.name, d.parent_id, t.level 1 FROM department d JOIN dept_tree t ON d.parent_id t.id WHERE t.level 10 ) SELECT * FROM dept_tree;这里WHERE t.level 10相当于给递归加了一个保险丝最多展开 10 层。即使数据有问题查询也会在 10 层内终止不会拖垮数据库。4.4 性能优化索引、UNION ALL 与 EXPLAIN ANALYZE递归查询的性能问题绝大多数出在关联字段上没有索引。以部门表为例递归成员JOIN department d ON d.parent_id t.id本质上就是拿t.id去查department.parent_id如果 parent_id 没有索引每次迭代都是全表扫描。我建议在 parent_id 上建索引ALTER TABLE department ADD INDEX idx_parent_id (parent_id);主键 id 一般已有索引不需要额外处理。建完索引后可以用EXPLAIN ANALYZE查看递归查询的真实执行时间和迭代次数EXPLAIN ANALYZE WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id FROM department WHERE id 3 UNION ALL SELECT d.id, d.name, d.parent_id FROM department d JOIN dept_tree t ON d.parent_id t.id ) SELECT * FROM dept_tree;执行计划会显示每一步的耗时和行数方便定位到底是哪一层迭代卡住了。另外如果你的递归结果集不需要去重尽量用UNION ALL。UNION需要维护一个去重表每轮迭代都和已有数据做比较当数据量大时这个开销会非常明显。我在一个几万节点的分类树上做过对比UNION ALL的耗时只有UNION的三分之一左右。4.5 递归查询不能直接做 UPDATE 和 DELETE递归 CTE 只能用于 SELECT 查询不能直接对递归结果集执行 UPDATE 或 DELETE。比如你想删除某个部门下的所有子部门不能写成-- 这个写法不合法 WITH RECURSIVE dept_tree AS (...) DELETE FROM department WHERE id IN (SELECT id FROM dept_tree);MySQL 会报错提示递归查询不能用于 modifying statement。正确做法是先查出要删除的 ID 集合再用另一个查询去更新或删除WITH RECURSIVE dept_tree AS ( SELECT id FROM department WHERE id 3 UNION ALL SELECT d.id FROM department d JOIN dept_tree t ON d.parent_id t.id ) SELECT id FROM dept_tree; -- 拿到 ID 集合后再执行删除或更新 DELETE FROM department WHERE id IN (3, 5, 6, 7, 8);如果你希望一个事务里自动完成“查出来再删”就需要借助存储过程或者在应用层处理。这算是一个比较容易被忽略的限制。4.6 常见报错速查表报错信息原因解决办法ERROR 3636: Recursive query aborted...超过 cte_max_recursion_depth检查数据是否有循环引用必要时调大参数ERROR 3577: recursive member must not be on the left side...递归成员写错了位置把引用自身的 SELECT 放到 UNION ALL 右侧ERROR 1146: Table doesnt existCTE 没有定义就引用确认 WITH RECURSIVE 的名称和后续 SELECT 名称一致ERROR 1054: Unknown column字段名写错或类型不匹配检查递归成员中的字段引用是否符合表结构查询结果少了第一行锚点成员没选对起点确认锚点 WHERE 条件是否覆盖根节点5. 8.0 之前的数据库怎么搞存储过程模拟方案与固定深度方案5.1 存储过程 临时表循环模拟递归如果你还在维护 MySQL 5.7 或更老版本没办法用递归 CTE那么存储过程加临时表是最接近递归效果的模拟方案。核心思路是建一个临时表存储结果从锚点开始循环每一轮把新查到的子节点插入临时表直到没有新节点。CREATE PROCEDURE get_subtree(IN root_id INT) BEGIN DECLARE last_count INT DEFAULT 0; CREATE TEMPORARY TABLE temp_dept LIKE department; CREATE TEMPORARY TABLE temp_next LIKE department; INSERT INTO temp_dept SELECT * FROM department WHERE id root_id; REPEAT INSERT INTO temp_next SELECT d.* FROM department d JOIN temp_dept t ON d.parent_id t.id WHERE d.id NOT IN (SELECT id FROM temp_next); INSERT INTO temp_dept SELECT * FROM temp_next; SELECT COUNT(*) INTO last_count FROM temp_next; DELETE FROM temp_next; UNTIL last_count 0 END REPEAT; SELECT * FROM temp_dept; DROP TEMPORARY TABLE temp_dept; DROP TEMPORARY TABLE temp_next; END;这个方案能用但也有很明显的缺点临时表在事务里要小心锁竞争而且每次循环都得检查NOT IN来防止重复插入逻辑比递归 CTE 复杂得多。我在老项目里维护过一段类似的代码后来升级到 8.0 后第一件事就是把它删掉换成递归查询。5.2 固定深度 LEFT JOIN 的适用场景固定深度 JOIN 并不是一无是处。如果你的业务层级在可预见的未来就是固定的两三层数据量不大而且查询频率极高那么用 JOIN 写死层级反而能借助索引拿到更可控的执行计划。比如评论就是固定的一级和二级回复不需要通用递归用两次 JOIN 就够用了。但一旦层级深度没有上限就不要用这个方案硬撑。5.3 要不要为了递归查询升级到 MySQL 8.0我的建议是如果你所在的团队已经评估过 8.0 的兼容性而你现在还在为树形数据写存储过程或者应用层递归那么升级带来的收益非常明确。除了递归 CTEMySQL 8.0 还有窗口函数、直方图、默认密码插件升级等一系列改进都是实实在在的生产力提升。如果因为历史原因暂时升不了至少可以在设计新表时把层级关系建模得规整一点给未来迁移留好余地。写作过程中我一直在想递归查询这个能力最值钱的地方不是它帮你少写了几行代码而是它把“怎么遍历一颗动态深度的树”这个逻辑从业务代码里彻底拿掉了。数据在哪一层SQL 不用改应用代码不用改只有数据变了树照常展开。这种减少心智负担的收益往往比省下的那点运行时间更值得重视。至于面试时怎么答这道题核心就一句话先给起点再给迭代规则数据库自动循环到没有新行为止。把这句话讲明白再把 UNION ALL 和 UNION、cte_max_recursion_depth 这两个点一补充就足够说明你是真正写过、踩过坑的。