mysql - 获取所有父节点

Question

在不使用存储过程的情况下，有没有办法将所有父节点作为单独的行获取？

示例数据:

id | parent_id | name
---------------------
 1 |      NULL | Cat1
 2 |      NULL | Cat2
 3 |         1 | Cat3
 4 |         3 | Cat4
 5 |         4 | Cat5
 6 |         2 | Cat6
 7 |      NULL | Cat7

当获取 id = 5 行的所有父项时，结果将是:

id | parent_id | name
---------------------
 1 |      NULL | Cat1
 3 |         1 | Cat3
 4 |         3 | Cat4

我应该注意:我可以进行多个连接(从而对检索到的 parent 数量创建固定限制)。

Answer 1

对于 MySQL 5.5、5.6 和 5.7

SELECT @r AS user_id, 
   (SELECT @r := parent_id FROM users_table WHERE id = user_id) AS parent_id, 
   @l := @l + 1 AS level 

   FROM (SELECT @r := 9, @l := 0) val, users_table WHERE @r <> 0 

注意:@r := 9。其中 9 是子用户的 ID。

See fiddle here

---

上面的查询在 MySQL 8 中被弃用。所以这里是 MySQL 8.0 的查询

with recursive parent_users (id, parent_id, level) AS (
  SELECT id, parent_id, 1 level
  FROM users_table
  WHERE id = 9
  union all
  SELECT t.id, t.parent_id, level + 1
  FROM users_table t INNER JOIN parent_users pu
  ON t.id = pu.parent_id
)
SELECT * FROM parent_users;

注意:id = 9。其中 9 是子用户的 id。

See fiddle here