MySQL 8递归查询中如何将层级节点名转为多列？

先明确示例表结构 #

假设你的Category表结构如下（可对应调整为你的实际字段）：

CREATE TABLE Category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES Category(id)
);

场景1：已知最大层级数（如最多3级） #

如果能确定分类的最大深度，直接用递归CTE结合JSON数组存储层级名称，再提取为固定列：

WITH RECURSIVE CategoryHierarchy AS (
    -- 根节点（parent_id为NULL）
    SELECT 
        id,
        category_name,
        parent_id,
        1 AS level,
        JSON_ARRAY(category_name) AS level_names
    FROM Category
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归遍历子节点
    SELECT 
        c.id,
        c.category_name,
        c.parent_id,
        ch.level + 1 AS level,
        JSON_ARRAY_APPEND(ch.level_names, '$', c.category_name) AS level_names
    FROM Category c
    JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)
SELECT 
    id,
    category_name,
    JSON_UNQUOTE(JSON_EXTRACT(level_names, '$[0]')) AS Category1Name,
    JSON_UNQUOTE(JSON_EXTRACT(level_names, '$[1]')) AS Category2Name,
    JSON_UNQUOTE(JSON_EXTRACT(level_names, '$[2]')) AS Category3Name
    -- 若有更多层级，继续添加`JSON_UNQUOTE(JSON_EXTRACT(level_names, '$[n]')) AS Category{n+1}Name`
FROM CategoryHierarchy
ORDER BY id;

说明：递归过程中用JSON_ARRAY_APPEND把当前分类名称追加到层级数组，主查询通过JSON_EXTRACT提取对应层级的名称，无对应层级时会返回NULL，导出Excel时会显示为空单元格，符合需求。

场景2：未知最大层级数（动态生成列） #

如果分类层级不固定，需要用存储过程动态生成SQL，自动适配最大深度：

DELIMITER //

CREATE PROCEDURE FlattenCategoryHierarchy()
BEGIN
    -- 第一步：获取分类的最大层级数
    DECLARE max_level INT;
    WITH RECURSIVE CategoryHierarchy AS (
        SELECT id, parent_id, 1 AS level FROM Category WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.parent_id, ch.level + 1 FROM Category c JOIN CategoryHierarchy ch ON c.parent_id = ch.id
    )
    SELECT MAX(level) INTO max_level FROM CategoryHierarchy;

    -- 第二步：动态拼接列选择语句
    DECLARE column_list VARCHAR(1000) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < max_level DO
        SET column_list = CONCAT(column_list, 
            ', JSON_UNQUOTE(JSON_EXTRACT(level_names, \'$[', i, ']\')) AS Category', i+1, 'Name'
        );
        SET i = i + 1;
    END WHILE;

    -- 第三步：构建完整SQL并执行
    SET @sql = CONCAT(
        'WITH RECURSIVE CategoryHierarchy AS (
            SELECT 
                id,
                category_name,
                parent_id,
                1 AS level,
                JSON_ARRAY(category_name) AS level_names
            FROM Category
            WHERE parent_id IS NULL
            UNION ALL
            SELECT 
                c.id,
                c.category_name,
                c.parent_id,
                ch.level + 1 AS level,
                JSON_ARRAY_APPEND(ch.level_names, \'$\', c.category_name) AS level_names
            FROM Category c
            JOIN CategoryHierarchy ch ON c.parent_id = ch.id
        )
        SELECT id, category_name', column_list, ' FROM CategoryHierarchy ORDER BY id;'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用存储过程生成结果
CALL FlattenCategoryHierarchy();

说明：存储过程先计算最大层级，再循环拼接对应层级的列，最后执行动态生成的递归查询，自动适配所有层级。

注意事项 #

• 用JSON数组而非字符串拼接的好处：避免分类名称中包含分隔符（如逗号、竖线）导致的拆分错误，可靠性更高。
• 导出Excel时，NULL值会自动显示为空单元格，无需额外处理。

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