MySQL多关联生成分类面包屑查询仅返回部分数据问题

原SQL的问题分析 #

1. 根分类筛选错误：原SQL中WHERE c1.velleman_parent = 0不符合数据实际情况——示例数据里根分类的velleman_parent是空值（而非0），导致根分类无法被正确选中。
2. 层级覆盖不全：固定4次LEFT JOIN仅能返回从根到叶子的4级链条，无法单独返回1级、2级、3级分类，这就是为什么只得到423条数据（仅包含4级分类），漏掉了其他层级的100条数据。
3. 面包屑拼接缺陷：直接用CONCAT拼接会在中间层级为空时出现多余的> 符号，导致面包屑格式错误。
4. 重复列名：多个velleman_id列名重复，不符合SQL语法规范（部分数据库会报错）。

优化方案：使用递归CTE（MySQL 8.0+） #

递归CTE是处理层级数据的标准方案，能自动遍历任意深度的分类结构，返回所有未删除分类的完整面包屑信息：

WITH RECURSIVE category_hierarchy AS (
    -- 锚点：所有未删除的分类，初始化路径为自身名称，记录当前层级
    SELECT
        id_category,
        category_name,
        velleman_id,
        velleman_parent,
        prestashop_category,
        prestashop_category_2,
        prestashop_category_3,
        prestashop_category_4,
        deleted,
        CAST(category_name AS CHAR(1000)) AS breadcrumb,
        JSON_ARRAY(velleman_id) AS velleman_ids,
        1 AS level
    FROM velleman_categories
    WHERE deleted = 0

    UNION ALL

    -- 递归步骤：向上关联父分类，拼接面包屑路径和层级ID数组
    SELECT
        ch.id_category,
        ch.category_name,
        ch.velleman_id,
        c.velleman_parent,
        ch.prestashop_category,
        ch.prestashop_category_2,
        ch.prestashop_category_3,
        ch.prestashop_category_4,
        ch.deleted,
        CONCAT(c.category_name, ' > ', ch.breadcrumb) AS breadcrumb,
        JSON_ARRAY_APPEND(ch.velleman_ids, '$', c.velleman_id) AS velleman_ids,
        ch.level + 1 AS level
    FROM category_hierarchy ch
    JOIN velleman_categories c 
        ON ch.velleman_parent = c.velleman_id
        AND c.deleted = 0 -- 只关联未删除的父分类
),
-- 筛选每个分类的完整层级记录（避免递归过程中产生的中间路径）
max_level AS (
    SELECT id_category, MAX(level) AS max_level
    FROM category_hierarchy
    GROUP BY id_category
)
-- 整理最终输出，拆分层级ID为单独列
SELECT
    ch.id_category,
    -- 提取各层级的velleman_id，不足层级显示NULL
    TRIM(BOTH '"' FROM JSON_EXTRACT(ch.velleman_ids, CONCAT('$[', ch.level - 1, ']'))) AS velleman_id_level1,
    TRIM(BOTH '"' FROM JSON_EXTRACT(ch.velleman_ids, CONCAT('$[', ch.level - 2, ']'))) AS velleman_id_level2,
    TRIM(BOTH '"' FROM JSON_EXTRACT(ch.velleman_ids, CONCAT('$[', ch.level - 3, ']'))) AS velleman_id_level3,
    TRIM(BOTH '"' FROM JSON_EXTRACT(ch.velleman_ids, CONCAT('$[', ch.level - 4, ']'))) AS velleman_id_level4,
    ch.breadcrumb AS category_name,
    ch.prestashop_category,
    ch.prestashop_category_2,
    ch.prestashop_category_3,
    ch.prestashop_category_4,
    ch.deleted
FROM category_hierarchy ch
JOIN max_level ml 
    ON ch.id_category = ml.id_category 
    AND ch.level = ml.max_level
ORDER BY ch.id_category;

方案说明 #

1. 递归CTE遍历层级：从每个未删除分类出发，向上递归查询父分类，逐步拼接面包屑路径和velleman_id的JSON数组，同时记录当前层级数。
2. 筛选完整路径：max_level子查询确保每个分类仅返回最完整的层级记录（即递归到根分类的那条数据）。
3. 格式化输出：通过JSON_EXTRACT拆分数组为单独的层级列，用TRIM去掉JSON字符串的引号，不足4级的列自动显示为NULL，完全符合需求格式。

兼容MySQL 5.x的替代方案（无递归CTE） #

如果使用MySQL 5.x不支持递归CTE，可以用多层LEFT JOIN，但需要调整逻辑以包含所有层级分类：

SELECT
    COALESCE(c4.id_category, c3.id_category, c2.id_category, c1.id_category) AS id_category,
    c1.velleman_id AS velleman_id_level1,
    c2.velleman_id AS velleman_id_level2,
    c3.velleman_id AS velleman_id_level3,
    c4.velleman_id AS velleman_id_level4,
    CONCAT_WS(' > ', c1.category_name, c2.category_name, c3.category_name, c4.category_name) AS category_name,
    COALESCE(c4.prestashop_category, c3.prestashop_category, c2.prestashop_category, c1.prestashop_category) AS prestashop_category,
    COALESCE(c4.prestashop_category_2, c3.prestashop_category_2, c2.prestashop_category_2, c1.prestashop_category_2) AS prestashop_category_2,
    COALESCE(c4.prestashop_category_3, c3.prestashop_category_3, c2.prestashop_category_3, c1.prestashop_category_3) AS prestashop_category_3,
    COALESCE(c4.prestashop_category_4, c3.prestashop_category_4, c2.prestashop_category_4, c1.prestashop_category_4) AS prestashop_category_4,
    COALESCE(c4.deleted, c3.deleted, c2.deleted, c1.deleted) AS deleted
FROM velleman_categories c1
LEFT JOIN velleman_categories c2 
    ON c1.velleman_id = c2.velleman_parent
    AND c2.deleted = 0
LEFT JOIN velleman_categories c3 
    ON c2.velleman_id = c3.velleman_parent
    AND c3.deleted = 0
LEFT JOIN velleman_categories c4 
    ON c3.velleman_id = c4.velleman_parent
    AND c4.deleted = 0
WHERE c1.deleted = 0
-- 确保每个分类都被作为起点（包括根、中间、叶子节点）
GROUP BY COALESCE(c4.id_category, c3.id_category, c2.id_category, c1.id_category);

这个方案通过将每个未删除分类作为c1起点，反向关联子分类，同时用CONCAT_WS自动忽略空值拼接面包屑，避免多余的> 符号。但缺点是最多只能支持4级分类，如果有更深的层级需要继续添加JOIN。

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