1. 递归 SQL 到底能解决什么问题树形数据的真实场景做数据库开发的同学迟早会遇到一类需求表里存着父子关系要查出某个节点下的全部子孙节点或者把一张带有层级关系的数据表展开成带缩进的树状结构。这类需求的典型形态就是一张员工表里有一列manager_id指向自己的上级或者商品分类表里parent_id指向父分类。在没有递归 SQL 之前我们面临一个非常尴尬的局面要么在应用层写循环多次查询数据库再把结果拼成树要么用临时表反复迭代插入把数据一层一层捞出来。这两种办法都能跑但代码丑、性能烂而且一旦层级深了光维护那个循环逻辑就能把人绕晕。递归 SQL递归 CTE即 WITH RECURSIVE 语法把这件复杂事变成了一个干净的声明式查询。你只需要告诉数据库两件事树从哪里开始锚点每一层怎么找下一层递归条件剩下的展开工作数据库自己完成。这在组织架构查询、商品分类树、地区行政区划、BOM 物料清单、评论回复楼中楼、菜单权限树等场景里都是实打实的刚需。这篇文章我会从一个标准的员工表案例出发完整演示递归 CTE 的用法从最基础的自顶向下查询、层级路径拼接到自底向上汇总、深度限制、循环保护再到多种树形存储方案的对比和选型。内容会偏实战你可以打开数据库照着语句敲一遍很多疑惑会在动手过程中迎刃而解。本文涉及的知识点适合已经掌握基础 SQL 的开发者不管你做的是 MySQL 8.0、PostgreSQL、SQL Server 还是 Oracle核心思路完全通用差异我会在相应位置单独提。2. 递归 CTE 的核心机制与语法拆解2.1 三个必须理解的组成部分递归 CTE 的语法并不复杂复杂的是理解它背后的执行过程。任何一条递归 CTE 都包含三个部分第一部分是锚点成员Anchor Member。它是整个递归的起点一条普通的 SELECT 语句负责从树形数据中挑出第一层数据。拿员工表举例如果我们要查某个总监下面的所有下属锚点就是找到这个总监本人或者找到所有顶级节点manager_id 为空的员工。第二部分是递归成员Recursive Member。它引用 CTE 自己负责从当前这一层结果出发找到下一层的数据。递归成员的每一次执行都会基于上一次产生的数据集再次查询直到查不出新数据为止。第三部分是连接方式。绝大多数情况下用的是UNION ALL当然也可以用UNION去重。在递归 CTE 里UNION ALL不只是合并结果集那么简单它承担着把上一轮查出的数据作为下一次查询的输入这个核心职责。用一个极简的类比理解这个结构你在一座大厦里找所有房间锚点就是服务台你问服务台在哪一层递归成员就是根据当前所在的楼层找到同一层里所有挂着指引牌的通道沿着通道走到下一层再重复。2.2 执行过程逐层拆解我直接用一个简单例子走一遍执行流程。假设有一张表employee字段为emp_id、emp_name、manager_id数据如下emp_idemp_namemanager_id1张三NULL2李四13王五14赵六25孙七26周八4现在这条 SQL 查张三的所有下级WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level FROM employee WHERE emp_id 1 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.level 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT * FROM emp_tree ORDER BY level, emp_id;执行过程分成这样几步第一步执行锚点查询找到张三emp_id1此时emp_tree临时结果集里有一条记录管理层级 level 记为 1。第二步执行递归成员查询拿emp_tree里刚产生的这一条记录去 JOIN 员工表条件是e.manager_id t.emp_id。张三的 emp_id 是 1于是查到李四manager_id1和王五manager_id1它们的 level 记为 2。第三步重复递归成员查询这次输入是李四和王五。李四的 emp_id 是 2查到赵六和孙七level 为 3王五的 emp_id 是 3没有任何员工的 manager_id 指向他查不到数据。第四步输入是赵六和孙七。赵六的 emp_id 是 4查到周八manager_id4level 为 4孙七没有任何下级无结果。第五步输入只有周八周八没有下级查询结果为空集递归停止。最终结果集中包含张三、李四、王五、赵六、孙七、周八六条数据。你看数据库内部其实就是反复执行同一条 JOIN 语句每一轮拿上一轮生成的集合去查新数据查不动了就自动停下来。这条 SQL 里的1 AS level我强烈建议保留实际业务里太常用了后面所有带缩进、带层级路径的查询都依赖这个字段。2.3 各数据库的语法差异虽然思路一致但不同数据库的写法还是有区别做开发的人最烦的就是换数据库重新学一遍。我顺手整理一下数据库递归语法注意点MySQL 8.0WITH RECURSIVE ...5.7 及以下版本不支持见 2.4 节替代方案PostgreSQLWITH RECURSIVE ...支持最完整包括递归后 SELECT 排序SQL ServerWITH ... (列名) AS (...)不用写 RECURSIVE 关键字默认递归次数上限为 32767OracleWITH ... AS (...)早期版本用START WITH ... CONNECT BYSQLiteWITH RECURSIVE ...相当标准写起来很舒服Oracle 的CONNECT BY是一种前置层级查询语法做组织架构查询也很方便但一旦需要同时做聚合、多条件过滤和路径拼接CTE 写法更灵活、更容易理解所以本文统一用 CTE 讲解。2.4 MySQL 旧版本的替代方案如果你的生产环境还是 MySQL 5.6/5.7没法定级到 8.0那递归 CTE 用不了。这时候能选的方案不多最常见的是临时表迭代或者应用层递归。临时表迭代的思路是建一张临时表存结果先插入顶级节点然后循环执行INSERT INTO 临时表 SELECT ... FROM 原表 JOIN 临时表 ON ...直到插入行数为 0。这个方案要在存储过程里写循环SQL 代码会多一些但在不支持递归 CTE 的环境中已经算是最优雅的替代了。说句实在话作为一个从 MySQL 5.6 时代一路摸爬滚打过来的人我的建议是如果条件允许尽早升到 8.0 以上。临时表迭代方案虽然能用但维护成本高存储过程调试起来也更痛苦新项目没必要在这方面将就。3. 树形数据查询的完整实操3.1 准备测试表和数据实操之前先把环境搭好。我用 MySQL 8.0 做演示同时保证语句在 PostgreSQL 和 SQL Server 上也能基本照搬。建表语句如下CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), manager_id INT, salary DECIMAL(10, 2), CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employee(emp_id) );插入一套带多层关系的测试数据覆盖一个根节点、多级子节点、叶子节点等多种情况INSERT INTO employee VALUES (1, 张三, NULL, 50000.00), (2, 李四, 1, 30000.00), (3, 王五, 1, 28000.00), (4, 赵六, 2, 20000.00), (5, 孙七, 2, 18000.00), (6, 周八, 4, 12000.00), (7, 吴九, 4, 11000.00), (8, 郑十, 5, 10000.00);这套数据的树结构是张三下面有李四和王五两个二级节点李四下面有赵六和孙七两个三级节点赵六下面又有周八吴九两个四级节点孙七下面有郑十一个四级节点王五下面暂时没人。建这张表我特意加了salary字段后面做自底向上的聚合汇总时会用到光有层级关系和姓名很多树形操作的演示会显得单薄。3.2 基础查询从指定节点向下展开整棵子树这是最常用的需求查张三下面的所有员工包括间接下级。上面那节已经演示过完整 SQL 了但这里我要稍微升级一下把层级序号列得清楚一点WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level FROM employee WHERE emp_id 1 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.level 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT emp_id, emp_name, manager_id, level FROM emp_tree ORDER BY level, emp_id;结果会是这样emp_idemp_namemanager_idlevel1张三NULL12李四123王五124赵六235孙七236周八447吴九448郑十54这个结果集可以直接交给前端做树形渲染。前端拿到manager_id字段就能自己把数据拼成嵌套结构后端不用做任何额外处理。很多同学第一次写递归 CTE 时最容易犯的错误是JOIN 条件方向搞反。正确写法是e.manager_id t.emp_id意思是下一层员工的领导是上一层员工的 ID。有人会写成e.emp_id t.manager_id这样查出来的就是自己的上级完全反了。3.3 进阶查询拼接层级路径和使用 GROUP_CONCAT实际展示树的时候光有 level 字段还不够通常还要看到从根节点到当前节点的完整路径。比如张三 / 李四 / 赵六 / 周八用户一眼就知道这条数据在组织里的位置。递归 CTE 里拼接路径非常方便定义一个path字段每一层在上一层的路径末尾追加当前节点名WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level, CAST(emp_name AS CHAR(500)) AS path FROM employee WHERE emp_id 1 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.level 1, CONCAT(t.path, / , e.emp_name) FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT emp_id, emp_name, level, path FROM emp_tree ORDER BY level, emp_id;执行结果中周八的 path 会是张三 / 李四 / 赵六 / 周八郑十的 path 会是张三 / 李四 / 孙七 / 郑十。这里必须提醒一个容易出大问题的细节MySQL 里递归 CTE 定义的字段长度是递归传递的。第一层CAST(emp_name AS CHAR(500))定义了 path 字段最大 500 个字符如果树特别深、路径特别长超过 500 字符就会报错或者被截断。实际项目里我一般直接给到 1000 或者 2000或者换成VARCHAR的大长度根据业务脑补一下最深层级数。PostgreSQL 里做法略有不同用||运算符拼接字符串初始path直接写emp_name::text更简洁。SQL Server 则是CAST(emp_name AS VARCHAR(MAX))都没有什么问题关键是先看自己用的数据库类型。想在同一层下按照某个字段排名比如每个部门下让工资从高到低排序可以把这个层次查询包一层外面再加窗口函数WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, salary, 1 AS level FROM employee WHERE emp_id 1 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, e.salary, t.level 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT emp_id, emp_name, level, salary, ROW_NUMBER() OVER (PARTITION BY level ORDER BY salary DESC) AS rank_in_level FROM emp_tree ORDER BY level, rank_in_level;窗口函数可以把每个级别里的员工按工资排序这在做层级报表时的实用程度非常高业务方经常提这种奇奇怪怪的需求。3.4 自底向上聚合计算每个节点的下级工资总额树形查询不仅有自上而下的展开还有自底向上的汇总。经典场景是每个管理者所管团队的工资总额是多少也就是算出张三整个树的总工资、李四子树的总工资等等。递归天生是自上而下的但我们可以先把整棵树每个节点的路径算出来再通过路径判断哪些节点归属于某个上级WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, salary, 1 AS level FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, e.salary, t.level 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT e.emp_id, e.emp_name, SUM(t.salary) AS team_total_salary, COUNT(t.emp_id) AS team_headcount FROM emp_tree e LEFT JOIN emp_tree t ON t.path LIKE CONCAT(e.path, %) GROUP BY e.emp_id, e.emp_name ORDER BY team_total_salary DESC;这个写法依赖 path 字段做归属判断逻辑上是找出所有路径以我的路径开头的员工他们的工资全算进我的团队。这样做虽然直观但性能上 LIKE 前缀匹配不便宜数据量上千上万可能还好几十万节点以上就得考虑别的方案。更正统的做法是反着递归从叶子节点向父节点方向聚合。但这种做法在纯 SQL 里写起来相当绕需要外部辅助表和多次更新实操中我反而更常用上面这个 path 方案。毕竟大多数递归查询面对的树规模都在几千到几万条记录的级别在可接受的性能范围内选最易理解的写法才是工程上的智慧。提示聚合成员的归属判断容易把自己也算进去。上面 SQL 里统计SUM(t.salary)是包含当前节点自己的工资因为自己的 path 肯定以自己 path 开头。如果业务上统计下属团队不含自己请在聚合时加条件WHERE t.emp_id e.emp_id算人头同理。3.5 判断叶子节点与完整路径编号树形查询里另外两个高频操作是判断叶子节点和给节点生成层级路径编号。判断叶子节点一个节点没有下级它就是叶子。SQL 上很好写先递归出整棵树再用NOT EXISTS找那些没有任何员工的 manager_id 等于该节点 emp_id的节点WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.level 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT emp_id, emp_name, level, CASE WHEN NOT EXISTS ( SELECT 1 FROM employee sub WHERE sub.manager_id emp_tree.emp_id ) THEN leaf ELSE internal END AS node_type FROM emp_tree ORDER BY level, emp_id;层级路径编号是另一种展示方式效果类似/1/2/4/这种格式前端解析起来方便得很WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level, CAST(/1/ AS CHAR(500)) AS tree_path FROM employee WHERE emp_id 1 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.level 1, CONCAT(t.tree_path, e.emp_id, /) FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id ) SELECT emp_id, emp_name, tree_path FROM emp_tree ORDER BY tree_path;这种路径格式天然具备排序优势同一父节点下的数据会紧挨在一起而且可以通过tree_path LIKE /1/%快速过滤某个子树。我在实际处理分类树导出时特别喜欢用这个格式Excel 里透视图表直接能识别层级关系。4. 性能优化、死循环防护与问题排查实录4.1 最常见事故无限递归与深度限制写递归 SQL 踩得最狠的坑就是无限递归。树形数据如果存在环路比如 A 的上级是 BB 的上级又是 A递归查询就会永远循环下去把数据库跑死。真实业务中环路数据比想象中更常见——人员调动时上级没及时更新、历史数据迁移时丢失了链条、Excel 导入时循环引用都可能造成环路。我见过不止一次有人在生产环境手滑执行了递归查询然后数据库 CPU 直接飙满。标准解法有两个第一给递归成员加深度限制。在所有数据库里都能通用在递归成员 SELECT 的 WHERE 条件里带上 level 判断WITH RECURSIVE emp_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level FROM employee WHERE emp_id 1 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.level 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id t.emp_id WHERE t.level 10 ) SELECT * FROM emp_tree;这里t.level 10限制最多递归 10 层超过就直接停。实际业务里组织架构一般不会超过这个深度既能防止死循环也不影响正常数据查询。第二利用数据库自带的限制参数。MySQL 里有个会话级变量cte_max_recursion_depth默认值比较小可以把它调大但不要取消限制SET SESSION cte_max_recursion_depth 1000;SQL Server 默认递归上限是MAXRECURSION 32767可以显式指定OPTION (MAXRECURSION 200);PostgreSQL 没有内置深度限制所以更得靠业务条件自己把关。我一直觉得有条件就两层保险都上CTE 里写明确深度上限数据库参数傻上限兜底。这样就算哪天代码里忘了限制数据库也不会彻底被拖垮。顺手说一句Oracle 的CONNECT BY可以用LEVEL 10做同样的限制思路一模一样。4.2 性能优化核心递归层级越浅越省力递归 SQL 性能问题通常出在两个地方递归成员中 JOIN 的效率和递归轮次的数量。JOIN 效率这块老生常谈但永远有效被 JOIN 的关联字段必须有索引。employee.manager_id上建一个索引绝大多数数据库递归查询的揽结成本会大幅下降CREATE INDEX idx_emp_manager ON employee(manager_id);还是那句话递归 CTE 每一层都会拿上一次结果去 JOIN 原表这个索引的命中率是百分之百效果非常明显。如果没建索引一次递归可能要多扫好几遍全表树的深度一上去就是指数级灾难。轮次数量方面递归查询的时间复杂度近似于O(深度 × 每层扫描成本)。深度不是你能随便控制的但起始锚点的选择会影响每层扫描范围。从根节点往下查和从某个叶子节点往上查的成本差异很大实际需求里能从上往下就不要从中间层起查。这里还要提一下UNION和UNION ALL的性能差异。递归 CTE 如果用了UNION去重数据库每次迭代后都要对所有已生成的记录做一次去重比较开销明显大于UNION ALL。只要关系业务上不会产生重复节点一律用UNION ALL。如果担心重复可以在最终 SELECT 里 DISTINCT不要在整个迭代过程中反复消耗性能。4.3 常见问题速查表我把实战中遇到的高频问题整理成一个速查表方便你排查问题现象可能原因解决方案查询一直不返回结果数据存在环路加显式深度限制参数结果缺少部分节点JOIN 条件方向写反检查e.manager_id t.emp_id是否朝向子节点路径字段被截断CTE 字段长度定义过小初始 CAST 或 VARCHAR 长度放大查询速度越来越慢关联字段无索引给 manager_id 建索引结果有重复行多路径可达同一节点递归里 UNION 或最终 DISTINCT报错超出递归深度数据库参数限制调大参数并配合业务条件限制level 列始终为 1递归成员里忘记1递归部分写t.level 14.4 查不出数据的隐蔽原因递归查询查不出数据时很多人第一反应是数据不存在但还有一种隐蔽情况根节点没有匹配上锚点条件。比如你想查王五的下级但王五在表里压根不存在锚点返回空集整个递归结果自然为空。这种问题肉眼很难发现尤其数据量大时几乎觉察不到。排查方式是先单独跑一下锚点 SELECT确定能查出的节点确实存在。另外一个隐蔽问题是数据类型隐式转换。JOIN 时如果manager_id是字符串类型而 emp_id 是整数类型某些数据库在隐式转换上可能行为诡异。开发人员在设计表结构时就应该保证关联字段的数据类型完全一致别埋这种低级雷。5. 树形数据的存储方案选型与延伸思路5.1 四种常见方案对比递归 CTE 只是查询手段真正的数据存储方案是另外一门学问。标准教材里讲树形数据有四种存储模型我直接拉个表格对比方案核心思路查询子树查询路径写入适用场景邻接表每行存 parent_id递归查询递归拼接简单小中型数据结构灵活路径枚举每行存完整路径字符串LIKE 前缀匹配直接读取简单读多写少层数浅闭包表额外存所有祖先-后代对直接关联查询直接读取写放大明显查询极频繁层级深嵌套集左右值编号范围查询较复杂更新代价高极少变动纯读场景邻接表就是本文一直用的方式最直观、写入最简单但要靠递归来查。路径枚举需要额外维护一个路径字段但查询时可以免掉递归直接用前缀匹配。闭包表查询效率最高但每次插入一个节点可能要同时插入多对关系写入和维护复杂度都大。嵌套集用左右值编号实现查询效率高但树结构调整时几乎要重算所有节点适合那种一层不变的结构。5.2 我的选型建议选型这事没有绝对答案结合我处理过的项目思路大概是这样如果树的规模在几万条以内变化频率中等优先选邻接表加递归 CTE。理由很朴素存储结构最简单业务理解零成本递归查询虽然有一定计算开销但这个数据量级下根本无所谓。这是目前绝大多数中小型项目的合理起点。如果树特别深、查询极其频繁、几乎没有写入可以考虑嵌套集。比如某个部门的组织架构一年都不动一次但是每天要被系统查几百上千次嵌套集的LEFT/RIGHT范围查询性能确实好。如果树结构变动频繁但查询要求也高闭包表值得考虑。代价是写入时要多插入多条记录但可以考虑用定时任务或触发器维护闭包关系把写入复杂度隔离在业务层之外。做技术选型最容易犯的错误是为了先进性而选型。我曾经在一个只有几百条分类数据的后台管理系统里见过别人用闭包表插入一个分类要维护十几条记录带来的收益却微乎其微。大部分系统根本到不了那个性能瓶颈简单方案就够了。5.3 递归思路的横向扩展学会递归 CTE 后你会发现它的用处远超查询树形数据。日期序列生成、数据补全、数数字、拆字符串全都能用递归解决。比如生成连续日期序列这在做报表填充空日期时超级实用WITH RECURSIVE date_range AS ( SELECT DATE(2025-01-01) AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt 2025-01-31 ) SELECT dt FROM date_range;比如拆解逗号分隔的字符串把一行数据拆成多行WITH RECURSIVE split_string AS ( SELECT a,b,c,d AS full_str, SUBSTRING_INDEX(a,b,c,d, ,, 1) AS val, 1 AS pos UNION ALL SELECT full_str, SUBSTRING_INDEX(full_str, ,, pos 1), pos 1 FROM split_string WHERE pos CHAR_LENGTH(full_str) - CHAR_LENGTH(REPLACE(full_str, ,, )) 1 ) SELECT val FROM split_string WHERE val ;这些场景说明递归 CTE 是一种思维方式而不是某个业务的专用工具。一旦你习惯了自己调自己的写法很多以前要写循环或者拼一堆 CTE 的问题都能秒解。我在实际工作中非常深刻地体会到数据查询写得多的人并不是记住的语法多而是脑子里有一种用集合思维解决问题的直觉。递归 CTE 就是这种直觉的重要一块拼图。6. 实战总结与个人心得写了这么多最后分享几点我从实际项目里踩坑攒下的心得。第一递归 CTE 的调试难度比普通查询高一个量级。建议先在锚点 SELECT 和递归成员 SELECT 各自单独执行一遍确认都能查出正确数据再合并起来看整体结果。一旦结果不对就这么二分排查效率比对着大 SQL 干瞪眼高得多。第二递归 CTE 的代码组织直接决定可维护性。把level、path这些辅助字段都放在 CTE 内部计算好外部查询只负责展示。不要在外部再用 JOIN 去补字段否则整个查询会嵌套得非常难看。我第一次写的时候图省事把路径拼接拆到了外部处理结果树一深就出各种问题后来还是老老实实塞进 CTE 里。第三生产环境一定要加深度保护这是底线。哪怕你觉得当前数据结构绝对不会出环路也要加上限制。数据问题永远比预期来得快等递归跑死数据库再去救火体验极其糟糕。我那个 cte_max_recursion_depth 设置到现在为止已经帮我挡住了至少三次事故。第四把常用树形查询封装成视图是个好习惯。比如带层级、路径、叶子标识的员工树查询封装成一个视图业务层就能直接SELECT * FROM v_emp_tree WHERE manager_id ?不用每个报表模块都重复写递归逻辑。这种方式维护起来也方便树结构逻辑改动只动一处别的模块无感知。递归 SQL 这个知识点说难不难说简单也有一大堆细节。但只要学会了锚点 递归成员 UNION ALL这套骨架加上对层级深度、循环防护、性能索引这些配套细节的理解绝大部分树形数据需求都能信手拈来。希望这篇文章能帮你把这块内容一举拿下。