MySQL 8.0+ 用 WITH RECURSIVE 查层级树并带路径，必须显式定义锚点与递归成员，锚点需初始化 path（如 CAST(id AS CHAR)），递归中用 CONCAT(tp.path, '/', c.id) 拼接，注意类型转换和循环防护。
MySQL 8.0+ 怎么用 WITH RECURSIVE 查层级树并带路径
直接上结论：必须显式定义锚点（anchor）和递归成员（recursive term），且递归查询里不能出现聚合、GROUP BY、ORDER BY（除非在最外层）。路径拼接要用字符串函数，不是简单加号。
常见错误是写成这样：SELECT id, name, CONCAT(path, '/', id) FROM tree WHERE parent_id IS NULL UNION ALL SELECT ... —— 锚点没初始化 path 字段，导致后续所有 CONCAT 都为 NULL。
- 锚点部分必须提供初始
path值，比如CAST(id AS CHAR)或CONCAT('/', id) - 递归部分的
CONCAT要注意类型兼容：若id是整型，得先转CHAR，否则整数 + 字符串在某些模式下会报错或截断 - 路径分隔符建议统一用
/，避免\在 Windows 环境或 JSON 输出时引发转义问题 - 递归深度默认 1000，超深树要提前设
SET SESSION cte_max_recursion_depth = 2000
WITH RECURSIVE tree_path AS ( SELECT id, parent_id, name, CAST(id AS CHAR) AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, CONCAT(tp.path, '/', c.id) FROM categories c INNER JOIN tree_path tp ON c.parent_id = tp.id ) SELECT * FROM tree_path ORDER BY path;
PostgreSQL 的 CONNECT BY 替代方案：用 RECURSIVE CTE + ARRAY 存路径
PostgreSQL 没有 CONNECT BY，但 ARRAY 类型让路径管理更安全——不会因字符串拼接出非法 ID 或被注入干扰。
典型误用是直接用 || 拼字符串，结果遇到含斜杠的 name 就破坏路径结构；或者忽略循环引用，查着查着卡死。
- 用
ARRAY[id]初始化路径，递归时用tp.path || c.id扩展，天然防 SQL 注入和分隔符污染 - 必须加
WHERE c.id != ALL(tp.path)判断循环，否则自引用节点（如parent_id = id）会导致无限递归 -
ARRAY路径可直接用于后续@>包含查询，比如找某节点所有子节点：path @> ARRAY[123] - 输出时用
array_to_string(path, '/')转成可读格式，别在 CTE 里就转，影响索引下推和去重效率
SQL Server 的 hierarchyid 怎么配合 JOIN 做树形关联
hierarchyid 不是装饰，是物理存储结构，JOIN 时用它比用 parent_id 关联快得多，但前提是字段必须真实存在且已建索引。
很多人把 hierarchyid::GetRoot() 当万能起点，却忘了它返回的是 /，而实际数据里可能是 /1/ 或 /1.1/，直接 = 匹配会漏根节点。
- 查某节点子树：用
node.IsDescendantOf(@root) = 1，别用LIKE模糊匹配路径字符串 - JOIN 时优先用
hierarchyid字段等值或范围比较，避免回表查parent_id - 插入新节点前必须调用
GetDescendant或GetReparentedValue生成合法hierarchyid，硬插字符串如'/1/2/'可能校验失败 - 导出路径时用
.ToString()方法，不要自己拼，否则可能丢失精度（比如/1.1.2/和/1.10.2/字符串比较会错乱）
跨数据库写法：不用递归 CTE 时怎么靠 JOIN 模拟三层以内树
如果目标库不支持递归（如 MySQL 5.7、SQLite），又确定层级 ≤ 3，用 LEFT JOIN 连三次是最稳的，比手写多个 UNION 更易读、更易加条件。
陷阱在于别把第三层 JOIN 条件写成 ON t3.parent_id = t2.id 却忘了 t2 可能为 NULL，结果第三层全丢——LEFT JOIN 的 ON 是“驱动侧为空也保留”，但 WHERE 里一加非空判断就变 INNER 了。
- 每层 JOIN 后用
COALESCE(t1.name, t2.name, t3.name)做兜底名，避免 NULL 干扰排序 - 路径字段手动拼：
CONCAT(COALESCE(t1.id, ''), '/', COALESCE(t2.id, ''), '/', COALESCE(t3.id, ''))，再用REPLACE(..., '//', '/')清理空段 - WHERE 条件只放最外层过滤，中间层别加
WHERE t2.id IS NOT NULL，那等于强制要求有二级节点 - 性能上，三连 JOIN 比递归 CTE 在浅层更快，但超过三层立刻不可维护，别硬撑
路径里的层级顺序、循环防护、类型转换，这三个地方一松懈，查出来的树就错位、截断或死锁。真要支持动态深度，别省那几行代码，老老实实上递归 CTE。
