mysql递归语句怎么写_如何在 SQL 中写递归语句

MySQL 在 8.0 的版本引入了公共表表达式(Common Table Expressions)，简称 CTE。CTE 在一些方面可以简化我们的 SQL 语句，让它看起来不至于太臃肿。

CTE 还可以用来写递归，这个在 MSSQL SERVER 上很早就能做到了。

先来看一个 Demo，使用递归生成 1 - 5 的数字系列。

WITH RECURSIVE cte (n) AS

(

SELECT 1

UNION ALL

SELECT n + 1 FROM cte WHERE n < 5

)

SELECT * FROM cte;

输出：

n

--------

1

2

3

4

5

递归表达式的语句有什么特点呢？使用 WITH RECURSIVE 开头，关键词 RECURSIVE 表明这段表达式是递归表达式；

自引用。递归的子查询有两部分，使用 [UNION [ALL]] 或 [UNION DISTINCT] 分开。

SELECT ... # 返回初始数据集

UNION ALL

SELECT ... # 返回其它数据集

第二个 SELECT 里面 FROM 子句之后接的是 CTE 名称，即在这里它引用了自身，这也是实现递归的关键逻辑所在。

要检查一段递归表达式的是否存在问题，需要看这几个方面：有初始数据集和边界条件，当达到了边界递归将不再继续；

正确的迭代表达式。

就拿刚才的 Demo 来说，它的初始数据集是 n = 1 ，终止条件是 n < 5，迭代的表达式是 n = n + 1。如果没有终止条件或者表达式写得有问题(比如把n = n + 1 写成 n = n - 1)，SQL 直到超出了递归最大深度后才会终止。

递归表达式可以用来做什么呢？生成斐波那契数列；

补全两个日期之间的缺失日期；

树形查询。

举一个递归实现树形查询的例子，还是拿 emp 表来说吧。我想知道 emp 表中每个员工的和 boss 之间的层级关系，以及员工所在的层级，使用递归就可以这么做：先获取到 boss 的信息；

然后根据上下级关系不断去迭代，直到找到所有没有下级的员工的信息。

WITH RECURSIVE cte (empno, ename, LEVEL, tree) AS

(SELECT

empno,

ename,

0 AS LEVEL,

CAST(ename AS CHAR(120)) AS tree

FROM

emp

WHERE mgr IS NULL

UNION ALL

SELECT

e.empno,

e.ename,

c.level + 1,

CONCAT_WS('-->', e.ename, c.tree)

FROM

cte c

INNER JOIN emp e

ON e.mgr = c.empno)

SELECT

*

FROM

cte

这条 SQL 需要注意一个地方，我在递归子查询里面的第一个 SELECT 语句中指定了 tree 字段的长度。如果没有指定 tree 字段的长度，它将使用 ename 字段的实际长度作为 tree 字段的长度，在第二个 SELECT 子句中放入超过 tree 字段长度的内容将会被截断。

上面 SQL 执行的结果：

empno ename level tree

------ ------ ------ ------------------------------

7839 KING 0 KING

7566 JONES 1 JONES-->KING

7698 BLAKE 1 BLAKE-->KING

7782 CLARK 1 CLARK-->KING

7499 ALLEN 2 ALLEN-->BLAKE-->KING

7521 WARD 2 WARD-->BLAKE-->KING

7654 MARTIN 2 MARTIN-->BLAKE-->KING

7788 SCOTT 2 SCOTT-->JONES-->KING

7844 TURNER 2 TURNER-->BLAKE-->KING

7900 JAMES 2 JAMES-->BLAKE-->KING

7902 FORD 2 FORD-->JONES-->KING

7934 MILLER 2 MILLER-->CLARK-->KING

7369 SMITH 3 SMITH-->FORD-->JONES-->KING

7876 ADAMS 3 ADAMS-->SCOTT-->JONES-->KING

这篇文章没有涉及到或者讲得不深但我觉得又比较重要的地方：字符串类型的字段的长度在非递归部分指定。因此，在递归子查询中，如果某个字段(字符串类型)，在递归部分的长度会比超出非递归部分指定的长度，超出长度的内容会被截断

递归子查询里面，递归部分访问非递归部分的字段是通过字段名称，而不是字段所在的位置。比如下面这个 SQL，你觉得会输出什么呢？

WITH RECURSIVE cte AS

(

SELECT 1 AS n, 1 AS p, -1 AS q

UNION ALL

SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 3

)

SELECT * FROM cte;

修改递归的最大深度、允许递归语句运行的最长时间。

这些在官方文档里都有详细的说明，想深入研究就去看官方文档吧。13.2.15 WITH (Common Table Expressions)​dev.mysql.com