MySQL递归CTE中十六进制拼接被截断至64字符问题

阿华AIGC实验室

2026-6-13

问题原因排查与解决方案

核心触发原因：递归CTE锚点成员的列长度限制

MySQL的递归CTE有个关键特性：锚点查询（初始查询）会定义递归列的元数据（包括数据类型、长度），后续递归阶段的结果会被强制转换为锚点列的长度。如果你的锚点查询里SortPath是通过UNHEX(HEX(root_node_id))生成的，而节点ID是8字节的BIGINT，那这个表达式返回的是VARBINARY(8)——递归时哪怕你拼接更长的字节串，也会被截断到锚点定义的长度。

你提到hlevel5时SortPath只显示64字符（对应32字节、4个BIGINT ID），说明你的锚点SortPath初始长度被隐式设为32字节，递归到第5层时，拼接后的40字节（5个ID）被截断为32字节，直接丢失了第5个ID。

具体排查与修复步骤

1. 检查递归拼接逻辑是否正确

确保递归阶段的SortPath是父节点路径 + 当前节点ID的拼接，而非仅取当前节点ID。正确的拼接写法应该是：

WITH RECURSIVE cte AS (
    -- 锚点：根节点
    SELECT 
        id,
        parent_id,
        1 AS hlevel,
        UNHEX(HEX(id)) AS SortPath
    FROM dct_node_adjc
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归：子节点拼接父路径
    SELECT 
        child.id,
        child.parent_id,
        parent.hlevel + 1 AS hlevel,
        -- 必须用CONCAT拼接父节点SortPath的HEX和当前节点ID的HEX，再转二进制
        UNHEX(CONCAT(HEX(parent.SortPath), HEX(child.id))) AS SortPath
    FROM dct_node_adjc child
    JOIN cte parent ON child.parent_id = parent.id
)
SELECT * FROM cte WHERE hlevel = 5;

如果递归阶段没写CONCAT(HEX(parent.SortPath), HEX(child.id))，而是直接用UNHEX(HEX(child.id))，那SortPath只会保留当前节点ID，不会累积路径。

2. 显式指定锚点列的长度（关键修复）

解决截断问题的核心是在锚点查询中显式把SortPath的长度设为VARBINARY(8000)，覆盖MySQL自动生成的短长度：

WITH RECURSIVE cte AS (
    SELECT 
        id,
        parent_id,
        1 AS hlevel,
        -- 强制指定长度为8000字节，避免递归截断
        CAST(UNHEX(HEX(id)) AS VARBINARY(8000)) AS SortPath
    FROM dct_node_adjc
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        child.id,
        child.parent_id,
        parent.hlevel + 1 AS hlevel,
        CAST(UNHEX(CONCAT(HEX(parent.SortPath), HEX(child.id))) AS VARBINARY(8000)) AS SortPath
    FROM dct_node_adjc child
    JOIN cte parent ON child.parent_id = parent.id
)
INSERT INTO dct_node_nest (id, parent_id, hlevel, SortPath)
SELECT id, parent_id, hlevel, SortPath FROM cte;

3. 验证CTE结果的长度

在插入到目标表前，先单独查询CTE的结果，确认SortPath的实际长度：

WITH RECURSIVE cte AS (
    SELECT 
        id,
        parent_id,
        1 AS hlevel,
        CAST(UNHEX(HEX(id)) AS VARBINARY(8000)) AS SortPath
    FROM dct_node_adjc
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        child.id,
        child.parent_id,
        parent.hlevel + 1 AS hlevel,
        CAST(UNHEX(CONCAT(HEX(parent.SortPath), HEX(child.id))) AS VARBINARY(8000)) AS SortPath
    FROM dct_node_adjc child
    JOIN cte parent ON child.parent_id = parent.id
)
SELECT 
    hlevel,
    id,
    LENGTH(SortPath) AS binary_length,  -- 查看二进制字节数
    HEX(SortPath) AS hex_string         -- 查看HEX格式，确认所有节点ID都在
FROM cte
WHERE hlevel = 5;

如果binary_length是40（对应5个BIGINT ID），说明CTE结果正确；如果还是32，那要检查锚点的CAST是否生效，或者节点ID的类型是否符合预期。