MySQL 递归 CTE怎样展开层级目录并避免重复节点怎样在批处理代码中减少额外分配

如果目录表采用 node_id 和 parent_id 保存上下级关系，MySQL 递归 CTE 可以把一棵目录树展开成带层级的结果。真正容易出错的地方不在 WITH RECURSIVE 这几个关键字，而在于没有区分“同一路径回环”和“不同路径重复到达同一个节点”。

树形数据通常用 UNION ALL 保留每一行，图形数据要单独设计去重语义；路径列负责防止一条路径回到自己，不能把它误当成全局节点去重。批处理时只带必要字段，并用 parent_id 索引、深度上限和批量根节点控制中间结果。

MySQL 官方地址：https://www.mysql.com/

下面使用 MySQL 8.4 参考手册中的递归 CTE 规则，示例只描述查询结构，不依赖本地运行截图。

先确定邻接表和递归边界

目录节点可以用一张邻接表表示：根节点的 parent_id 为 NULL，普通节点指向它的直接父节点。批量展开时不要默认扫描整张表，先按一个或一组根节点限定输入。

-- 目录表只保留层级展开所需的稳定字段
CREATE TABLE catalog_node (
  node_id BIGINT PRIMARY KEY,
  parent_id BIGINT NULL,
  name VARCHAR(200) NOT NULL,
  KEY idx_catalog_parent (parent_id)
);

-- 先取一个根节点，避免每次批处理都从整棵目录开始
SELECT node_id, parent_id, name
FROM catalog_node
WHERE node_id = 100;

parent_id 上的索引是递归成员反复查找子节点时的基础。它不能修复循环数据，也不能替代根节点过滤，但可以减少每一层关联时的无关扫描。

递归 CTE 的最小展开模型

递归 CTE 由非递归成员和递归成员组成。前者放入根节点，后者用上一轮产生的节点去连接下一层。示例保留 depth 和 path_ids，既便于展示层级，也为防环提供依据。

-- path_ids 用逗号保存当前路径，depth 仅表示层级深度
WITH RECURSIVE catalog_tree AS (
  -- 非递归成员：从指定根节点开始
  SELECT
    node_id,
    parent_id,
    name,
    0 AS depth,
    CAST(node_id AS CHAR(2000)) AS path_ids
  FROM catalog_node
  WHERE node_id = 100

  UNION ALL

  -- 递归成员：只接上当前节点的直接子节点
  SELECT
    child.node_id,
    child.parent_id,
    child.name,
    tree.depth + 1,
    CONCAT(tree.path_ids, ',', child.node_id)
  FROM catalog_tree AS tree
  JOIN catalog_node AS child
    ON child.parent_id = tree.node_id
  -- 同一路径再次遇到节点时停止，避免环路继续扩张
  WHERE tree.depth 

这里的 CAST 放在非递归成员中，是因为 MySQL 会根据非递归成员推断 CTE 列类型。路径随着递归变长时，预留足够宽度可以避免截断；但宽度也不应无限放大，否则每一轮都携带更宽的中间行。

为什么“重复节点”和“循环节点”不是一回事

如果每个节点只有一个父节点，数据结构本身接近树，UNION ALL 通常能保留完整层级。此时 FIND_IN_SET 主要是防御脏数据中的环路，例如 A 指向 B、B 又指回 A。

如果一个节点可以从多个父节点到达，结果中出现同一个 node_id 可能是业务允许的多条路径，也可能只是希望最终列表不重复。把 path_ids、depth 放进 CTE 后，两个路径的整行并不相同，因此单纯改成 UNION DISTINCT 不一定能按节点去重。

需要“每个节点只保留一条代表路径”时，可以让递归部分防止单路径回环，再在外层按节点选择一条记录。窗口函数放在递归 CTE 外层，避免把 MySQL 禁止放入递归成员的窗口函数塞进递归定义里。

-- 先展开候选路径，再按节点选择最浅的一条代表记录
WITH RECURSIVE candidate_paths AS (
  -- 根节点是一条候选路径
  SELECT node_id, parent_id, name, 0 AS depth,
         CAST(node_id AS CHAR(2000)) AS path_ids
  FROM catalog_node
  WHERE node_id = 100

  UNION ALL

  -- 只沿当前路径未出现过的子节点继续展开
  SELECT child.node_id, child.parent_id, child.name,
         path.depth + 1,
         CONCAT(path.path_ids, ',', child.node_id)
  FROM candidate_paths AS path
  JOIN catalog_node AS child ON child.parent_id = path.node_id
  WHERE path.depth 

批处理怎样减少中间结果和额外分配

这里的“额外分配”不只指客户端语言的对象分配，也包括数据库为了递归 CTE 保存中间结果而产生的临时表和更宽的行。MySQL 文档提醒，CTE 结果较大时，内部临时表可能从内存转到磁盘，因此应该先收紧数据边界。

-- 只调整当前会话的递归保护，不影响后续新会话
SET SESSION cte_max_recursion_depth = 100;

-- 给单次 SELECT 增加时间边界，避免异常图数据占满会话
WITH RECURSIVE catalog_tree AS (
  SELECT node_id, parent_id, name, 0 AS depth
  FROM catalog_node
  WHERE node_id IN (100, 200, 300)
  UNION ALL
  SELECT child.node_id, child.parent_id, child.name, tree.depth + 1
  FROM catalog_tree AS tree
  JOIN catalog_node AS child ON child.parent_id = tree.node_id
  WHERE tree.depth 

如果业务需要完整路径，建议把路径列放在最终展示查询中；如果批处理只需要节点 ID 和父子关系，就不要在每一轮携带名称、说明或大 JSON 字段。客户端也应按批次读取必要列，避免为了打印目录而把整棵树复制成多份对象。

常见误区与适用边界

• 把 UNION DISTINCT 当成万能去重。它比较的是整行，路径和深度不同就可能仍然保留多行；先明确是按路径、按节点还是按层级去重。
• 只设置递归深度，不检查路径。深度上限只能截断结果，不能说明数据没有环路；需要审计时应把触发上限的根节点记录下来。
• 在递归成员里放聚合、窗口或排序。MySQL 对递归 SELECT 有限制，聚合和排序应移到递归 CTE 外层。
• 忽略列类型由初始成员决定。字符串路径要在非递归成员中显式预留宽度，避免后续拼接截断或报错。

结语

一条可维护的 MySQL 递归 CTE 查询，应同时回答三个问题：从哪些根节点开始、怎样阻止一条路径回环、最终需要按什么身份去重。树形目录可以用 UNION ALL 加路径防环；多父节点图则要在外层用明确的节点排名或其他业务规则选择代表记录。批处理阶段再通过索引、列裁剪、深度和时间边界控制中间结果，才能减少不必要的临时存储和客户端额外分配。

相关问题：如果目录允许多父节点，应该保留全部路径还是只保留最短路径？如果递归结果突然变慢，是根节点范围变大、parent_id 索引缺失，还是路径列导致临时表变宽？把这两个问题记录到批处理日志，通常比只记录“查询超时”更容易定位。