MySql 实现递归with recursive

MySQL版本8.0.21-2

递归方式生成一个临时表tmp，且填充了10条数据

WITH recursive tmp ( a, b ) AS (
	SELECT
		1,
		'2022-01-01' UNION ALL
	SELECT
		ROUND( RAND()* 10 ),
		b - INTERVAL ROUND( RAND() * 1000 ) DAY 
	FROM
		TMP 
	LIMIT 10 
	) TABLE tmp;
1
2
3
4
5
6
7
8
9
10
11

在这里插入图片描述

以列a递增

WITH recursive tmp ( a, b ) AS (
	SELECT 
	1, 
	'2022-01-01' 
	UNION ALL 
	SELECT 
	a + 1, 
	b - INTERVAL ROUND( RAND() * 1000 ) DAY 
	FROM TMP 
	LIMIT 10 ) TABLE tmp;
1
2
3
4
5
6
7
8
9
10

在这里插入图片描述

输出1~n：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10
)
SELECT * FROM cte;
#可将`LIMIT`改成`WHERE n < 10`
1
2
3
4
5
6
7
8

+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

输出n！，即n的阶乘：

WITH RECURSIVE cte ( n, m ) AS ( SELECT 1, 1 UNION ALL SELECT n + 1, ( n + 1 ) * m FROM cte WHERE n < 10 ) SELECT
* 
FROM
	cte;
#或者
WITH RECURSIVE cte AS (
	SELECT
		1 AS n,
		1 AS m UNION ALL
	SELECT
		n + 1,
		( n + 1 ) * m 
	FROM
		cte 
	WHERE
		n < 10 
	) SELECT
	* 
FROM
	cte;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

+----+---------+
| n  | m       |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       6 |
|  4 |      24 |
|  5 |     120 |
|  6 |     720 |
|  7 |    5040 |
|  8 |   40320 |
|  9 |  362880 |
| 10 | 3628800 |
+----+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

输出前n个数的和：

WITH RECURSIVE cte AS ( SELECT 1 AS n, 1 AS sum UNION ALL SELECT n + 1, sum + n + 1 FROM cte WHERE n < 10 ) SELECT
* 
FROM
	cte;
1
2
3
4

+----+-----+
| n  | sum |
+----+-----+
|  1 |   1 |
|  2 |   3 |
|  3 |   6 |
|  4 |  10 |
|  5 |  15 |
|  6 |  21 |
|  7 |  28 |
|  8 |  36 |
|  9 |  45 |
| 10 |  55 |
+----+-----+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

简单递归用法：
首先我们引出一个问题： 什么叫做递归?
递归：给定函数初始条件,然后反复调用自身直到终止条件.

例子1：递归得到依次递增的序列：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
1
2
3
4
5
6
7

+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
1
2
3
4
5
6
7
8
9

官方文档中对于这个写法的解释:

At each iteration, that SELECT produces a row with a new value one greater than the value of n from the previous row set. The first iteration operates on the initial row set (1) and produces 1+1=2; the second iteration operates on the first iteration’s row set (2) and produces 2+1=3; and so forth. This continues until recursion ends, which occurs when n is no longer less than 5.

也就是说，一个with recursive 由两部分组成。第一部分是非递归部分(union all上方)，第二部分是递归部分(union all下方)。递归部分第一次进入的时候使用非递归部分传递过来的参数。也就是第一行的数据值，进而得到第二行数据值。然后根据第二行数据值得到第三行数据值。

例子2：递归得到不断复制的字符串
这里的as表示列名,表示说这个CTE有两个列,也可以写为with cte(n,str) as (subquery)

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
1
2
3
4
5
6
7

结果：

+---+-----+
| n | str |
+---+-----+
| 1 | abc |
| 2 | abc |
| 3 | abc |
+---+-----+
1
2
3
4
5
6
7

或者报错：‘Data too long for column ‘str’ at row 1’

这里的话concat是每一次都连接一个str，这个str来自上一行的结果，但是最终输出却是每一行都没有变化的值,这是为什么?
这是因为我们在声明str的时候限制了它的字符长度，使用类型转换CAST('abc' AS CHAR(30)) 就可以得到复制的字符串了。
**注意：**这里也可能会报错，看mysql模式在严格模式下这里会显示Error Code: 1406. Data too long for column ‘str’ at row 1
关于strict SQL mode和nonstrict SQL mode：mysql 严格模式 Strict Mode说明

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
1
2
3
4
5
6
7

