MySQL多级父子关系表的排序优化及层级展示实现咨询

问题1：如何对多级父子关系表按层级正确排序？ #

你之前用的COALESCE排序逻辑只覆盖了两级关系——能把根节点和它的直接子节点归为一组，但到了第三级节点（比如Station 1 sub），它的parent是二级节点Station 1的ID，此时coalesce(parent, id)返回的是2044，而Station 1的coalesce(parent, id)返回的是2043，两者分组键不同，自然没法排在一起。

要处理多级树形结构的排序，最可靠的方法是生成每个节点的完整祖先路径，然后按路径排序。MySQL 8.0及以上版本支持递归CTE（公共表表达式），可以轻松实现这一点：

WITH RECURSIVE category_tree AS (
    -- 先选中所有根节点（parent为null），初始路径就是自身ID
    SELECT id, name, parent, CAST(id AS CHAR(200)) AS path
    FROM Table1
    WHERE parent IS NULL
    UNION ALL
    -- 递归遍历子节点，把父节点的路径和当前节点ID拼接成新路径
    SELECT t.id, t.name, t.parent, CONCAT(ct.path, ',', t.id) AS path
    FROM Table1 t
    JOIN category_tree ct ON t.parent = ct.id
)
SELECT id, name, parent
FROM category_tree
ORDER BY path;

这个查询会生成每个节点的路径（比如Station 1 sub的路径是2043,2044,2050），按路径排序后，所有子节点都会紧跟在它的完整祖先链后面，完美实现层级排序。

问题2：如何实现带短横线的层级展示？ #

方法1：MySQL端直接生成格式化结果 #

同样用递归CTE计算每个节点的层级深度，然后用REPEAT函数生成对应数量的短横线前缀：

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent, 0 AS depth
    FROM Table1
    WHERE parent IS NULL
    UNION ALL
    SELECT t.id, t.name, t.parent, ct.depth + 1 AS depth
    FROM Table1 t
    JOIN category_tree ct ON t.parent = ct.id
)
SELECT CONCAT(REPEAT('-', depth), name) AS formatted_category
FROM category_tree
ORDER BY 
    -- 先按根节点分组，再按层级，最后按ID排序
    CASE WHEN parent IS NULL THEN id ELSE CAST(parent AS UNSIGNED) END,
    depth,
    id;

执行后会直接输出你想要的格式：

Company Ops
-Station 1
-Station 2
-Station 3
--Station 1 sub
FM Stuff
-New Const
-Blighted

方法2：PHP端构建树形结构后输出 #

如果你的MySQL版本低于8.0（不支持递归CTE），可以用PHP先把所有数据查出来，再构建树形结构递归输出：

// 假设从数据库查询到的分类数据是这个格式
$categories = [
    ['id' => 2043, 'name' => 'Company Ops', 'parent' => null],
    ['id' => 2044, 'name' => 'Station 1', 'parent' => '2043'],
    ['id' => 2045, 'name' => 'Station 2', 'parent' => '2043'],
    ['id' => 2049, 'name' => 'Station 3', 'parent' => '2043'],
    ['id' => 2050, 'name' => 'Station 1 sub', 'parent' => '2044'],
    ['id' => 2047, 'name' => 'FM Stuff', 'parent' => null],
    ['id' => 2046, 'name' => 'New Const', 'parent' => '2047'],
    ['id' => 2048, 'name' => 'Blighted', 'parent' => '2047'],
];

// 构建以parent为键的树形数组
$tree = [];
foreach ($categories as $cat) {
    // 把null转为字符串方便作为键，或者根据实际存储调整
    $parentKey = $cat['parent'] ?? 'root';
    $tree[$parentKey][] = $cat;
}

// 递归输出分类
function printCategories($parentKey, $depth = 0, $tree) {
    if (!isset($tree[$parentKey])) return;
    foreach ($tree[$parentKey] as $cat) {
        echo str_repeat('-', $depth) . $cat['name'] . "\n";
        // 递归遍历子节点，层级+1
        printCategories($cat['id'], $depth + 1, $tree);
    }
}

// 调用函数输出根节点（parent为null的分组）
printCategories(null);

运行这段PHP代码后，也会输出你需要的带层级短横线的格式。

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