MySQL 递归 CTE怎样展开层级目录并避免重复节点怎样完成一个可复用的最小示例

MySQL 递归 CTE 可以从一个根节点沿 parent_id 展开子目录。防重复要先确定目标：带路径展示时，在每条路径记录已访问节点以阻止环；只要唯一可达节点时，让递归结果仅包含 id 并使用 UNION DISTINCT。把 depth、path 一起去重，不能保证同一个节点只保留一次。

下面以 MySQL 8.4 语义和单父节点目录表为例，给出独立练习库中的最小写法。例子没有连接真实数据库，也没有执行查询；文中的预期结构由样例数据推导。

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

明确目录是一棵树还是一张图

一行一个唯一 id、每行至多一个 parent_id，是单父节点模型。正确的树从单根展开时本身不会通过两个父节点走到同一节点。多父关系需要独立边表；如果同一节点可经多条路径到达，路径集合和节点集合就是不同输出。

业务接口要明确是返回目录树、所有路径还是去重节点列表。多根查询中一个根可能是另一根的后代，重复出现也可能来自入口重叠，而非递归连接写错。先固定入口与输出契约，再确定去重方式。

准备节点与父节点索引

练习表使用主键维护节点身份，parent_id 索引服务于寻找子节点。这里只讨论查询；生产账户不应为读取目录获得建表或删表权限。

-- 在独立练习库中创建示例表；不要覆盖业务表。
CREATE TABLE cte_directory_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  parent_id BIGINT UNSIGNED NULL,
  name VARCHAR(60) NOT NULL,
  KEY idx_parent (parent_id)
);
-- 每行只有一个父节点，id唯一；本例故意不定义级联行为。
INSERT INTO cte_directory_demo (id,parent_id,name) VALUES
(31,NULL,'文档'),(42,31,'研发'),(58,31,'运营'),(76,42,'接口');

样例包含根31、两个直接子节点42和58，以及42的子节点76。parent_id 允许 NULL 表示根。外键只能约束引用是否存在，不会自动阻止父子循环，目录修改仍需业务约束。

用路径防止同一分支重复访问

-- 根节点为31，根深度为0，最多展开到深度20。
WITH RECURSIVE branch (id,parent_id,name,depth,visited) AS (
  SELECT id,parent_id,name,0,
         CAST(CONCAT(',',id,',') AS CHAR(1024))
  FROM cte_directory_demo
  WHERE id=31
  UNION ALL
  SELECT c.id,c.parent_id,c.name,b.depth+1,
         CONCAT(b.visited,c.id,',')
  FROM cte_directory_demo AS c
  JOIN branch AS b ON c.parent_id=b.id
  -- 分隔符避免把编号3误判为编号31的一部分。
  WHERE b.depth

branch 包含节点字段、depth 和 visited。根行先把自己的 id 写入带逗号分隔的字符串；递归行仅接受当前路径里没有出现过的子节点。分隔符很关键：查询 ,3, 不会把 ,31, 当成相同节点。

深度20是样例业务上限，不是官方推荐值。BIGINT UNSIGNED 的十进制最多20位，加上分隔符，21个节点的路径能容纳在示例1024字符宽度内。锚点显式 CAST 是因为CTE字段类型根据非递归部分推断；递归字符串增长不会自动扩宽锚点定义。

这个条件防止同一路径回到已访问节点，但不会在一张多父图里全局去重。如果一个节点经两条不同合法路径到达，仍可有两行。外层 DISTINCT 即使消除部分重复，也不能代替递归过程中的环路终止条件。

只要唯一节点时缩小去重列

若目标只是批处理一遍每个节点，可用只含 id 的可达集合。其他展示字段在递归完成后连接原表，避免把每次变化的深度或路径纳入行去重。

-- 只返回可达节点集合：递归内部仅保留稳定的节点id。
WITH RECURSIVE reachable (id) AS (
  SELECT id FROM cte_directory_demo WHERE id=31
  UNION DISTINCT
  SELECT c.id
  FROM cte_directory_demo AS c
  JOIN reachable AS r ON c.parent_id=r.id
)
SELECT d.id,d.parent_id,d.name
FROM reachable AS r
JOIN cte_directory_demo AS d ON d.id=r.id
ORDER BY d.id;

UNION DISTINCT 比较整行。这里整行只有 id，所以相同节点会合并；若增加 depth，自环会生成相同 id 与不同 depth 的行，去重不再阻止这种增长。不要为了展示深度随手扩展这个递归集合。

给批处理加深度和失败边界

CTE 的业务深度限制与服务器递归上限承担不同职责。达到业务上限会截断展开，不代表完整目录只有这些节点；需要完整性证明时，应额外明确被截断的分支。服务器递归深度或执行超时触发错误时，批处理不能把失败当作“无子节点”。

为批处理提供根id、输出模式、深度上限和任务标识。应用层使用参数绑定根节点，记录查询失败及取消原因；不要拼接用户输入作为 SQL 标识或条件。单条CTE只在该语句中存在，不会创建长期数据库表。

样例查询用 ORDER BY 明确输出顺序；递归计算内部的顺序不能当展示顺序。parent_id 索引也不保证所有规模都高效，多父图的路径数可能远大于节点数，限制深度仍不能代替业务规模预算。

返回空集是递归没生效吗？

先确认根id存在且入口过滤符合预期。根行没有数据时，递归没有初始集合。

UNION ALL是不是一定产生重复？

不是。它保留产生的所有行；正确单根树本身可能没有重复。问题在于数据关系和输出语义。

怎样清理练习数据？

只在确定是自己的独立练习表时删除该表；生产目录不需要为CTE建立临时副本。本文不执行任何建表或清理操作。

语法、字段类型推断与递归限制参考：https://dev.mysql.com/doc/refman/8.4/en/with.html。