MySQL5如何实现Oracle connect by prior及order siblings by功能

MySQL5 没有原生递归CTE支持，常规find_in_set方案只能实现基础的层级遍历，无法做到同级节点按指定字段排序的效果。要适配海量树形表的查询需求，用带排序路径记录的通用递归存储过程是最稳定的方案，不需要提前知道树的深度，排序逻辑和Oracle原生order siblings by完全一致。

核心实现逻辑 #

• 递归遍历树节点时，额外记录每个节点的层级、从根节点到当前节点的全路径排序值
• 最终返回结果时按全路径排序值排序，天然保证父节点排在子节点前、同级节点严格按指定字段排序
• 存储过程通过入参动态适配不同表的字段、表名、根节点规则，一次创建即可给所有树形结构表复用，不需要每张表单独写逻辑

注意：执行前需要先调整MySQL递归深度配置，默认值为0会导致递归失败：SET max_sp_recursion_depth = 255;，如果业务树高超过255可以按需调大，不建议设置超过1000避免栈溢出。

通用存储过程代码 #

DELIMITER //
CREATE PROCEDURE sp_query_tree(
    IN p_table_name VARCHAR(100),    -- 待查询树形表名
    IN p_id_col VARCHAR(50),         -- 主键字段名
    IN p_pid_col VARCHAR(50),        -- 父级关联字段名
    IN p_order_col VARCHAR(50),      -- 同级节点排序字段名
    IN p_order_dir VARCHAR(4),       -- 排序方向，传ASC/DESC
    IN p_root_condition VARCHAR(200) -- 根节点过滤条件，例如"pid = ''"、"pid IS NULL"
)
BEGIN
    -- 初始化结果临时表，存储节点ID、层级、排序路径
    DROP TEMPORARY TABLE IF EXISTS tmp_tree_res;
    CREATE TEMPORARY TABLE tmp_tree_res (
        node_id VARCHAR(100),
        node_level INT,
        sort_path TEXT,
        PRIMARY KEY(node_id)
    );

    -- 内部递归遍历过程
    DROP PROCEDURE IF EXISTS sp_traverse;
    CREATE PROCEDURE sp_traverse(
        IN p_parent_id VARCHAR(100),
        IN p_cur_level INT,
        IN p_parent_path TEXT
    )
    BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE v_id VARCHAR(100);
        DECLARE v_sort_val VARCHAR(200);
        DECLARE v_cur CURSOR FOR SELECT node_id, sort_val FROM tmp_cur_children;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        -- 动态查询当前父节点下的所有子节点/根节点，按排序字段预排序
        SET @child_sql = CONCAT(
            'DROP TEMPORARY TABLE IF EXISTS tmp_cur_children;',
            'CREATE TEMPORARY TABLE tmp_cur_children AS ',
            'SELECT CONCAT('',', p_id_col, ') AS node_id, CAST(`', p_order_col, '` AS CHAR) AS sort_val ',
            'FROM `', p_table_name, '` ',
            'WHERE ', IF(p_parent_id IS NULL, p_root_condition, CONCAT('`', p_pid_col, '` = ''', p_parent_id, '''')),
            ' ORDER BY `', p_order_col, '` ', p_order_dir
        );
        PREPARE stmt FROM @child_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        OPEN v_cur;
        traverse_loop: LOOP
            FETCH v_cur INTO v_id, v_sort_val;
            IF done = 1 THEN
                LEAVE traverse_loop;
            END IF;
            -- 拼接全路径排序值：排序值定长补位，避免数字/字符串排序错乱
            SET @full_path = CONCAT(IFNULL(p_parent_path, ''), LPAD(v_sort_val, 10, '0'), v_id, '/');
            INSERT IGNORE INTO tmp_tree_res VALUES (v_id, p_cur_level + 1, @full_path);
            -- 递归遍历当前节点的子节点
            CALL sp_traverse(v_id, p_cur_level + 1, @full_path);
        END LOOP;
        CLOSE v_cur;
        DROP TEMPORARY TABLE IF EXISTS tmp_cur_children;
    END //

    -- 从根节点启动遍历
    CALL sp_traverse(NULL, 0, '');

    -- 关联原表返回最终结果，按排序路径排序即等效order siblings by效果
    SET @res_sql = CONCAT(
        'SELECT t.*, tr.node_level AS level FROM `', p_table_name, '` t ',
        'INNER JOIN tmp_tree_res tr ON t.`', p_id_col, '` = tr.node_id ',
        'ORDER BY tr.sort_path'
    );
    PREPARE res_stmt FROM @res_sql;
    EXECUTE res_stmt;
    DEALLOCATE PREPARE res_stmt;

    -- 清理资源
    DROP TEMPORARY TABLE IF EXISTS tmp_tree_res;
    DROP PROCEDURE IF EXISTS sp_traverse;
END //
DELIMITER ;

测试场景调用示例 #

针对提供的test表测试数据，调用方式如下：

-- 先调整递归深度
SET max_sp_recursion_depth = 255;
-- 调用通用存储过程
CALL sp_query_tree(
    'test',
    'id',
    'pid',
    'ordernumber',
    'ASC',
    "pid = ''"
);

执行返回结果和Oracle查询预期结果完全一致：

性能优化建议 #

• 海量数据场景下，给每张树形表的父ID字段、排序字段创建联合索引，能大幅提升子节点查询速度
• 如果排序字段是字符串类型，调整存储过程中LPAD的补位长度为排序字段的最大长度，避免字符串排序错乱
• 存储过程一次创建后可永久复用，不需要针对不同表重复编写遍历逻辑，完全兼容GUID类型主键

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