MySQL with recursive 用法浅析

目录

写在前面

语句功能

with recursive 语法讲解

细节补充

“union all”语句

添加递归终止条件

---

写在前面

介绍“with recursive”用法的文章不少，但我都觉得讲的不够通俗，所以干脆自己写一篇。话不多说，进入正题。

语句功能

with recursive用于在MySQL中进行递归查询，另外由于树形结构的数据存储，在进行查询时，就是通过递归来实现的，所以很多人接触with recursive就是由于要对树形结构的数据进行查询。但你还是要区分清楚，with recursive并不局限于树形结构的查询，而是只要你需要递归查询，就可以用他。

with recursive 语法讲解

首先吐槽一下，MySQL的with recursive之所以让人在第一次接触的时候感觉不好理解，我个人觉得主要是他这个语法的设计问题，说白了就是这个语法看起来就让人感觉很迷惑，你要是接触过oracle那个递归查询的语法，会发现语法非常精简，很“见名知意”，属于那种几乎不用学习，你看别人写的一个例子，就可以模仿实现自己的需求的。接下来开始讲解，我们先来看个例子。

假设现在有一个公司里的部门信息表，名字叫“department”，除了存放部门信息外，也存储了表的层级关系，大概是这样的：

部门信息表

id	name	parent_id	level
1	总经理		1
2	总经理办公室	1	2
3	研发部	1	2
4	人事部	2	3
5	采购部	2	3
6	java开发部	3	3
7	前端开发部	3	3

“parent_id”表示当前节点的父节点的id，“level”表示当前节点所在的层级，如果对上面这张表进行递归查询，语法是这样的：

with recursive t1 as (select * from department where id = '3'union allselect * from department t2 inner join t1 on t1.id = t2.parent_id
)select * from t1;

这个语句表示从研发部开始，查询研发部的所有子节点，直到树的底部。如果想查询整张表，那就需要从根节点开始，把条件改为“id = '1'”，由于总经理是根节点，就相当于查询整张表。

接下来讲解这个语法，首先“with recursive”的语法有一部分是基本固定的：

with recursive t1 as (union all)select * from t1;

上面这个结构是基本固定的，这个临时表名“t1”你可以改成什么a1、b1、c2都可以，只要用到临时表名的地方一起改就行。剩下的主要就是“union all”上下的两句sql怎么写，在这之前，我们先来看一下，抛开sql语句，单纯的在一棵树里面进行一次递归查询的逻辑是怎样的：

先选中一个节点，查询该节点的所有子节点（假设有n个子节点），然后，再查询这n个子节点的所有子节点（假设有m个子节点），一直循环（也就是递归），到什么时候结束呢，到树的底部，或者你也可以指定查询到某一层。

当然这种从上往下查的看起来相对复杂一点，递归也可以从下往上查，这样简单一些，那么逻辑就是：

先选中一个节点，查询该节点的父节点，然后，继续查询这个父节点的父节点，一直循环（也就是递归），直到树的根节点，或者也可以指定查询到某一层。

 不论从上往下查，还是从下往上查，这里面的关键信息有三个：

1. 选中一个节点，也就是递归的起点；
2. 查询这个节点的下一个节点（如果是从上往下查，就是子节点，如果是从下往上查，就是父节点），但是数据库怎么知道下一个节点是谁呢，这个你才知道，你要告诉数据库，两个节点之间的关联条件；
3. 查到哪里结束，也就是递归的终点。

现在我们再重新看“with recursive”的语法：

with recursive t1 as (//语句1select * from department where id = '3'union all//语句2select * from department t2 inner join t1 on t1.id = t2.parent_id
)select * from t1;

说明：

语句1：是在说明递归的起点，这里就指定从研发部开始递归。

语句2：是在说明当前节点和下一个节点的关联条件，注意看他是怎么表达两个节点的关联条件的，是以连接查询的语法来表达两个节点的关联条件，使用最外层的“with recursive t1”定义的临时表名“t1”来表示当前节点所在的表，另外一个“department”则表示下一个节点所在的表。这条语句本身是从上往下查，所以最后的关联条件就是当前表的id值等于下一张表的parent_id值。

递归的终点在哪呢？上面的例子没有指定递归的重点，所以实际会一直查询到树的底部，如果想指定递归的终点，则是在语句2中指定，像下面这样就是通过表里的某个字段来指定递归的终点：

select * from department t2 inner join t1 on t1.id = t2.parent_id where t2.level < 4;

到此，with recursive 的基本用法就讲解完毕了，注意一下，整个“with as recursive t1 ()”语句都只是在定义递归的逻辑，最下面那行“select * from t1”才是真正的在执行查询，你别把最后这行给漏掉了。

接下来我们验证一下你是否真的学会了。如果你真的理解了“with recursive”的语法，那么上述例子当中的那张表，如果把需求改成从下往上查，你应当能看懂下面的查询语句：

with recursive t1 as (//语句1select * from department where id = '3'union all//语句2select * from department t2 inner join t1 on t1.parent = t2.id
)select * from t1;

语句1：并没有做修改，因为不需要改变递归的起点，只是需求由查询研发部的所有子节点，改成了查询研发部的所有父节点。

语句2：唯一的改动点就在这里，最后面的关联条件给改了，既然是从下往上查，那么应当是当前表的parent_id字段和下一张表的id字段关联。

细节补充

“union all”语句

注意一下递归查询里面的union all语句是遵循union all的语法的，也就是他上下两个语句里面的列是要一致的，以上述那张表为例，如果你不使用“*”，而是指定列的话，两条语句指定的列是需要一致的：

with recursive t1 as (//语句1select id, name, parent_id from department where id = '3'union all//语句2select t2.id, t2.name, t2.parent_id from department t2 inner join t1 on t1.id = t2.parent_id
)select * from t1;

添加递归终止条件

最后在补充一个带了递归终止条件的SQL语句示例：

with recursive t1 as (//语句1select id, name, parent_id from department where id = '3'union all//语句2select t2.id, t2.name, t2.parent_id from department t2 inner join t1 on t1.id = t2.parent_idwhere t2.level < 4
)select * from t1;

结束，goodbye。