如何在MySQL中拼接层级父子节点为指定格式字符串？

问题场景 #

我有一个MySQL数据库表，结构如下：

id     | name             | parent_id | has_children
100017 | Roupas Femininas | 0         | 1
100018 | Meias            | 100017    | 1
100419 | Outros           | 100118    | 0
100418 | Meia calça       | 100118    | 0
100417 | Meias            | 100118    | 0
100117 | Tecidos          | 100017    | 1
100416 | Outros           | 100117    | 0
100413 | Lona             | 100117    | 0
100412 | Jeans            | 100117    | 0

需要提取并拼接所有父节点与子节点，得到如下格式的结果：

Roupas Femininas > Meias > Outros
Roupas Femininas > Meias > Meia calça
Roupas Femininas > Meias > Meias
Roupas Femininas > Tecidos > Outros
Roupas Femininas > Tecidos > Lona
Roupas Femininas > Tecidos > Jeans

我查阅了《Hierarchical Data in MySQL》相关资料，尝试了一段SQL语句但结果不符合预期，请问该如何编写正确的SQL以得到预期结果？

解决方案 #

从你的数据来看，这是固定三层的层级结构：根节点（parent_id=0）→ 中间节点（has_children=1）→ 叶子节点（has_children=0），直接用三次表关联就能搞定：

SELECT 
    CONCAT(root.name, ' > ', middle.name, ' > ', leaf.name) AS category_path
FROM 
    your_table_name leaf
JOIN 
    your_table_name middle ON leaf.parent_id = middle.id
JOIN 
    your_table_name root ON middle.parent_id = root.id
WHERE 
    leaf.has_children = 0
    AND root.parent_id = 0
ORDER BY 
    root.name, middle.name, leaf.name;

注意事项 #

• 把your_table_name替换成你实际的表名
• 用CONCAT拼接三层节点的名称，用> 分隔成预期格式
• 通过WHERE条件只筛选叶子节点和根节点，确保输出的都是完整的三层路径
• 最后排序让结果更整齐

如果你的层级结构可能有更多层（不是固定三层），可以用MySQL 8.0及以上支持的递归CTE来处理任意深度的路径：

WITH RECURSIVE category_path AS (
    -- 先取出所有根节点，初始化路径为节点名称
    SELECT 
        id, 
        name, 
        parent_id, 
        CAST(name AS CHAR(255)) AS path
    FROM 
        your_table_name
    WHERE 
        parent_id = 0
    UNION ALL
    -- 递归关联子节点，把子节点名称拼到父路径后面
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        CONCAT(cp.path, ' > ', c.name) AS path
    FROM 
        your_table_name c
    JOIN 
        category_path cp ON c.parent_id = cp.id
)
-- 只取叶子节点的完整路径
SELECT path AS category_path
FROM category_path
WHERE has_children = 0
ORDER BY path;

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