MySQL 8中WITH RECURSIVE查询结果无法按层级顺序排序求助

我正在使用WITH RECURSIVE查询生成用户层级结构，查询能返回正确结果，但数据顺序不符合需求。多次调整后仍无法得到目标结果。

当前查询结果 #

+-----+--------+------------+------------+
| id  | title  | parent_id  | sort_order |
+-----+--------+------------+------------+
|  1  | null   | null       |          0 |
|  2  | A      | 1          |          1 |
|  3  | B      | 1          |          2 |
|  4  | AA     | 2          |          1 |
|  5  | AB     | 2          |          2 |
| 13  | BA     | 3          |          1 |
| 17  | BC     | 3          |          2 |
|  6  | AAA    | 4          |          1 |
|  7  | AAB    | 4          |          2 |
|  8  | AAC    | 4          |          3 |
|  9  | ABA    | 5          |          1 |
| 10  | ABB    | 5          |          2 |
| 11  | ABC    | 5          |          3 |
| 12  | ABD    | 5          |          4 |
| 16  | BAA    | 13         |          1 |
+-----+--------+------------+------------+

期望结果 #

+-----+--------+------------+------------+
| id  | title  | parent_id  | sort_order |
+-----+--------+------------+------------+
|  1  | null   | null       |          0 |
|  2  | A      | 1          |          1 |
|  4  | AA     | 2          |          1 |
|  6  | AAA    | 4          |          1 |
|  7  | AAB    | 4          |          2 |
|  8  | AAC    | 4          |          3 |
|  5  | AB     | 2          |          2 |
|  9  | ABA    | 5          |          1 |
| 10  | ABB    | 5          |          2 |
| 11  | ABC    | 5          |          3 |
| 12  | ABD    | 5          |          4 |
|  3  | B      | 1          |          2 |
| 13  | BA     | 3          |          1 |
| 16  | BAA    | 13         |          1 |
| 17  | BC     | 3          |          2 |
+-----+--------+------------+------------+

当前WITH RECURSIVE查询是广度优先（先取出所有一级节点，再所有二级节点），而我需要的是深度优先：遍历完一个节点的完整子层级后，再处理下一个同级节点。

标题中的数据为示例数据，便于理解层级结构。

解决方案：维护排序路径字段实现深度优先排序 #

可以通过在递归CTE中维护一个层级排序路径来实现目标顺序。这个路径会拼接每一层节点的sort_order（需格式化为固定长度字符串，避免数字排序问题），最终按该路径排序即可。

修改后的查询语句如下：

WITH RECURSIVE hierarchy AS (
    -- 锚点成员：根节点，初始化排序路径
    SELECT 
        id, 
        title, 
        parent_id, 
        sort_order,
        -- 将sort_order格式化为固定长度字符串，确保排序时顺序正确
        LPAD(sort_order::TEXT, 5, '0') AS sort_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归成员：拼接父节点的排序路径与当前节点的sort_order
    SELECT 
        c.id, 
        c.title, 
        c.parent_id, 
        c.sort_order,
        h.sort_path || '.' || LPAD(c.sort_order::TEXT, 5, '0') AS sort_path
    FROM categories c
    JOIN hierarchy h ON c.parent_id = h.id
)
SELECT id, title, parent_id, sort_order
FROM hierarchy
ORDER BY sort_path;

原理说明 #

1. 锚点成员：从根节点开始，将根节点的sort_order格式化为固定长度的字符串作为初始排序路径（比如LPAD确保1变成00001，10变成00010，避免字符串排序时10排在1前面）。
2. 递归成员：每次递归时，将父节点的排序路径与当前节点的格式化sort_order拼接，形成当前节点的完整排序路径（比如根节点路径是00000，其子节点A的路径是00000.00001，A的子节点AA的路径是00000.00001.00001）。
3. 最终排序：通过ORDER BY sort_path，数据库会按照拼接的路径字符串排序，自然实现深度优先的层级遍历——先遍历完一个分支的所有节点，再回到同级节点处理下一个分支。

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