MySql 8 实现递归查询父子集
文章转载自:【MySql 8】WITH RECURSIVE 递归查询父子集
背景
开发过程中遇到一个问题,需要从父级 id 查询全部子集和子集的子集,
以前的实现方式是查询全部数据在后台通过代码实现,但是这样 数据量小还可以,数据量一大,就有问题。
后来百度 mysql 的父子查询也都是存储过程,但是项目不允许使用存储过程,不利于后续维护。
最后终于发现了一篇文章有讲到 mysql 自带的函数WITH RECURSIVE
实现父子集查询
ps:WITH RECURSIVE
我只在 mysql8.0 以上使用过,其他版本请自行测试!
需求
找到 name 为张三的孩子和孙子,pid 为当前记录的父 id,如张三儿子的 pid 为张三的 id,以此类推。
表格数据:
| id | name | pid |
| :-- | :------- | :-- |
| 8 | 张三 | 0 |
| 9 | 李四 | 0 |
| 10 | 张三儿子 | 8 |
| 11 | 张三孙子 | 10 |
| 12 | 李四女儿 | 9 |
处理结果:
| id | name | pid |
| :-- | :------- | :-- |
| 8 | 张三 | 0 |
| 10 | 张三儿子 | 8 |
| 11 | 张三孙子 | 10 |
使用
# 计算1到100的累加的结果。
WITH RECURSIVE t(n) AS ( //t为我们结果表,n为字段,可以只指定表明不指定字段
VALUES (1) //递归的开始,此时可理解为t表字段n只有一条记录 1
UNION ALL
SELECT n+1 FROM t WHERE n < 100
// 这里产生的结果为 2 ,此时t表的字段n有两条记录分别为1,2,3 ...100
)
SELECT sum(n) FROM t; //对字段n求和
父求子
resource 表:
| id | name | pid |
| :-- | :------- | :-- |
| 8 | 张三 | 0 |
| 9 | 李四 | 0 |
| 10 | 张三儿子 | 8 |
| 11 | 张三孙子 | 10 |
| 12 | 李四女儿 | 9 |
temp 表:
| id | name | pid |
| :-- | :--- | :-- |
| 8 | 张三 | 0 |
期望结果:
| id | name | pid |
| :-- | :------- | :-- |
| 8 | 张三 | 0 |
| 10 | 张三儿子 | 8 |
| 11 | 张三孙子 | 10 |
WITH RECURSIVE temp AS ( // 将结果表命名为temp
SELECT * FROM resource r WHERE r.name ='张三' //查询出父id这条记录,此时这条记录已存在temp表中
UNION ALL
/*
*这时要注意,下面这条sql是获取的期望结果中的后两条记录(不包含第一条)
*注意where后的条件,我们使用temp表中的唯一一条记录的id关联resource表中的pid
*仅当temp第一条记录匹配不到resource表中的pid时才会对temp的第二条记录id进行匹配
*/
SELECT r.* FROM resource r,temp t WHERE t.id = r.pid
) select * from temp
子求父
resource 表:
| id | name | pid |
| :-- | :------- | :-- |
| 8 | 张三 | 0 |
| 9 | 李四 | 0 |
| 10 | 张三儿子 | 8 |
| 11 | 张三孙子 | 10 |
| 12 | 李四女儿 | 9 |
temp 表:
| id | name | pid |
| :-- | :--- | :-- |
| 11 | 张三孙子 | 10 |
期望结果:
| id | name | pid |
| :-- | :------- | :-- |
| 11 | 张三孙子 | 10 |
| 10 | 张三儿子 | 8 |
| 8 | 张三 | 0 |
WITH recursive temp AS (
SELECT * FROM resource r WHERE r.name ='张三孙子'
UNION ALL
//已知的是子集,所以我们需要通过temp的pid匹配resource的id
SELECT r.* FROM resource r,temp t WHERE t.pid = r.id
)select * from temp
Q.E.D.