MySQL层级递归查询异常修复：无法获取完整层级结构

问题说明 #

现有MySQL表结构如下：

尝试用以下查询获取parent_id=0的完整层级数据：

select  id,
        username,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '0') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

但仅返回部分结果：

期望得到完整的层级链：

当前使用MySQL 8.0.27，且无法修改id和parent_id字段，需修复查询逻辑。

修复方案 #

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

MySQL 8.0及以上版本支持递归公共表表达式（CTE），语法清晰且稳定，是处理层级结构查询的官方推荐方式：

WITH RECURSIVE hierarchy AS (
    -- 递归起始节点：parent_id=0的根节点
    SELECT id, Name AS username, parent_id
    FROM products
    WHERE parent_id = 0
    UNION ALL
    -- 递归关联子节点：将当前层级的节点作为父节点，匹配下一级子节点
    SELECT p.id, p.Name AS username, p.parent_id
    FROM products p
    JOIN hierarchy h ON p.parent_id = h.id
)
SELECT * FROM hierarchy;

方案2：修复原有变量查询逻辑 #

原有查询失效的核心原因是排序逻辑错误：order by parent_id, id导致子节点（如ID31）先于其父节点（ID1）被加载，此时@pv变量中还未包含父节点ID，无法匹配子节点。

若坚持使用变量方式，需调整排序规则，确保父节点在子节点之前被处理。但这种方式依赖固定的层级顺序，通用性较差，示例如下：

select  id,
        Name as username,
        parent_id 
from    (select * from products
         order by 
             -- 按层级顺序手动指定排序优先级，确保父节点先出现
             case parent_id
                 when 0 then 0
                 when 3 then 1
                 when 2 then 2
                 when 1 then 3
                 when 31 then 4
                 when 32 then 5
             end, id) products_sorted,
        (select @pv := '0') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

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