MySQL 递归 CTE 怎样为每层携带路径文本

在组织架构、目录树或商品分类里，递归 CTE 不只要返回当前节点，通常还要带出“根节点 / 父节点 / 当前节点”这样的路径。这个需求的关键不是把 CONCAT() 写进递归成员，而是先在锚点查询中把 path 的字符类型定宽。MySQL 会依据非递归部分确定递归 CTE 的列类型，锚点太窄，后面拼接再长也不会自动扩容。

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

一、先看递归 CTE 的列类型边界

假设有一张 nodes 表，字段是 id、parent_id 和 name。最容易踩坑的写法是让锚点直接返回一个很短的字符串：

-- 锚点只返回根节点；path 的类型也会从这里开始确定
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, name AS path
    FROM nodes
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归成员把当前节点追加到已有路径后面
    SELECT n.id, n.parent_id, n.name,
           CONCAT(t.path, ' / ', n.name) AS path
    FROM nodes AS n
    JOIN tree AS t ON n.parent_id = t.id
)
SELECT id, name, path FROM tree;

如果根节点名称推导出的字符类型不够容纳后续层级，结果可能出现路径被截断，或者直接报 Data too long for column。排查时先对照两段 SELECT 的列顺序：第四列必须都是路径，且锚点列和递归列要能在同一字符类型边界内表达。

二、在锚点侧显式预留 path 宽度

解决方式是把锚点的路径显式转换为足够宽的字符类型。下面用 CHAR(512) 作为示例，实际宽度应按业务数据估算：

-- 先把 path 定成可容纳多层名称的字符列，避免递归列过窄
WITH RECURSIVE tree (id, parent_id, name, path, depth) AS (
    SELECT id,
           parent_id,
           name,
           CAST(name AS CHAR(512)) AS path,
           0 AS depth
    FROM nodes
    WHERE parent_id IS NULL

    UNION ALL

    -- 只追加当前节点；depth 单独表示层级，不混入 path 文本
    SELECT n.id,
           n.parent_id,
           n.name,
           CONCAT(t.path, ' / ', n.name) AS path,
           t.depth + 1 AS depth
    FROM nodes AS n
    JOIN tree AS t ON n.parent_id = t.id
)
SELECT id, name, path, depth
FROM tree;

这里的 CAST 是类型边界，不是对每一层重复截断。递归成员的 CONCAT 只表达路径如何增长；把 depth 单列出来，则可以单独控制展示层级、排序和排查，不必从路径文本反推层数。

三、把现象拆成三层检查

遇到“路径为空、少了后缀或递归直接报错”时，可以按下面的证据顺序判断。

• 先查列对齐：锚点和递归成员的列数、顺序、别名是否对应；尤其确认 path 没有误放到 name 位置。
• 再查类型宽度：查看锚点表达式是否有 CAST(... AS CHAR(N))，并估算 N 能否覆盖分隔符与所有层级名称。
• 最后查数据边界：确认父子关系没有形成环，且停止条件与业务树深度相符；无限递归不是加大 CHAR(N) 就能解决的。

如果只有深层数据失败，优先怀疑长度预算；如果根节点就失败，先检查列顺序和字符表达式；如果层数持续增加，则要回到父子数据质量与终止条件。

四、按长度和结果反向验证

宽度估算可以用“最长节点名称 × 最大层级 + 分隔符长度 × 边数”作为起点，再给实际数据留余量。中文字符、连接符和字符集都应按真实表结构考虑，不要只拿一条短路径试算。

-- 结果核对关注长度和层级，不把它当成性能测试
WITH RECURSIVE tree (id, parent_id, name, path, depth) AS (
    SELECT id, parent_id, name,
           CAST(name AS CHAR(512)) AS path,
           0 AS depth
    FROM nodes
    WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, n.name,
           CONCAT(t.path, ' / ', n.name),
           t.depth + 1
    FROM nodes AS n
    JOIN tree AS t ON n.parent_id = t.id
)
SELECT id, depth, path, CHAR_LENGTH(path) AS path_length
FROM tree
ORDER BY path, depth;

CHAR_LENGTH(path) 只帮助核对实际字符长度，不能替代递归停止条件。结果中应能看到每个子节点的路径包含父级前缀，depth 随层级增加；若同一节点反复出现，先修复循环数据或补充明确的访问记录设计。

相关问题

为什么只在深层节点出现 Data too long？

因为锚点确定的 path 类型能容纳根节点，却容纳不了多次拼接后的字符串；把宽度设置放在锚点查询，而不是只改递归成员。

把 path 改成很大的 CHAR 就能解决所有问题吗？

不能。它只能解决字符列宽度不足，不能解决父子环、错误列对齐、错误停止条件或不合理的层级数据。

总结清单

1. 确认递归 CTE 的锚点列与递归列逐列对齐。
2. 在锚点侧用 CAST 预留 path 宽度。
3. 用 CONCAT 追加节点，用 depth 表示层级。
4. 用真实名称长度、分隔符和最大层级复核预算。
5. 将长度问题与循环、停止条件、排序问题分开处理。