SQL如何实现树形结构的路径关联：利用递归CTE配合Join查询
现在，MySQL 8.0+ 和 PostgreSQL 都支持 WITH RECURSIVE 语法来处理树形数据，这无疑是个好消息。但先别急着高兴，这里有个常见的“坑”：如果你以为语法通用就能直接照搬，那大概率会踩雷。两套数据库在路径拼接、类型安全以及循环防护机制上，差异其实非常大，稍不注意，查出来的可能就是乱码、数据截断，甚至引发无限递归。
MySQL 8.0+ 中 CONCAT 拼路径必须做 CAST 类型转换
MySQL 对字符串的长度和类型相当敏感。举个例子，如果 id 字段是整数类型，直接写 CONCAT(tp.path, '/', c.id) 可能会出问题。这里存在隐式类型转换，一旦转换失败或者 path 字段长度超过了默认的 CHAR(1) 宽度，数据就会被无情截断。
- 锚点部分必须显式初始化：在递归的起始部分，务必用
CAST(id AS CHAR(255))来显式定义路径字段的类型和长度。如果不这么做，递归过程中CONCAT函数会按照最窄的类型来推导，路径越长，被截断的风险就越高。 - 递归部分同样需要转换：在递归成员里拼接子节点ID时，
c.id也要进行CAST(c.id AS CHAR)操作，千万别依赖数据库的隐式转换，那不够可靠。 - 分隔符的选择有讲究：建议使用
'/'作为分隔符，而不是'.'。这既能避免与数值的小数点混淆，也方便后续使用正则表达式或者SUBSTRING_INDEX函数来提取路径中的特定部分。
WITH RECURSIVE tree_path AS (
SELECT id, parent_id, name, CAST(id AS CHAR(255)) AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name,
CONCAT(tp.path, '/', CAST(c.id AS CHAR))
FROM categories c
INNER JOIN tree_path tp ON c.parent_id = tp.id
)
SELECT * FROM tree_path;
PostgreSQL 用 ARRAY 存路径比字符串更安全
相比之下，PostgreSQL 提供了更优雅的解决方案：使用 ARRAY 类型来存储路径。这种方式天生具备防SQL注入、防非法ID拼接的优势，而且支持 @> 这类数组运算符来进行高效的子树判定，完全不需要依赖字符串的 LIKE 或正则匹配，性能和维护性都更好。
- 锚点写法简洁：直接使用
ARRAY[id] AS path，PostgreSQL会自动推导为integer[]类型，非常省心。 - 递归拼接语义清晰：拼接操作使用
tp.path || c.id运算符，而不是CONCAT，保证了类型的一致性和代码的可读性。 - 防循环是关键：必须记得在递归成员中加入
WHERE NOT c.id = ANY(tp.path)这个条件。否则，如果数据中存在自引用节点（比如某个节点的parent_id等于自己的id），查询就会陷入无限循环，直到栈溢出。 - 输出转换放最后：如果需要输出可读的字符串路径，统一在最外层的
SELECT中使用array_to_string(path, '/')进行转换。不要在CTE内部转换，这样才能充分利用数组索引下推来优化查询性能。
WITH RECURSIVE tree_path AS ( SELECT id, parent_id, name, ARRAY[id] AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, tp.path || c.id FROM categories c INNER JOIN tree_path tp ON c.parent_id = tp.id WHERE NOT c.id = ANY(tp.path) -- 关键：防自环 ) SELECT id, name, array_to_string(path, '/') AS path FROM tree_path;
用路径结果做 JOIN 关联时，别在递归 CTE 里 ORDER BY 或 GROUP BY
这是一个需要特别注意的语法限制：在 WITH RECURSIVE 的递归成员（也就是 UNION ALL 右侧的部分）中，是禁止出现 ORDER BY、GROUP BY 或者聚合函数的。如果加了，MySQL会报 ERROR 3642，PostgreSQL则会提示递归查询格式不正确。
- 排序分组放外层：所有排序、去重、分组操作，都必须放到最外层的
SELECT语句中。例如，可以写成SELECT * FROM tree_path ORDER BY path。 - 关联查询分步走：如果需要关联其他表（比如查询每个部门下的员工数量），正确的做法是：先通过递归CTE生成完整的路径结果集，然后再在外层查询中与员工表进行
JOIN。不要试图在递归内部直接进行LEFT JOIN。 - 注意字符串比较的差异：如果生成的路径字段要用于
JOIN条件，需要留意MySQL和PostgreSQL的细微差别。MySQL的字符串比较默认会忽略末尾空格，而PostgreSQL则严格区分。稳妥起见，可以在两端都使用TRIM()函数，或者干脆统一使用PostgreSQL的ARRAY类型来避免歧义。
真正影响性能的不是递归本身，而是 parent_id 和 id 上缺索引
很多人误以为递归查询本身很慢，其实不然。递归查询的本质是进行多次单层连接（JOIN），每一次递归都要根据 parent_id 去查找它的子节点。如果 parent_id 字段上没有索引，那么每一次查找都是一次全表扫描。想象一下，一个10层深的树，可能就意味着10次全表扫描，性能瓶颈立刻就会出现。
- 索引是性能基石：必须在
parent_id字段上建立索引。如果条件允许，建立(parent_id, id)这样的联合索引效果更佳，因为它可以覆盖查询所需的所有字段，避免回表。 - 筛选条件要放对地方：对于根节点的筛选条件（例如
WHERE name = '技术部'），一定要放在递归CTE的锚点部分。如果错误地放到外层查询，会导致数据库先展开整棵树，然后再进行过滤，效率极其低下。 - 高级类型的正确使用：PostgreSQL 如果使用了
hierarchyid这类扩展类型（需要安装相应扩展），其物理存储结构确实能优化父子跳转。但前提是这个字段真实存在，并且已经建立了GIST索引，不是简单地改个字段类型就能自动获得性能提升的。
说到底，路径拼接看似只是简单的字符串或数组操作，但类型安全、索引优化、循环防护这三者缺一不可。漏掉任何一环，轻则导致查询结果错乱，重则让整个查询卡死。因此，别急着复制粘贴网上的示例代码，先看看你的执行计划里，有没有出现 Using index condition 和清晰的 Recursive 步骤，这才是保证效率和正确性的关键。
