WITH RECURSIVE 是 MySQL 8.0+ 构建层级路径最干净的解法，关键在于通过 path 字段排序实现父排前、子紧随、同级按 name 排，而非依赖 level 或 ROW_NUMBER()；SQL Server 需构造 sort_path 模拟排序逻辑，MySQL 5.7 则需函数但有循环引用风险。
MySQL 8.0+ 直接用 WITH RECURSIVE 构建层级路径
不用写函数、不拼 SQL 字符串，递归 CTE 是最干净的解法。关键不是“查出来”，而是“排序时能反映树形深度和同级顺序”。
-
WITH RECURSIVE必须包含 anchor（根节点）和 recursive member（子节点 JOIN），且 recursive 部分只能引用 CTE 自身一次 - 层级字段（如
level）必须显式累加，不能靠ROW_NUMBER()替代——因为递归结果本身无序，ORDER BY只作用于最终 SELECT，不影响递归过程中的遍历顺序 - 要实现“父排前、子紧随其后、同级按 name 排”，得在
ORDER BY里组合多个字段：path（字符串路径）比level更可靠，比如ORDER BY path，其中path定义为CONCAT('/', id, '/')或更健壮的LPAD(id, 10, '0')拼接
示例（假设表 categories，根节点 parent_id IS NULL）：
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 AS level, CAST(id AS CHAR(200)) AS path
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.level + 1,
CONCAT(t.path, '.', c.id)
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY path;SQL Server 用 CTE + ORDER BY 但需警惕 ORDER SIBLINGS BY 不存在
SQL Server 没有 Oracle 的 ORDER SIBLINGS BY，也不能在 CONNECT BY 里排序兄弟节点——它压根不支持 CONNECT BY。常见误区是以为 ORDER BY 写在 CTE 外就能控制树内顺序，实际不行。
- 真正起作用的是递归过程中子节点的生成顺序：必须在 recursive member 的
JOIN后加ORDER BY子句（但 T-SQL 不允许！）→ 所以得靠path字段模拟排序逻辑 - 推荐做法：在 CTE 中构造
sort_path，用RIGHT('00000' + CAST(id AS VARCHAR), 5)确保数值对齐，避免 “1, 10, 2” 这种字典序错乱 - 如果业务要求“同级按 name 升序”，就得把
name也塞进sort_path，比如CONCAT(sort_path, '_', RIGHT('00000' + CAST(ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY name) AS VARCHAR), 5))，但这会让 CTE 变复杂且影响性能
简明安全版（仅保证父子拓扑，同级顺序由应用层补）：
WITH tree AS (
SELECT id, name, parent_id, 0 AS level,
CAST(RIGHT('00000'+CAST(id AS VARCHAR),5) AS VARCHAR(200)) AS sort_path
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.level + 1,
t.sort_path + '_' + RIGHT('00000'+CAST(c.id AS VARCHAR),5)
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY sort_path;MySQL 5.7 要用函数生成排序键，但注意循环引用风险
getPriority() 类函数看似简单，实际是把排序逻辑从 SQL 层移到了过程层，容易掩盖数据质量问题。
- 函数里用
WHILE循环向上追溯，一旦遇到parent_id指向自身（id=5, parent_id=5）或形成环（1→2→3→1），就会无限循环直到超时或栈溢出 - 函数返回的字符串路径（如
'1.2.5'）在ORDER BY中生效，但 MySQL 对长字符串排序效率低，且无法利用索引 - 若表中存在多棵树（多个 root），函数仍能工作，但排序结果会把所有树混在一起，除非你在
WHERE中先限定某棵子树
调用方式不变，但上线前务必验证：
SELECT * FROM categories ORDER BY getPriority(id);
验证环的 SQL（执行一次即可）：
SELECT c1.id FROM categories c1 JOIN categories c2 ON c1.parent_id = c2.id WHERE c1.id = c2.parent_id OR c1.parent_id = c1.id;
存储过程中拼接动态 SQL 是最后手段，别轻易用
像 SQL Server 示例里用游标遍历 root、拼 UNION ALL 的做法，本质是把递归变成多次查询合并。它绕开了语法限制，代价是维护性差、执行计划不可复用、易 SQL 注入。
- 拼接前必须
QUOTENAME(@Id)（SQL Server）或CAST(@Id AS CHAR)（MySQL），否则整型 ID 被注入恶意字符串 - 每棵子树独立查询，无法跨树排序（比如想让“部门A”的子节点排在“部门B”的根节点之前，做不到）
- 结果集列名、类型必须完全一致，否则
UNION ALL报错；若子树深度不同，还得补NULL占位
真要用，至少加个保护：
-- SQL Server 示例片段
IF EXISTS(SELECT 1 FROM categories WHERE parent_id = @Id AND id = @Id)
BEGIN
RAISERROR('Loop detected at node %d', 16, 1, @Id);
RETURN;
END树形排序真正的难点不在语法，而在数据质量：环、多根、空 parent_id 和非空 parent_id 混用、ID 类型不一致——这些都会让任何优雅方案当场失效。动手前先跑一遍环检测和根节点统计，比调半天 ORDER BY 有用得多。
