MySQL 递归 CTE怎样展开层级目录并避免重复节点为什么结果为空或不符合预期

我第一次把目录表改成递归 CTE 时，最先遇到的不是语法错误，而是结果看起来“少了一层”：根节点能查到，子节点却为空；把 UNION ALL 换成去重写法后，重复行少了，环路数据却仍然让查询变慢。后来把问题拆成“锚点从哪里开始、递归成员怎样连接、重复按什么维度判断”三件事，排查就清楚多了。

这篇只围绕 MySQL 递归 CTE 展开层级目录。先给结论：空结果通常是锚点或父子连接条件不成立；重复节点通常是多条路径汇聚到同一节点；环路则必须用路径成员或业务约束明确阻断，不能只依赖整行去重。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/with.html

MySQL 官方文档把递归 CTE 分成非递归部分和递归部分，并说明递归 CTE 适合遍历层级数据。下面的写法按照这个边界组织，读者可以把表名和字段替换成自己的目录表。

先把变化和问题边界说清楚

递归 CTE 的关键变化不是“多写一个 WITH”，而是把一次查询拆成两个固定角色：锚点查询负责产生第一批行，递归成员负责根据上一轮行继续找到下一层。MySQL 要求递归 CTE 使用 WITH RECURSIVE，两部分通常由 UNION ALL 或 UNION DISTINCT 连接。

对目录表来说，最稳的起点是先确认数据模型：id 是当前节点，parent_id 指向父节点，根节点的 parent_id 可以是 NULL 或约定的虚拟根。只要锚点选错，后面的递归逻辑再完整也会得到空结果。

目录展开的最小骨架：锚点加递归成员

下面用一张简单的 category 表表示目录。示例中的中文 SQL 注释只解释关键边界，不依赖本地执行结果。

-- 目录节点：id 是本节点，parent_id 指向上一层节点
CREATE TABLE category (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT NULL,
  name VARCHAR(100) NOT NULL,
  KEY idx_category_parent_id (parent_id)
);

-- 锚点返回根目录，递归成员按 parent_id 连接下一层
WITH RECURSIVE category_tree (id, parent_id, name, depth, path) AS (
  SELECT
    id,
    parent_id,
    name,
    0 AS depth,
    CAST(id AS CHAR(200)) AS path
  FROM category
  WHERE id = 1 -- 这里的 1 是本次展开的根节点

  UNION ALL

  SELECT
    child.id,
    child.parent_id,
    child.name,
    tree.depth + 1,
    CONCAT(tree.path, ',', child.id)
  FROM category_tree AS tree
  JOIN category AS child
    ON child.parent_id = tree.id -- 子节点的父 id 对上当前节点
  WHERE tree.depth 

这里最容易忽略的是列类型。MySQL 会根据锚点部分推断递归 CTE 的列类型；如果锚点中的 path 太窄，递归拼接更长路径时可能截断或报错，所以示例用 CAST 预留宽度。递归成员的列顺序也必须与锚点一致。

如果结果为空，我通常先只运行锚点查询，再单独检查 SELECT * FROM category WHERE id = 1。锚点没有行，递归部分不会凭空创建目录；锚点有行但没有子节点，则重点看 child.parent_id = tree.id 是否与真实数据方向相反。

为什么要同时保留 depth 和 path

depth 是阅读和限深用的；path 是排查重复路径与环路用的。只返回节点名称，读者很难判断一个节点是同一条路径下的重复，还是从不同父节点被正确找到。

目录有多父关系或脏数据时，同一个 id 可能通过两条路径进入结果。此时“结果中出现两行”不一定等于查询错误：如果你要展示每条目录路径，应该保留路径；如果业务要求每个节点只出现一次，就要明确选择节点去重策略。

-- 只保留路径没有再次包含当前子节点的分支
WITH RECURSIVE category_tree (id, parent_id, name, depth, path) AS (
  SELECT
    id,
    parent_id,
    name,
    0,
    CAST(id AS CHAR(200))
  FROM category
  WHERE id = 1

  UNION ALL

  SELECT
    child.id,
    child.parent_id,
    child.name,
    tree.depth + 1,
    CONCAT(tree.path, ',', child.id)
  FROM category_tree AS tree
  JOIN category AS child
    ON child.parent_id = tree.id
  WHERE tree.depth 

这个条件表达的是“同一条路径内不重复访问”。它不等于全局去重：如果一个节点从两个不同父节点到达，仍然可能出现两行，这正是路径语义和节点语义的区别。

UNION DISTINCT 和路径防环不是同一件事

这是我在实际排查中最容易混淆的一点。

• UNION DISTINCT 关注结果行是否完全相同。它可以帮助处理传递闭包中的重复行，但只要 depth 或 path 不同，同一个节点仍可能是不同的行。
• 路径防环关注当前路径是否再次遇到相同节点。它针对的是 A→B→A 这样的环路，目标是阻止递归继续沿同一条路径走回已访问节点。
• 如果业务要求全局只出一个节点，需要额外定义“保留哪条路径、哪一层或哪一个父节点”，不能把数据库自然返回的第一行当成业务规则。

因此，目录展示通常保留 UNION ALL 加路径防环，让路径信息完整；关系闭包更关心可达节点集合时，才考虑 UNION DISTINCT，并把用于判断重复的列设计成真正代表集合成员的列。

结果为空或不符合预期时，我会按这个顺序排查

1. 先查锚点：根节点条件是否真的命中，根节点是 id = 1 还是 parent_id IS NULL，不要把两种模型混用。
2. 再查连接方向：向下展开是 child.parent_id = tree.id；向上查祖先则应从当前节点连接到 parent.id = child.parent_id，方向写反就会空。
3. 看列宽：路径、名称或拼接列在锚点中要预留足够类型；递归部分不会替锚点自动扩大列宽。
4. 看递归上限：深度条件过小会让结果看起来缺层；完全不设边界又会放大脏数据环路的影响。
5. 最后看排序：递归结果没有天然的目录展示顺序，按 path 排序比直接依赖返回顺序更可读。

这套顺序的好处是先排除“根本没有输入行”的问题，再处理连接方向、字段类型和数据质量。不要一看到重复就先换成 UNION DISTINCT，否则可能把需要保留的两条合法路径误删。

我的结论：先确定语义，再选去重方式

如果目标是展开一棵目录树，建议从 WITH RECURSIVE、明确锚点、depth、path 和深度上限开始；如果数据可能出现环路，再加路径防环条件。如果目标是求一个可达节点集合，再讨论 UNION DISTINCT 或按节点 id 的业务去重。

对我来说，最重要的经验是：空结果先看锚点和连接方向，重复结果先问“重复的是整行、节点还是路径”，环路再看路径防护。三个问题分开后，递归 CTE 就不再是只能靠改关键字碰运气的查询。

相关问题

为什么忘记 WITH RECURSIVE 会提示表不存在？

因为递归 CTE 自引用时需要显式声明 RECURSIVE。没有这个关键字，递归名称可能会被当成普通表名解析。

为什么递归 CTE 展开到一半就没有结果了？

常见原因是某一层没有匹配到子节点、深度条件已达到上限，或者路径条件把该分支判定为已访问。分别查看锚点、单层连接和过滤条件更容易定位。

UNION DISTINCT 能不能完全防止递归死循环？

不能把它当成通用防环方案。它只消除满足去重规则的重复结果；是否能阻断环路取决于参与去重的列和递归语义。路径防环或数据层面的父子约束更直接。

参考：https://dev.mysql.com/doc/refman/8.4/en/with.html