如何在MySQL 5.6中通过单查询实现分类表的层级排序？

环境：MySQL 5.6
数据表名：CategoryTable
数据列：

• CATEGORY_ID (INT)
• CATEGORY_NAME (VARCHAR)
• LEVEL (INT)
• MOTHER_CATEGORY (INT)

我已尝试编写基础查询：

SELECT
    CATEGORY_ID, CATEGORY_NAME , LEVEL , MOTHER_CATEGORY
FROM
    CategoryTable 

但不知如何使用ORDER BY子句来得到如下层级排序的结果：

CATEGORY_ID         CATEGORY_NAME            LEVEL     MOTHER_CATEGORY
     1              MainCategory               0               0
     2              -SubCategory1              1               1
     3              --SubCategory2             2               2
     4              ---SubCategory3            3               3
     5              2Nd_Main_Category          0               0
     6              -SubCategory1              1               5
     7              --SubCategory2             2               6
     8              ---SubCategory3            3               7

请问是否可以通过MySQL查询实现该效果？

可以实现，核心是生成每个分类的层级路径字符串用于排序，同时给子分类名称添加对应层级的缩进符号。由于MySQL 5.6不支持递归CTE，这里提供两种适配方案：

方法1：自连接构造路径（适用于固定层级） #

如果你的分类层级是固定的（比如示例中的0-3级），直接通过多次自连接拼接排序路径：

SELECT
    c0.CATEGORY_ID,
    CONCAT(REPEAT('-', c0.LEVEL), c0.CATEGORY_NAME) AS CATEGORY_NAME,
    c0.LEVEL,
    c0.MOTHER_CATEGORY
FROM CategoryTable c0
LEFT JOIN CategoryTable c1 ON c0.MOTHER_CATEGORY = c1.CATEGORY_ID AND c0.LEVEL = 1
LEFT JOIN CategoryTable c2 ON c1.MOTHER_CATEGORY = c2.CATEGORY_ID AND c0.LEVEL = 2
LEFT JOIN CategoryTable c3 ON c2.MOTHER_CATEGORY = c3.CATEGORY_ID AND c0.LEVEL = 3
ORDER BY
    COALESCE(c3.CATEGORY_ID, c2.CATEGORY_ID, c1.CATEGORY_ID, c0.CATEGORY_ID),
    COALESCE(c2.CATEGORY_ID, c1.CATEGORY_ID, c0.CATEGORY_ID),
    COALESCE(c1.CATEGORY_ID, c0.CATEGORY_ID),
    c0.CATEGORY_ID;

方法2：用户变量模拟递归（支持任意层级） #

如果分类层级不固定，用用户变量递归生成每个分类的完整路径：

SELECT
    CATEGORY_ID,
    CONCAT(REPEAT('-', LEVEL), CATEGORY_NAME) AS CATEGORY_NAME,
    LEVEL,
    MOTHER_CATEGORY
FROM (
    SELECT
        t.*,
        @path := CASE
            WHEN t.LEVEL = 0 THEN CAST(t.CATEGORY_ID AS CHAR)
            ELSE CONCAT(@prev_path, ',', t.CATEGORY_ID)
        END AS path,
        @prev_path := CASE
            WHEN t.LEVEL = 0 THEN CAST(t.CATEGORY_ID AS CHAR)
            WHEN t.LEVEL = (SELECT LEVEL FROM CategoryTable WHERE CATEGORY_ID = t.MOTHER_CATEGORY) + 1 THEN CONCAT(@prev_path, ',', t.CATEGORY_ID)
            ELSE SUBSTRING_INDEX(@prev_path, ',', t.LEVEL)
        END
    FROM CategoryTable t
    CROSS JOIN (SELECT @path := '', @prev_path := '') vars
    ORDER BY LEVEL, MOTHER_CATEGORY, CATEGORY_ID
) sorted
ORDER BY path;

关键说明 #

1. 缩进处理：通过REPEAT('-', LEVEL)给对应层级的分类名称添加前缀，LEVEL为0的主分类无缩进，层级每加1多一个-。
2. 排序逻辑：构造的层级路径（如1,2,3）能确保子分类紧跟父分类，形成严格的层级顺序。

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