+---+--------------+
| n | str          |
+---+--------------+
| 1 | abc          |
| 2 | abcabc       |
| 3 | abcabcabcabc |
+---+--------------+
1
2
3
4
5
6
7

当然，如果上一行的值有多个，我们还可以对多个值进行重新组合得到我们想要的结果，比如下面这个例子。

例子3:生成斐波那契数列

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 92
)
SELECT * FROM fibonacci;
1
2
3
4
5
6
7
8

+----+---------------------+---------------------+
| n  | fib_n               | next_fib_n          |
+----+---------------------+---------------------+
|  1 |                   0 |                   1 |
|  2 |                   1 |                   1 |
|  3 |                   1 |                   2 |
|  4 |                   2 |                   3 |
|  5 |                   3 |                   5 |
|  6 |                   5 |                   8 |
|  7 |                   8 |                  13 |
|  8 |                  13 |                  21 |
|  9 |                  21 |                  34 |
| 10 |                  34 |                  55 |
| 11 |                  55 |                  89 |
| 12 |                  89 |                 144 |
| 13 |                 144 |                 233 |
| 14 |                 233 |                 377 |
| 15 |                 377 |                 610 |
| 16 |                 610 |                 987 |
| 17 |                 987 |                1597 |
| 18 |                1597 |                2584 |
| 19 |                2584 |                4181 |
| 20 |                4181 |                6765 |
| 21 |                6765 |               10946 |
| 22 |               10946 |               17711 |
| 23 |               17711 |               28657 |
| 24 |               28657 |               46368 |
| 25 |               46368 |               75025 |
| 26 |               75025 |              121393 |
| 27 |              121393 |              196418 |
| 28 |              196418 |              317811 |
| 29 |              317811 |              514229 |
| 30 |              514229 |              832040 |
| 31 |              832040 |             1346269 |
| 32 |             1346269 |             2178309 |
| 33 |             2178309 |             3524578 |
| 34 |             3524578 |             5702887 |
| 35 |             5702887 |             9227465 |
| 36 |             9227465 |            14930352 |
| 37 |            14930352 |            24157817 |
| 38 |            24157817 |            39088169 |
| 39 |            39088169 |            63245986 |
| 40 |            63245986 |           102334155 |
| 41 |           102334155 |           165580141 |
| 42 |           165580141 |           267914296 |
| 43 |           267914296 |           433494437 |
| 44 |           433494437 |           701408733 |
| 45 |           701408733 |          1134903170 |
| 46 |          1134903170 |          1836311903 |
| 47 |          1836311903 |          2971215073 |
| 48 |          2971215073 |          4807526976 |
| 49 |          4807526976 |          7778742049 |
| 50 |          7778742049 |         12586269025 |
| 51 |         12586269025 |         20365011074 |
| 52 |         20365011074 |         32951280099 |
| 53 |         32951280099 |         53316291173 |
| 54 |         53316291173 |         86267571272 |
| 55 |         86267571272 |        139583862445 |
| 56 |        139583862445 |        225851433717 |
| 57 |        225851433717 |        365435296162 |
| 58 |        365435296162 |        591286729879 |
| 59 |        591286729879 |        956722026041 |
| 60 |        956722026041 |       1548008755920 |
| 61 |       1548008755920 |       2504730781961 |
| 62 |       2504730781961 |       4052739537881 |
| 63 |       4052739537881 |       6557470319842 |
| 64 |       6557470319842 |      10610209857723 |
| 65 |      10610209857723 |      17167680177565 |
| 66 |      17167680177565 |      27777890035288 |
| 67 |      27777890035288 |      44945570212853 |
| 68 |      44945570212853 |      72723460248141 |
| 69 |      72723460248141 |     117669030460994 |
| 70 |     117669030460994 |     190392490709135 |
| 71 |     190392490709135 |     308061521170129 |
| 72 |     308061521170129 |     498454011879264 |
| 73 |     498454011879264 |     806515533049393 |
| 74 |     806515533049393 |    1304969544928657 |
| 75 |    1304969544928657 |    2111485077978050 |
| 76 |    2111485077978050 |    3416454622906707 |
| 77 |    3416454622906707 |    5527939700884757 |
| 78 |    5527939700884757 |    8944394323791464 |
| 79 |    8944394323791464 |   14472334024676221 |
| 80 |   14472334024676221 |   23416728348467685 |
| 81 |   23416728348467685 |   37889062373143906 |
| 82 |   37889062373143906 |   61305790721611591 |
| 83 |   61305790721611591 |   99194853094755497 |
| 84 |   99194853094755497 |  160500643816367088 |
| 85 |  160500643816367088 |  259695496911122585 |
| 86 |  259695496911122585 |  420196140727489673 |
| 87 |  420196140727489673 |  679891637638612258 |
| 88 |  679891637638612258 | 1100087778366101931 |
| 89 | 1100087778366101931 | 1779979416004714189 |
| 90 | 1779979416004714189 | 2880067194370816120 |
| 91 | 2880067194370816120 | 4660046610375530309 |
| 92 | 4660046610375530309 | 7540113804746346429 |
+----+---------------------+---------------------+
92 rows in set (0.06 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

试了下最多可以生成92位，93就报错BIGINT UNSIGNED value is out of range in ‘(fibonacci.num + fibonacci.next_num)’，测试MySQL版本为8.0.21

改成这样：

WITH recursive fibonacci AS (
	SELECT 
	1 AS n,
	0 AS num,
	CAST(1 AS UNSIGNED) AS next_num
	UNION ALL
	SELECT n + 1, next_num, num + next_num FROM fibonacci WHERE n < 93
) SELECT * FROM fibonacci;
1
2
3
4
5
6
7
8

+----+---------------------+----------------------+
| n  | num                 | next_num             |
+----+---------------------+----------------------+
|  1 |                   0 |                    1 |
|  2 |                   1 |                    1 |
|  3 |                   1 |                    2 |
|  4 |                   2 |                    3 |
|  5 |                   3 |                    5 |
|  6 |                   5 |                    8 |
|  7 |                   8 |                   13 |
|  8 |                  13 |                   21 |
|  9 |                  21 |                   34 |
| 10 |                  34 |                   55 |
| 11 |                  55 |                   89 |
| 12 |                  89 |                  144 |
| 13 |                 144 |                  233 |
| 14 |                 233 |                  377 |
| 15 |                 377 |                  610 |
| 16 |                 610 |                  987 |
| 17 |                 987 |                 1597 |
| 18 |                1597 |                 2584 |
| 19 |                2584 |                 4181 |
| 20 |                4181 |                 6765 |
| 21 |                6765 |                10946 |
| 22 |               10946 |                17711 |
| 23 |               17711 |                28657 |
| 24 |               28657 |                46368 |
| 25 |               46368 |                75025 |
| 26 |               75025 |               121393 |
| 27 |              121393 |               196418 |
| 28 |              196418 |               317811 |
| 29 |              317811 |               514229 |
| 30 |              514229 |               832040 |
| 31 |              832040 |              1346269 |
| 32 |             1346269 |              2178309 |
| 33 |             2178309 |              3524578 |
| 34 |             3524578 |              5702887 |
| 35 |             5702887 |              9227465 |
| 36 |             9227465 |             14930352 |
| 37 |            14930352 |             24157817 |
| 38 |            24157817 |             39088169 |
| 39 |            39088169 |             63245986 |
| 40 |            63245986 |            102334155 |
| 41 |           102334155 |            165580141 |
| 42 |           165580141 |            267914296 |
| 43 |           267914296 |            433494437 |
| 44 |           433494437 |            701408733 |
| 45 |           701408733 |           1134903170 |
| 46 |          1134903170 |           1836311903 |
| 47 |          1836311903 |           2971215073 |
| 48 |          2971215073 |           4807526976 |
| 49 |          4807526976 |           7778742049 |
| 50 |          7778742049 |          12586269025 |
| 51 |         12586269025 |          20365011074 |
| 52 |         20365011074 |          32951280099 |
| 53 |         32951280099 |          53316291173 |
| 54 |         53316291173 |          86267571272 |
| 55 |         86267571272 |         139583862445 |
| 56 |        139583862445 |         225851433717 |
| 57 |        225851433717 |         365435296162 |
| 58 |        365435296162 |         591286729879 |
| 59 |        591286729879 |         956722026041 |
| 60 |        956722026041 |        1548008755920 |
| 61 |       1548008755920 |        2504730781961 |
| 62 |       2504730781961 |        4052739537881 |
| 63 |       4052739537881 |        6557470319842 |
| 64 |       6557470319842 |       10610209857723 |
| 65 |      10610209857723 |       17167680177565 |
| 66 |      17167680177565 |       27777890035288 |
| 67 |      27777890035288 |       44945570212853 |
| 68 |      44945570212853 |       72723460248141 |
| 69 |      72723460248141 |      117669030460994 |
| 70 |     117669030460994 |      190392490709135 |
| 71 |     190392490709135 |      308061521170129 |
| 72 |     308061521170129 |      498454011879264 |
| 73 |     498454011879264 |      806515533049393 |
| 74 |     806515533049393 |     1304969544928657 |
| 75 |    1304969544928657 |     2111485077978050 |
| 76 |    2111485077978050 |     3416454622906707 |
| 77 |    3416454622906707 |     5527939700884757 |
| 78 |    5527939700884757 |     8944394323791464 |
| 79 |    8944394323791464 |    14472334024676221 |
| 80 |   14472334024676221 |    23416728348467685 |
| 81 |   23416728348467685 |    37889062373143906 |
| 82 |   37889062373143906 |    61305790721611591 |
| 83 |   61305790721611591 |    99194853094755497 |
| 84 |   99194853094755497 |   160500643816367088 |
| 85 |  160500643816367088 |   259695496911122585 |
| 86 |  259695496911122585 |   420196140727489673 |
| 87 |  420196140727489673 |   679891637638612258 |
| 88 |  679891637638612258 |  1100087778366101931 |
| 89 | 1100087778366101931 |  1779979416004714189 |
| 90 | 1779979416004714189 |  2880067194370816120 |
| 91 | 2880067194370816120 |  4660046610375530309 |
| 92 | 4660046610375530309 |  7540113804746346429 |
| 93 | 7540113804746346429 | 12200160415121876738 |
+----+---------------------+----------------------+
93 rows in set (0.10 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

可以生成到93个（长度20了），但是94+都不行了，达到了bigint上限

语法说明：
UNION ALL与UNION DISTINCT

UNION ALL：非递归部分和递归部分用UNION ALL分隔,那么所有的行都会被加入到最后的表中
UNION DISTINCT：非递归部分和递归部分用UNION DISTINCT分隔，重复的行被消除。这对于执行传递闭包的查询非常有用，以避免无限循环。

limit控制递归次数

recursive(第二个select)不能使用的结构：

官网的描述:

The recursive SELECT part must not contain these constructs:（递归查询部分必须不含下列结构）

Aggregate functions such as SUM()，聚合函数例如SUM()
Window functions
GROUP BY
ORDER BY
DISTINCT

限制递归次数/时间：
当出现不符合设置情况的会报错,分为以下几种设置方法:

cte_max_recursion_depth :default 设置为1000,表达递归的层数.可以使用如下语句修改这个值:

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
1
2

当然也可以设置为global,也就是set global cte_max_recursion_depth = 1000000;这样子就对全局的递归都有限制

max_execution_time :设置最近的递归时间

SET max_execution_time = 1000; -- impose one second timeout
1

MAX_EXECUTION_TIME:设置全局的递归时间

官网文档说明如下:

The cte_max_recursion_depth system variable enforces a limit on the
number of recursion levels for CTEs. The server terminates execution
of any CTE that recurses more levels than the value of this variable.

The max_execution_time system variable enforces an execution timeout
for SELECT statements executed within the current session.

The MAX_EXECUTION_TIME optimizer hint enforces a per-query execution
timeout for the SELECT statement in which it appears.

limit:限之最大行的数量

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;
1
2
3
4
5
6
7

报错Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

相关阅读:
nginx简介
 linux快速安装nodejs与pm2
原型链污染攻击也称JavaScript Prototype 污染攻击
 软文撰写的创意思维有哪些，腾轩科技传媒讲解一些技巧
 Win11如何查看用户登录记录？Win11开机显示账户登录信息的方法
 高危安全漏洞风险提示:Oracle7月修复Weblogic Server严重RCE漏洞
 我在上海偶遇数字凤凰#坐标徐汇美罗城
 javaSE___语法基础（关键字、标识符、数据类型、字符、字符串、运算符）
地铁听书系列之“看破不说破，81个为人处事潜规则”8月圆满收尾20220831
C++初阶-模板初阶
原文地址：https://blog.csdn.net/Gan_1314/article/details/125431748