MySQL如何实现树形结构递归查询并输出层级缩进格式结果

你提到的CONNECT BY是Oracle专属的递归查询语法，MySQL原生不支持，可根据你使用的MySQL版本选择以下方案实现带层级缩进的树形结构查询：

方案1：MySQL 8.0及以上版本（推荐，使用递归CTE） #

该版本支持标准SQL的递归公共表表达式语法，实现逻辑清晰，性能稳定：

WITH RECURSIVE tree_cte AS (
    -- 锚点查询：匹配所有根节点
    SELECT 
        id,
        name,
        parentId,
        0 AS level,
        CAST(id AS CHAR(200)) AS sort_path -- 用于排序保证树形层级顺序
    FROM citizensTree 
    WHERE parentId = -1
    
    UNION ALL
    
    -- 递归查询：逐级匹配子节点
    SELECT 
        t.id,
        t.name,
        t.parentId,
        tc.level + 1 AS level,
        CONCAT(tc.sort_path, ',', t.id) AS sort_path
    FROM citizensTree t
    INNER JOIN tree_cte tc ON t.parentId = tc.id
)
-- 输出带缩进的最终结果，同时保留节点类型字段
SELECT 
    CONCAT(REPEAT('  ', level), name) AS indented_node_name,
    CASE
        WHEN level = 0 THEN 'Root'
        WHEN EXISTS (SELECT 1 FROM citizensTree t2 WHERE t2.parentId = tree_cte.id) THEN 'Inner'
        ELSE 'Leaf'
    END AS node_type
FROM tree_cte
ORDER BY sort_path;

逻辑说明： #

• level字段记录节点的层级深度，根节点层级为0，每向下一级层级+1
• REPEAT(' ', level)生成对应层级的缩进空格，拼接节点名即可得到你需要的缩进输出
• sort_path字段记录从根节点到当前节点的ID路径，排序后可保证子节点紧跟对应父节点展示，不会出现乱序

方案2：MySQL 5.x版本（使用用户变量模拟递归） #

如果使用不支持CTE的旧版本MySQL，可通过用户变量实现同等效果：

SELECT 
    CONCAT(REPEAT('  ', level), name) AS indented_node_name,
    node_type
FROM (
    SELECT 
        t.id,
        t.name,
        t.parentId,
        @level := IF(FIND_IN_SET(parentId, @path) > 0, @level + 1, @level) AS level,
        @path := CONCAT(@path, ',', id) AS sort_path,
        IF(parentId = -1, 'Root', IF(EXISTS(SELECT 1 FROM citizensTree t2 WHERE t2.parentId = t.id), 'Inner', 'Leaf')) AS node_type
    FROM citizensTree t,
    (SELECT @level := 0, @path := '-1') AS init_vars
    ORDER BY parentId, id
) AS tree_temp
ORDER BY sort_path;

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