递归CTE实战：用SQL搞定树形结构查询，告别“写死”代码

大家好我是小耶写功课只是为了我踩过的坑你们别再踩了你有没有写过这样的代码查组织架构先查顶层部门再循环查子部门每层写一个for循环。部门深度是5层就写5个循环深度不确定就写while循环不断查库。这种写法代码难看、性能差、还容易出bug。每次看到这种代码我都想问一句为什么不用递归CTE递归CTE是SQL标准中处理树形结构的官方解法MySQL 8.0、PostgreSQL、SQL Server都原生支持。树形结构查询有好几种写法每种写法各有优劣写法优点缺点循环查库直观容易理解代码冗长性能差N次查询递归CTE标准解法一次查询支持任意深度语法稍复杂需注意性能闭包表查询极快维护成本高适合读多写少路径枚举查询快易于理解更新路径成本高递归CTE可能是树形查询的最优解——语法简单清晰、一次搞定、不需要额外维护表结构。我把三个最常用的场景拆开讲看完就能上手。一、先搞清楚递归CTE是什么递归CTERecursive Common Table Expression说白了就是一个能“自己调用自己”的临时查询结果集。它由两部分组成锚点Anchor告诉数据库“从哪开始找”。比如查部门树的时候先找到最顶层的那个部门——WHERE parent_id IS NULL。递归成员Recursive Member告诉数据库“怎么继续往下找”。比如通过当前层的id去关联子部门的parent_id——ON d.parent_id dt.id。两部分用UNION ALL连接起来。数据库执行的时候先跑锚点拿到第一层结果然后拿着这些结果去跑递归成员拿到第二层再拿第二层去跑第三层……直到再也找不到新数据为止。整个流程可以理解成从起点出发每次拿当前查到的结果去查下一层一层层往下展开直到触底为止。就像玩一个“点开一个文件夹自动展开它里面所有子文件夹”的游戏只不过这个游戏是数据库帮你自动完成的。二、场景一遍历组织架构树——最基础的用法假设有一张部门表department字段为id、name、parent_id。要查出某个部门及其所有下级WITH RECURSIVE dept_tree AS ( -- 锚点从根部门开始 SELECT id, name, parent_id, 0 AS depth FROM department WHERE id 1 -- 指定根节点ID UNION ALL -- 递归找子部门 SELECT d.id, d.name, d.parent_id, dt.depth 1 FROM department d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT * FROM dept_tree ORDER BY depth, id;depth字段记录层级深度方便在应用层做缩进展示。ORDER BY depth保证了父级在子级之前输出。为什么这比循环查库好一次查询搞定所有层级不用在应用层写循环不用反复建立数据库连接性能提升肉眼可见。三、场景二面包屑导航——从叶子节点往上找电商网站的商品分类、博客的评论嵌套、系统的菜单路径——这些都需要“从当前位置追溯到根”。比如给一个商品分类ID生成“首页 手机 智能手机 iPhone 15”这样的面包屑WITH RECURSIVE breadcrumb AS ( -- 锚点从当前分类开始 SELECT id, name, parent_id, 1 AS level FROM category WHERE id 123 -- 指定当前分类ID UNION ALL -- 递归往上找父级 SELECT c.id, c.name, c.parent_id, b.level 1 FROM category c INNER JOIN breadcrumb b ON c.id b.parent_id ) SELECT * FROM breadcrumb ORDER BY level DESC;ORDER BY level DESC让根节点排在前面直接就能渲染面包屑。一层SQL搞定原本需要递归查询或多次查库才能完成的事情。四、场景三带路径的完整子树——行政区域查询有时候不仅要查出所有下级节点还要知道每个节点的完整路径。比如查询某个省下的所有市、区、街道WITH RECURSIVE region_tree AS ( -- 锚点从指定省份开始 SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(1000)) AS path FROM region WHERE id 440000 -- 广东省 UNION ALL -- 递归找下级行政区 SELECT r.id, r.name, r.parent_id, rt.depth 1, CONCAT(rt.path, , r.name) FROM region r INNER JOIN region_tree rt ON r.parent_id rt.id ) SELECT * FROM region_tree ORDER BY depth, id;path字段用CAST指定了足够长度CONCAT逐层拼接最终得到类似“广东省 广州市 天河区”的完整路径。这个模式在电商的SPU-SKU层级、BOM物料清单、组织权限树等场景中都非常实用。五、递归CTE的性能陷阱与避坑指南递归CTE虽强但用不好也会踩坑。陷阱1缺少索引每层全表扫描递归查询的每一层都会执行一次JOIN。如果parent_id没有索引每一层都要扫描全表性能急剧下降。解法确保parent_id上有索引。对于频繁查询树形结构的表这是必须的。陷阱2锚点写错查不出数据锚点必须明确指定根节点。用WHERE parent_id IS NULL或WHERE id 1都可以但要确保和业务数据一致。如果业务中用parent_id 0表示根节点锚点就要写成WHERE parent_id 0。陷阱3数据中存在环导致无限递归如果数据中出现A.parent_id B.id且B.parent_id A.id这样的环递归CTE会无限执行下去。解法限制递归深度。在递归成员中添加WHERE dt.depth 20这样的条件或在最外层加LIMIT。大多数数据库也提供了MAXRECURSION选项来控制最大迭代次数。陷阱4大数据量下性能下降当树形结构数据量极大百万级节点且查询频繁时递归CTE可能成为性能瓶颈。解法考虑用闭包表Closure Table或路径枚举Path Enumeration方案来替代递归CTE。这两种方案以空间换时间适合读多写少的场景。六、总结递归CTE是处理树形结构查询的标准写法三个核心场景覆盖了大部分日常需求从根到叶遍历组织架构、商品分类——WHERE parent_id IS NULL往下找从叶到根面包屑导航、权限继承——WHERE id 当前节点往上找带路径的子树行政区域、BOM清单——用CONCAT拼接完整路径递归CTE的语法看起来有点吓人但拆开看就是“锚点 UNION ALL 递归关联”三部分。写的时候记住三件事锚点写对、索引建好、防环做好剩下的就是多练。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~