MySQL 5.7.37如何实现层级递归查询获取指定ID所有子节点

问题背景 #

使用MySQL 5.7.37版本，需查询posts表中指定ID对应的所有子节点，表结构与测试数据如下：

已知MySQL 8.0+支持的WITH RECURSIVE递归CTE语法无法在5.7版本运行，网上流传的用户变量+FIND_IN_SET写法存在执行顺序不确定的缺陷，查询ID=6的节点时会返回错误结果，以下提供两种可稳定运行的方案。

方案1：递归存储过程（推荐，稳定性最高） #

通过临时表存储遍历结果，逐层循环查找下级节点，直到没有新的子节点被找到，逻辑和8.0版本递归CTE完全一致，不存在结果偏差问题。

第一步：创建存储过程 #

DELIMITER //
CREATE PROCEDURE get_all_child_posts(IN root_id INT)
BEGIN
    -- 创建内存临时表存储遍历结果
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_posts (
        id INT,
        name VARCHAR(255),
        parent_id INT,
        level INT
    ) ENGINE = MEMORY;
    -- 清空临时表残留数据
    TRUNCATE TABLE tmp_posts;
    -- 写入根节点，层级标记为0
    INSERT INTO tmp_posts
    SELECT id, name, parent_id, 0 FROM posts WHERE id = root_id;
    -- 循环遍历下一级节点
    SET @cur_level = 0;
    WHILE ROW_COUNT() > 0 DO
        SET @cur_level = @cur_level + 1;
        INSERT INTO tmp_posts
        SELECT p.id, p.name, p.parent_id, @cur_level
        FROM posts p
        JOIN tmp_posts t ON p.parent_id = t.id AND t.level = @cur_level - 1;
    END WHILE;
    -- 返回结果：不需要根节点的话加 WHERE level > 0 即可
    SELECT id, name, parent_id FROM tmp_posts;
    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS tmp_posts;
END //
DELIMITER ;

第二步：调用存储过程 #

例如查询ID=6节点下的所有子节点（包含节点自身）：

CALL get_all_child_posts(6);

执行后会正确返回id为6、3、4、5的4条记录，和8.0递归CTE返回结果完全一致。

方案2：修正版用户变量查询（无需创建存储过程） #

原用户变量写法的问题在于没有固定遍历顺序，MySQL优化器可能改变行读取顺序导致变量赋值异常，修正后写法如下：

SELECT id, name, parent_id
FROM (
    SELECT
        id,
        name,
        parent_id,
        @pv := IF(FIND_IN_SET(parent_id, @pv) > 0, CONCAT(@pv, ',', id), @pv) AS node_list
    -- 必须先按父ID、ID排序，固定遍历顺序
    FROM (SELECT * FROM posts ORDER BY parent_id, id) sorted_posts,
         (SELECT @pv := 6) init_param
) res
WHERE FIND_IN_SET(id, @pv) OR id = 6;

注意：该写法依赖MySQL派生表的执行逻辑，数据量超过10万、或者跨小版本时存在概率性结果异常，生产环境优先选择存储过程方案。

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