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.