Mysql 查询子节点所有的父节点（用法+详细解释）

业务场景

现在有一个表 t_user，包含如下两列

t_id  |  parent_id  
1     |  0  
2     |  1
3     |  0 
4     |  2
5     |  4

通过自连接，形成一个树状结构:

这种结构经常应用在“菜单”、“职级”等场景中

问：如何查询子节点所有的父节点。例如，如何查询5上面的所有父节点

“如何查询子节点所有的父节点”SQL及其使用

SELECT
@r AS _id,
(SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id,
@l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars,
t_user h WHERE @r <> 0 AND parent_id > 0

返回结果为：

lvl表示level，即：1=自身，2=父节点，3=父节点的父节点，以此类推。如果不需要，可以去掉

上述SQL的使用方式：

如上图所示，只需要修改我标注的两个地方即可（当然，表明和字段名也要做响应修改）：

• 5：要查询的子节点
• 0: 如果该节点没有父节点，则会被置位 0

上述SQL解释

首先复习几个知识点：

1. @r 表示声明了一个r 变量
2. := 为赋值符号。其他语言中一般直接使用=

上述SQL中的几个变量的意思：

1. @r ：保存当前节点。（从叶节点往根节点找，@r 保存当前到哪个位置了）。@r 初始为要找的节点。即 @r := 5
2. @l：保存当前的Level
3. _id：当前节点。

下面开始拆解SQL：

1. 首先初始化变量@r和@l。即(SELECT @r := 5, @l := 0) vars
2. @r AS _id 将当前节点作为查询结果 _id
3. 查找当前节点_id的父节点，将其赋给@r，然后作为查询结parent_id。即 (SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id
4. 将@l加1，并作为查询结果lvl。即 @l := @l + 1 AS lvl
5. 循环 2,3,4 步骤，得到如下结果：
   
   去掉where条件就是上图的执行结果
6. 执行where条件，过滤掉 @r <> 0 (_id!=0) 和 parent_id > 0 的数据
7. select展示查询结果

经过上述步骤，最终得：