怎么在MySQL中实现自连接查询多级树形结构-mysql教程-PHP中文网

不能直接用join实现无限级树，因join仅支持固定深度（如三级需写三次join），无法应对动态层级；mysql 5.7及更早不支持with recursive，8.0+才引入递归cte，且自连接无法生成可排序路径、易漏数据、不兼容聚合与索引。

怎么在mysql中实现自连接查询多级树形结构

为什么不能直接用 JOIN 实现无限级树？

MySQL 没有原生的递归 CTE（直到 8.0 才支持），5.7 及更早版本连 WITH RECURSIVE 都不识别。你写 WITH RECURSIVE t AS (...) 会直接报错 ERROR 1064 (42000)。即使在 MySQL 8.0+，自连接本身也不等于递归查询——单纯多层 JOIN 只能查固定深度（比如三级就写三次 JOIN），无法应对“某节点下有 5 级子节点”这种动态场景。

MySQL 8.0+ 正确做法：用 WITH RECURSIVE 构建路径

核心是把树结构转为带层级和路径的扁平结果。假设表 categories 有 id、name、parent_id 字段：

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS level, CAST(id AS CHAR(1000)) 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;

注意点：

CAST(id AS CHAR(1000)) 是必须的——递归 CTE 要求所有分支的列类型一致，否则报错 ERROR 1250 (HY000): Table 'tree' has different number of columns
path 字段用于排序保证树形顺序，也方便前端解析层级
若要查某个节点的所有祖先，把 UNION ALL 方向反过来（从子往父递归），起始条件改为 WHERE id = ?

MySQL 5.7 或更低版本：用闭包表或路径枚举替代

硬写自连接查多级只会让 SQL 膨胀且不可维护。真实项目中更推荐两种方案：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

路径枚举（Path Enumeration）：在原表加一个 path 字段，存类似 '/1/5/12/' 的字符串，用 LIKE '/1/%' 查子树，用 SUBSTRING_INDEX 解析父级
闭包表（Closure Table）：额外建一张 category_closure 表，字段为 ancestor_id、descendant_id、depth，每次插入节点时预计算并写入所有祖先-后代关系。查子树变成简单 SELECT descendant_id FROM category_closure WHERE ancestor_id = ?

两者都牺牲写性能换读性能，但比拼接 N 层 LEFT JOIN 更可靠、可索引、易维护。

自连接写法只适用于明确的固定层级

比如查“一级分类 → 二级分类 → 三级分类”的三元组，可以用三层 JOIN：

SELECT 
  lv1.name AS level1,
  lv2.name AS level2,
  lv3.name AS level3
FROM categories lv1
LEFT JOIN categories lv2 ON lv2.parent_id = lv1.id
LEFT JOIN categories lv3 ON lv3.parent_id = lv2.id
WHERE lv1.parent_id IS NULL;

但要注意：