MySQL层级数据查询异常：修改parent_id后结果缺失

问题描述 #

原有基于用户变量的MySQL层级查询语句，在将member14的parent_id从13修改为15后，仅返回Member14，无法得到预期的完整分支（8→13→15→14）。

原查询语句：

SELECT  id,
        nama,
        parent_id
FROM    (SELECT * FROM members
         ORDER BY parent_id, id) members,
        (SELECT @pv := '3') initialisation
WHERE   FIND_IN_SET(parent_id, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id)

预期输出：

id     nama     parent_id
8      Member8   3
13     Member13  8
15     Member15  13
14     Member14  15

问题原因 #

原查询依赖用户变量+子查询排序实现层级遍历，核心要求是父节点必须在子节点之前被处理。但ORDER BY parent_id, id的排序逻辑无法保证这一点：当子节点的parent_id大于父节点的parent_id，但子节点的id更小（或表中存在其他干扰数据）时，子节点会被优先处理，此时父节点还未被加入@pv变量，导致子节点无法被匹配；后续父节点处理时，已跳过的子节点也无法被回溯处理。

解决方案 #

方案1：使用MySQL 8.0+递归CTE（推荐） #

MySQL 8.0及以上版本支持递归公共表表达式（CTE），这是官方标准的层级查询方式，不受排序顺序影响，结果稳定可靠：

WITH RECURSIVE member_hierarchy AS (
    -- 初始节点：获取根节点（id=3）
    SELECT id, nama, parent_id
    FROM members
    WHERE id = 3
    -- 递归获取所有后代节点
    UNION ALL
    SELECT m.id, m.nama, m.parent_id
    FROM members m
    JOIN member_hierarchy mh ON m.parent_id = mh.id
)
-- 排除根节点，与原查询输出格式一致
SELECT id, nama, parent_id
FROM member_hierarchy
WHERE id != 3;

方案2：兼容低版本MySQL（调整排序逻辑） #

如果必须适配MySQL 5.x版本，需调整子查询的排序逻辑，确保父节点始终在子节点之前被处理。可以通过生成节点的层级路径来排序：

SELECT  id,
        nama,
        parent_id
FROM    (
    SELECT * FROM members
    ORDER BY (
        -- 生成节点的完整祖先路径，按路径排序保证父节点优先
        SELECT GROUP_CONCAT(ancestor.id ORDER BY ancestor.id SEPARATOR ',')
        FROM (
            SELECT @id := (SELECT parent_id FROM members WHERE id = @id) AS id
            FROM members, (SELECT @id := m.id) init
            WHERE @id IS NOT NULL
        ) ancestors
        JOIN members ancestor ON ancestor.id = ancestors.id
    )
) members,
(SELECT @pv := '3') initialisation
WHERE   FIND_IN_SET(parent_id, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id);

这种方式通过动态生成每个节点的祖先路径保证排序逻辑，但实现复杂，不如递归CTE直观可靠。

内容的提问来源于stack exchange，提问作者Eggy