递归 CTE 生成组织树：终止条件、层级与循环防护

我第一次把部门查询从应用层循环改成 MySQL 递归 CTE 时，真正麻烦的不是写出 WITH RECURSIVE，而是把“从哪开始、何时停止、数据成环怎么办”一起定义清楚。可落地的做法是：用根节点作为锚点，以 parent_id 连接下一层，同时携带 level、ID 路径和名称路径；再用业务层级上限与路径查重做双重防护。

MySQL 官方文档：https://dev.mysql.com/doc/refman/8.4/en/with.html

• 接口参数只保留根节点 root_id 与最大层级 max_depth。
• 锚点决定 CTE 列类型，增长路径必须在锚点中显式扩宽。
• 叶节点自然停止；深度上限控制异常链；路径检查阻断重复 ID。

先把组织树查询当成一个接口

表结构采用邻接表：每个节点只保存自己的主键和父节点。调用方需求不是“查整张表”，而是“给定一个节点，返回它和全部后代，并说明每行处于哪一层”。因此我会把 root_id 定义为必填参数，把 max_depth 定义为服务端上限，而不是允许前端无限放大。

-- parent_id 支撑递归成员按父节点寻找直接子节点
CREATE TABLE org_unit (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT NULL,
  name VARCHAR(100) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  INDEX idx_org_parent (parent_id),
  CONSTRAINT fk_org_parent
    FOREIGN KEY (parent_id) REFERENCES org_unit(id)
);

外键只能保证父节点存在，不能阻止 A 指向 B、B 又指向 A 这类逻辑环。因此写入端仍应检查祖先链；读取端则保留防御性保护，不能把数据永远正确当作查询前提。

锚点、递归成员与层级字段

MySQL 的递归 CTE 由非递归锚点和递归成员组成。锚点返回指定根节点，递归成员只处理上一轮产生的节点并寻找其直接子节点。当某一轮没有新行时，递归自然结束。下面的查询还把 ID 路径写成带斜杠的字符串，避免 ID 1 与 11 的子串误判。

-- :root_id 由调用方绑定；:max_depth 由服务端限制在合理范围
WITH RECURSIVE org_tree AS (
  SELECT
    u.id,
    u.parent_id,
    u.name,
    u.sort_order,
    0 AS level,
    CAST(CONCAT('/', u.id, '/') AS CHAR(2000)) AS path_ids,
    CAST(u.name AS CHAR(2000)) AS path_names
  FROM org_unit AS u
  WHERE u.id = :root_id

  UNION ALL

  SELECT
    child.id,
    child.parent_id,
    child.name,
    child.sort_order,
    parent.level + 1,
    CONCAT(parent.path_ids, child.id, '/'),
    CONCAT(parent.path_names, ' / ', child.name)
  FROM org_tree AS parent
  JOIN org_unit AS child
    ON child.parent_id = parent.id
  WHERE parent.level 

level=0 表示根节点，下一层依次加一。path_names 适合展示和稳定排序，path_ids 专门承担防环判断，不要把可变名称拿来识别节点。

终止条件与循环防护是两层契约

官方说明指出，递归成员不再产生新行时查询结束；MySQL 还用 cte_max_recursion_depth 限制递归层数，默认值为 1000。但服务器上限是最后保险，不等于业务终止条件。组织结构通常远浅于 1000 层，所以查询中的 max_depth 应来自明确的业务规则。

我会把防护拆成三种含义：没有子节点是正常结束；达到 max_depth 是受控截断；路径命中重复 ID 是数据异常被隔离。接口若只返回结果集，调用方无法区分后两者。需要严格诊断时，可以同时返回服务端设定的层级上限，并异步运行一条专门的组织关系巡检，而不是在递归成员里堆聚合或窗口函数；MySQL 对递归成员中的 GROUP BY、窗口函数和 ORDER BY 等结构有限制。

错误处理、类型与兼容边界

最容易被忽略的是路径列类型。MySQL 只根据非递归锚点推断递归 CTE 的列类型，所以锚点里的 CAST(... AS CHAR(2000)) 不是装饰：如果递归成员不断拼接更长字符串，未扩宽的列可能被截断，严格模式下还会报“Data too long”。长度 2000 只是示例，应结合最大层级、名称长度和字符集确定。

接口错误语义也要稳定：根节点不存在时返回空集，不应伪装成数据库故障；max_depth 小于 0 或超过服务端上限时，在执行 SQL 前拒绝；查询超时或达到服务器递归限制时记录为运行错误；发现环时，当前查询会排除重复节点，但应把数据修复交给写入校验或巡检任务。需要兼容旧版 MySQL 时，先确认目标版本支持递归 CTE；否则只能改用闭包表、物化路径或应用层遍历，而不是删掉防护条件硬跑。

常见问题

为什么不用 UNION DISTINCT 自动去重？ 因为组织树结果还携带 level 和路径，同一 ID 在不同路径下整行并不一定重复。显式检查 ID 路径能更准确表达“祖先链不能再次出现当前节点”。

为什么不在递归成员里排序？ 递归成员负责生成关系，展示顺序放到最外层 SELECT 更清晰，也符合 MySQL 对递归部分的语法边界。若同级必须稳定排序，可把 sort_order 编入专用排序路径，而不是依赖未承诺的迭代顺序。