1. 你大概率也遇到过层级表查“上级路径”到底难在哪先交代一下背景。做组织架构、商品分类、权限菜单、评论回复链这类业务时数据表十有八九是“邻接表”设计每一行只保存一个parent_id指向父节点。这种结构特别符合人的直觉插入数据也不用关心顺序问题随手一行INSERT就完事。但真正用起来就麻烦了最典型的需求就是标题里写的“上级ID路径查询”。举个例子分类表category里面有上下级依赖关系我想知道“苹果手机”这个节点的完整上级链也就是“手机数码 / 手机 / 智能机 / 苹果”这串路径或者至少拿到“1, 2, 3, 5”这样一串祖先ID。这个需求看起来简单但层级不定有的节点 3 层就到顶了有的节点可能挂 10 层你没办法提前知道该JOIN几次自己。在我接手的老项目里遇到这种需求最常干的事就是先把所有数据查出来扔到应用层用 Java/Python 写个递归方法去拼父子关系。业务小的项目这么玩没问题数据量一大光是N1查询就能把接口拖死。后来换了 MySQL 8.0有了递归 CTE这类查询终于可以在一条 SQL 里解决干净。这篇就围绕“上级ID路径查询”这个具体场景把 CTE 怎么用、递归怎么跑、路径怎么拼、性能怎么优化、有哪些坑一条条掰开讲清楚。不管你之前有没有用过WITH RECURSIVE看完都能直接拿 SQL 去改业务。1.1 邻接表最直观也最磨人的一张树表先把示例表建好后面所有 SQL 都跑在这张表上CREATE TABLE category ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, parent_id INT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_parent_id (parent_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里parent_id 0表示顶级节点不需要NULL避免后面递归判断时还得处理空值。插入几条示例数据INSERT INTO category (id, name, parent_id) VALUES (1, 手机数码, 0), (2, 手机, 1), (3, 智能机, 2), (4, 功能机, 2), (5, 苹果, 3), (6, 华为, 3), (7, 三星, 3), (8, 平板电脑, 1), (9, 旧款功能机, 4), (10, 诺基亚, 9);如果我现在想查id 9旧款功能机的祖先链肉眼可以数出来是9 - 4 - 2 - 1也就是路径“手机数码 / 手机 / 功能机 / 旧款功能机”。但程序不认识“肉眼”它需要一条能自动沿着parent_id往上爬的逻辑。1.2 没有递归的年代大家是怎么凑合查的MySQL 8.0 之前没有递归 CTE处理这种需求大概有四种土办法每一种都各有各的难受第一种固定层级 JOIN。如果业务方说“最多不会超过 4 层”那就反复LEFT JOIN自己 4 次把每一层的节点都查出来。这种写法最好懂但层级一改SQL 就得跟着改而且一旦数据里有第 5 层结果就直接丢了。第二种存储过程 循环。在存储过程里逐层查询把结果塞进临时表直到找不到父节点为止。好处是通用坏处是代码量大、调试麻烦而且存储过程不方便和普通业务 SQL 组合更没法直接嵌到报表查询里。第三种应用层递归。把所有分类一次性查出来在内存里用 Map 组装树。在数据量不大、分类总数就几千条的场景下这个方案很好用但如果你想“只查某一个子树”就不得不把全表数据都捞出来属于杀鸡用牛刀。第四种设计层面换方案。比如用“路径枚举”表每行节点直接存ancestors字段或者用“闭包表”专门存所有父子关系。这些方案查询效率确实高但写入时维护成本极大增删改一个节点可能要连带更新几十上百行对大部分中小业务来说属于过度设计。所以递归 CTE 的价值就很清楚了既不用改表结构也不用写存储过程一条 SQL 拿捏任意层级。1.3 其实你要的只是两句话祖先链和全路径“上级ID路径查询”这个标题展开来看无非两种情况给定节点 ID查出它所有上级节点的 ID 列表不管顺序。给定节点 ID从根节点到当前节点拼出完整的 ID 路径比如1/2/4/9。后面第三、四、五节分别来解决这两个问题外加一个“向下查所有子孙节点”的常用变体。不过在动手写 SQL 之前得先把WITH RECURSIVE的运行逻辑搞清楚不然很容易写出“看上去对、跑起来错”的 SQL。2. WITH RECURSIVE 怎么“递归”锚点、迭代与终止WITH RECURSIVE的语法其实特别简单核心就是两部分锚点成员anchor member和递归成员recursive member。中间用UNION [ALL | DISTINCT]连起来。WITH RECURSIVE cte_name (列名1, 列名2, ...) AS ( -- 锚点成员查询的起点不引用 CTE 自身 SELECT ... UNION ALL -- 递归成员引用 cte_name 自身反复迭代 SELECT ... ) SELECT * FROM cte_name;很多人第一次写会懵觉得“递归”这个词太抽象。换个角度理解就顺了锚点决定了迭代从哪里开始递归成员决定了每一次迭代怎么从结果集里再长出下一批数据当某次迭代没有产生任何新行时递归自动终止。2.1 一段最简单的代码从数字1加到5先看 MySQL 官方文档里最经典的数列例子WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM cte WHERE n 5 ) SELECT n FROM cte;执行结果n --- 1 2 3 4 5拆开看它的执行过程先执行锚点SELECT 1结果集里有一条数据n 1。执行递归成员SELECT n 1 FROM cte WHERE n 5注意这里cte代表的是上一步刚产生的数据而不是全部历史数据。上一步结果是n 1所以这一步产出n 2放进最终结果集。第三步迭代拿n 2通过n 1产出n 3。一直迭代到n 5。此时递归成员还是被执行了一次的它拿n 5尝试生产n 6但WHERE n 5过滤掉了这一行没有产生新数据迭代就此停止。这个例子请多看两遍它是后面所有层级查询的基础。关键点在于递归成员每次读到的cte只是上一轮新产生的行不包含前几轮产生的历史行。所以如果我们想一层层往上爬父节点就得在递归成员里把当前行cte.parent_id作为条件去关联原表。2.2 递归成员和锚点成员之间的关系锚点和递归成员之间有个硬性要求两边查出来的列数必须一致。如果锚点查 3 列递归成员也必须是 3 列顺序一一对应。否则 MySQL 直接报错错误号大概是ERROR 3504实际上就是列数量对不上。列名不用在锚点里指定类型但递归成员里的字段类型要和锚点兼容。比如锚点里CAST(id AS CHAR(500))转成了字符串递归成员里CONCAT(...)拼接出来的也是字符串这样才能兼容。还有一点容易被忽略递归成员不能使用聚合函数、窗口函数包括GROUP BY、DISTINCT这类操作。MySQL 官方文档限制得很明确。如果有这种需要必须把递归 CTE 当成一个“临时结果源”在外层再GROUP BY。2.3 UNION ALL 还是 UNION DISTINCT写递归 CTE 时UNION和UNION ALL都能用。UNION默认带DISTINCT会对最终结果去重UNION ALL不去重。在层级树查询场景里我建议直接用UNION ALL。原因有二。第一树形结构只要数据本身没有环从同一个节点出发沿着parent_id往上爬路径是唯一的不存在重复行。去重没意义纯属白白增加计算成本。第二在某些数据量大的场景UNION DISTINCT会在每一轮迭代都做排序去重性能明显变差。当然如果表里存在脏数据、循环引用UNION DISTINCT有时能靠去重“糊弄”过去但这属于掩盖问题后面专门讲循环引用这个坑。3. 自底向上查询给出任意节点找到它所有上级现在进入正题标题里的“上级ID路径查询”核心 SQL 长这样WITH RECURSIVE cte AS ( SELECT id, parent_id, name, 1 AS lvl FROM category WHERE id 9 UNION ALL SELECT c.id, c.parent_id, c.name, cte.lvl 1 FROM category c INNER JOIN cte ON c.id cte.parent_id ) SELECT id, parent_id, name, lvl FROM cte ORDER BY lvl;执行结果id parent_id name lvl 9 4 旧款功能机 1 4 2 功能机 2 2 1 手机 3 1 0 手机数码 43.1 获取全部祖先ID核心SQL如果你只需要 ID 列表那SELECT里只取id就行。这条 SQL 里的INNER JOIN cte ON c.id cte.parent_id是灵魂它做的操作是拿当前已找到的节点ID去原表里找谁是它的父节点。锚点WHERE id 9把起点定为“旧款功能机”它先进入结果集。然后递归成员拿着9这个值去关联原表category找到c.id 9这一行读出它的parent_id 4于是4进入结果集。下一轮拿着结果集里的行id 4再关联一次找到id 4的parent_id 2把2加进结果集。等拿到id 1时它的parent_id 0原表里没有id 0的行INNER JOIN匹配不到递归终止。这里有个初学者容易搞反的细节向上查是c.id cte.parent_id向下查是c.parent_id cte.id两者正好相反。如果写反了查id 9的上级结果会变成查它的所有子孙一脸懵。别问我是怎么知道的。3.2 带名称、带层级深度不迷路上面 SQL 里加了lvl字段表示“当前节点距起点隔了几层”。这个字段非常实用尤其在展示树形表格时能直接用来做缩进。比如前端拿到结果后按lvl乘以固定像素做缩进就是一个天然的多级列表。如果你还想在结果里顺便带上“每次迭代的源节点 ID”可以用一个start_id字段保留最初起点WITH RECURSIVE cte AS ( SELECT id, parent_id, name, id AS start_id, 1 AS lvl FROM category WHERE id 9 UNION ALL SELECT c.id, c.parent_id, c.name, cte.start_id, cte.lvl 1 FROM category c INNER JOIN cte ON c.id cte.parent_id ) SELECT start_id, id, parent_id, name, lvl FROM cte ORDER BY lvl;当你需要在一个 CTE 里同时查多个节点的祖先链时比如传入一批 ID锚点直接改成WHERE id IN (5, 6, 9)这个start_id就能帮你区分每一条链分别属于谁。3.3 直接生成逗号分隔的完整路径很多时候我们不光要“有哪些祖先”还要“祖先按从根到叶的顺序连起来的一串”。有两种写法都可以做到。写法一递归过程中用CONCAT累积路径。起点是id 9先让它自己的路径是9往上递归找到id 4时路径变成4,9再往上找到id 2时变成2,4,9最后到根变成1,2,4,9。SQL 如下WITH RECURSIVE cte AS ( SELECT id, parent_id, name, CAST(id AS CHAR(500)) AS id_path, CAST(name AS CHAR(500)) AS name_path FROM category WHERE id 9 UNION ALL SELECT c.id, c.parent_id, c.name, CONCAT(c.id, ,, cte.id_path), CONCAT(c.name, /, cte.name_path) FROM category c INNER JOIN cte ON c.id cte.parent_id ) SELECT id, id_path, name_path FROM cte ORDER BY LENGTH(id_path) - LENGTH(REPLACE(id_path, ,, )) DESC;结果里层次最深的那一行就是完整路径id id_path name_path 9 9 旧款功能机 4 4,9 功能机/旧款功能机 2 2,4,9 手机/功能机/旧款功能机 1 1,2,4,9 手机数码/手机/功能机/旧款功能机注意锚点里我写了CAST(id AS CHAR(500))这一步不能省。如果不转类型递归成员里CONCAT(c.id, ,, cte.id_path)算出来的可能是其他类型后面迭代再拼接时容易出问题。转成字符串既保证类型一致也避免隐式转换导致索引失效。写法二递归出祖先集合后用GROUP_CONCAT聚合。个人更推荐这种因为它思路更简单不需要维护一个越来越长的字符串WITH RECURSIVE cte AS ( SELECT id, parent_id, name, 1 AS lvl FROM category WHERE id 9 UNION ALL SELECT c.id, c.parent_id, c.name, cte.lvl 1 FROM category c INNER JOIN cte ON c.id cte.parent_id ) SELECT GROUP_CONCAT(id ORDER BY lvl DESC) AS ancestor_ids, GROUP_CONCAT(name ORDER BY lvl DESC SEPARATOR /) AS ancestor_names FROM cte;查询结果ancestor_ids ancestor_names 1,2,4,9 手机数码/手机/功能机/旧款功能机这段 SQL 的思路是先把祖先链递归出来每个节点都带一个lvl层级号然后用GROUP_CONCAT ... ORDER BY lvl DESC把层级最浅的根节点排在最前面正好形成“从根到当前节点”的路径。用GROUP_CONCAT有一个隐藏限制它默认最大长度只有 1024 字节如果树的层级深、路径长会被静默截断。解决方法是先调大会话变量SET SESSION group_concat_max_len 1000000;建议只要是正式报表查询都先执行这一句避免线上出现“路径怎么少了一截”的诡异问题。3.4 这个方向最容易踩的坑有一种错误写法很有迷惑性递归成员里用WHERE cte.parent_id c.id或者WHERE c.parent_id cte.id乍一看好像也在“找上级”实际上查出来的全是子节点。判断方向对不对最好的方法是拿一层数据手推一遍。拿id 9为例如果你发现结果里出现了id 10那肯定方向反了因为10是9的子节点而不是父节点。另一个坑是锚点里忘了加WHERE条件直接把全表所有节点都当起点。你以为自己写的“递归”其实变成了“每一棵树都从上往下跑一遍”结果集直接爆炸轻则数量翻倍重则卡死。4. 自顶向下查询给出任意节点展开它的整棵子树既然是层级表除了“查上级路径”还有一个同等的刚需查某个节点下面挂了哪些子孙节点。虽然它不完全等于标题里的“上级ID路径查询”但它是递归 CTE 最常见的另一半用法而且原理互通顺手写清楚。思想就是把第三节的 JOIN 条件反过来——拿当前节点的id去匹配原表里的parent_idWITH RECURSIVE cte AS ( SELECT id, parent_id, name, 1 AS lvl FROM category WHERE id 1 UNION ALL SELECT c.id, c.parent_id, c.name, cte.lvl 1 FROM category c INNER JOIN cte ON c.parent_id cte.id ) SELECT id, parent_id, name, lvl FROM cte ORDER BY lvl, id;查询结果节选id parent_id name lvl 1 0 手机数码 1 2 1 手机 2 8 1 平板电脑 2 3 2 智能机 3 4 2 功能机 3 5 3 苹果 4 6 3 华为 4 7 3 三星 4 9 4 旧款功能机 4 10 9 诺基亚 54.1 获取全部子孙节点及其层级这个结果集就是一个扁平化的“整棵子树”lvl字段从 1 开始往下递增。实际项目中我会把这个结果交给前端组件渲染成目录树因为分层信息已经完整前端不用再做任何递归计算。如果只想要某个指定层级以下的数据比如只要两层外层加WHERE lvl 2就行。想要每个父节点下面直接挂了多少子节点可以配合GROUP BY统计各层节点数WITH RECURSIVE cte AS ( SELECT id, parent_id, name, 1 AS lvl FROM category WHERE id 1 UNION ALL SELECT c.id, c.parent_id, c.name, cte.lvl 1 FROM category c INNER JOIN cte ON c.parent_id cte.id ) SELECT lvl, COUNT(*) AS node_count FROM cte GROUP BY lvl ORDER BY lvl;结果lvl node_count 1 1 2 2 3 3 4 3 5 1这种“按层级统计节点数”的查询在分析分类结构是否合理时特别有用比如突然发现某一层挂了上百个节点说明分类设计可能有问题。4.2 统计每个分支的叶子/总数如果想查“某个分支下一共有多少个叶子节点”也就是没有子节点的节点可以先递归出整棵子树再用NOT EXISTS过滤出“在原表中不存在任何 parent_id 自身 id 的节点”WITH RECURSIVE cte AS ( SELECT id, parent_id, name, 1 AS lvl FROM category WHERE id 1 UNION ALL SELECT c.id, c.parent_id, c.name, cte.lvl 1 FROM category c INNER JOIN cte ON c.parent_id cte.id ) SELECT COUNT(*) AS leaf_count FROM cte WHERE NOT EXISTS ( SELECT 1 FROM category sub WHERE sub.parent_id cte.id );在这个示例里叶子节点是 5、6、7、8、10 这 5 个。这个统计对权限模块特别常见比如“某个角色组下到底绑定了多少个最终权限点”。4.3 常见错误“方向反了”自顶向下查询最常出的错误就是把递归成员里面的 JOIN 条件写成c.id cte.parent_id。一旦写反你查id 1的子孙结果会一路向上找id 1的父节点然后返回来一堆无关数据甚至因为parent_id 0导致结果集直接为空。记住一句口诀向上查父JOIN 的连接键是c.id cte.parent_id向下查子JOIN 的连接键是c.parent_id cte.id。判断方法永远只有一个——拿一行数据手推一遍。5. 性能、深度限制和“老方案”对比光会写 SQL 不算真会用CTE 递归查询在生产环境会遇到三个绕不开的问题深度限制、死循环、性能。5.1 cte_max_recursion_depth 与死循环防护MySQL 对递归深度有一个默认上限cte_max_recursion_depth默认值是 1000。也就是说递归迭代超过 1000 轮MySQL 直接报错ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing cte_max_recursion_depth to a larger value.这个限制本质是保护机制防止递归失控把数据库拖垮。但如果你确实有超深层级比如某个分类套了 1500 层可以临时调大SET SESSION cte_max_recursion_depth 10000;更稳妥的做法是在配置文件的[mysqld]段永久调整cte_max_recursion_depth 10000这里要强调一句调参不是解决问题的根本办法遇到超限先怀疑数据是不是有环。树形结构的数据最怕脏数据比如两条记录互相把对方设为父节点形成 A→B→A 的环。一旦有环递归就会无限迭代直到撞上深度上限。如果没有上限保护服务直接卡死。怎么查有没有环可以用一条普通 SQL 自查SELECT a.id, b.id FROM category a INNER JOIN category b ON a.parent_id b.id AND b.parent_id a.id;这种互指数据在业务上通常是垃圾数据建议在应用层写入时增加校验或者在数据定期清洗任务里跑一遍上面的 SQL 找出来。5.2 索引建议parent_id 上有没有索引差别巨大递归 CTE 性能好不好很大程度上取决于parent_id上有没有索引。递归的本质就是反复通过parent_id查原表每一轮迭代都是一次INNER JOIN。如果parent_id没有索引每次迭代都是全表扫描数据量一大一次查询可能要扫好几遍全表时间直接指数级上涨。所以在建表时就应该加索引ALTER TABLE category ADD KEY idx_parent_id (parent_id);主键id有主键索引不用管。有了这个索引递归查询每一轮相当于走一次ref连接速度快得多。另外一个排查性能问题的技巧是使用EXPLAIN ANALYZEMySQL 8.0.18 支持它可以真实执行语句并输出每一轮迭代的开销信息EXPLAIN ANALYZE WITH RECURSIVE cte AS ( SELECT id, parent_id, name, 1 AS lvl FROM category WHERE id 1 UNION ALL SELECT c.id, c.parent_id, c.name, cte.lvl 1 FROM category c INNER JOIN cte ON c.parent_id cte.id ) SELECT * FROM cte;输出里会有类似actual time0.05..0.2 rows10 loops7的信息loops就是迭代轮数。如果发现loops特别大但查询结果行数又不多就要警惕是不是路径上存在重复引用是不是parent_id索引没走5.3 对比表存储过程、多次JOIN、程序递归、闭包表整理一张表方便你评估什么时候该用 CTE方案层级不固定查询代码量维护成本可嵌入普通SQL适用场景递归 CTE支持少低可以中小数据量、业务变动频繁的树查询固定层级 JOIN不支持中中可以层级确定的极简场景存储过程 临时表支持多高不行老系统历史包袱应用层递归支持中中不行全表数据量小、需要完整树闭包表支持少高写入复杂可以读多写极少、层级很深的场景从这张表能看出CTE 不是万能的但对绝大多数业务来说它是在代码量和灵活性之间最平衡的方案。如果你遇到的是“写极其频繁但查询很少”的团队闭包表会更合适如果数据量超过百万节点递归 CTE 每轮迭代都要走索引访问性能可能会吃紧这时建议评估一下闭包表或者路径枚举方案。6. 实战笔记结合业务数据处理的一些体会这一节写一些实际项目中的心得体会属于那种不亲自跑一遍很难从文档里学到的经验。6.1 从CTE结果到前端树组件的完整链路很多人以为拿到递归结果就算完事结果前端拿着一个扁平列表不会渲染。实际做法是在后端用 CTE 查出带lvl和parent_id的扁平列表然后应用程序里用一次循环组装成树结构再返回给前端。组装树的逻辑很简单建立一个map[id - node]。遍历列表把每个节点挂到map[node.parent_id].children下面。找不到父节点的说明是顶级节点作为根列表返回。这样做的好处是数据库只查一次应用层最多做一次O(n)的循环前端拿到直接递归渲染整个链路性能稳定。这里有个小技巧CTE 负责查出“哪些节点属于这棵树”应用层负责“谁是谁的爹”。两者职责分离比在 SQL 里硬拼路径字符串更清晰。6.2 循环引用脏数据导致递归爆炸的排查曾经在线上遇到过一个诡异问题一个看起来没多少数据的分组表递归查询竟然执行了几十秒。后来一查发现有一条数据的parent_id指回了自己等于说这个节点是自己的爹。递归没有任何出口条件一路疯狂迭代直到撞上cte_max_recursion_depth上限报错。排查方法其实就一句话凡是递归查询的表必须有数据完整性保护。最稳妥的办法是在应用层禁止parent_id等于自身 ID禁止成环引用。如果历史原因已经产生了脏数据可以在查询前先跑诊断 SQL-- 查找自引用 SELECT * FROM category WHERE id parent_id; -- 查找互指两层环 SELECT a.id, a.name, a.parent_id, b.id AS parent_of_a FROM category a INNER JOIN category b ON a.parent_id b.id AND b.parent_id a.id;还有更复杂的多节点环比如 A→B→C→A这种就需要用递归 CTE 再去查一遍“路径中重复出现的节点”。不过到这一步基本属于极少数情况最实际的预防措施还是在写入入口做校验。6.3 面试与面试题视角这道题到底在考什么“MySQL 怎么查询树形结构的所有上级/所有下级”算是一道高频面试题尤其是问到 MySQL 8.0 新特性的时候。面试官真正想考察的点其实有三个第一你知不知道 MySQL 8.0 引入了递归 CTE。在 8.0 之前只能用存储过程或应用层递归而 8.0 开始有标准写法。第二你了不了解 CTE 的执行机制。能说清楚锚点成员和递归成员的区别能说清楚“每一轮迭代拿到的只是上一轮新生成的集合而不是全部结果集”。第三你会不会处理递归的终止条件和死循环风险。比如为什么INNER JOIN能天然终止递归为什么parent_id 0不会导致无限循环以及深度上限参数怎么设置。如果一个候选人能答到第三层基本可以确定他真的在项目里用过递归 CTE而不是背了几道面试题。反过来如果你正在准备面试这篇文章里的第三、四节内容已经足够应对所有常规追问剩下的就是亲手在本地 MySQL 上跑一遍把执行结果看熟。最后分享一个我个人的使用习惯遇到层级查询需求我不会上来就写递归 CTE而是先问三个问题——这棵树会不会乱改数据量大概多少查询链路里谁会用到结果如果数据量小、结构稳定有时候一次全量查询配应用层组装就足够了但如果是“任意节点找祖先链”这种按点查询递归 CTE 一定是第一选择。灵活性、可读性、扩展性都更好而且它用的是标准 SQL 语法未来换到 PostgreSQL、SQL Server这套写法依然通用。
