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;
以列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~n：
WITH RECURSIVE cte (n) AS ( SELECt 1 UNIOn ALL SELECT n + 1 FROM cte LIMIT 10 ) SELECt * FROM cte; #可将`LIMIT`改成`WHERe n < 10`
+----+ | n | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+
输出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;
+----+---------+ | n | m | +----+---------+ | 1 | 1 | | 2 | 2 | | 3 | 6 | | 4 | 24 | | 5 | 120 | | 6 | 720 | | 7 | 5040 | | 8 | 40320 | | 9 | 362880 | | 10 | 3628800 | +----+---------+
输出前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;
+----+-----+ | n | sum | +----+-----+ | 1 | 1 | | 2 | 3 | | 3 | 6 | | 4 | 10 | | 5 | 15 | | 6 | 21 | | 7 | 28 | | 8 | 36 | | 9 | 45 | | 10 | 55 | +----+-----+
简单递归用法：
首先我们引出一个问题： 什么叫做递归?
递归：给定函数初始条件,然后反复调用自身直到终止条件.
例子1：递归得到依次递增的序列：
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 | +---+
官方文档中对于这个写法的解释:
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;
结果：
+---+-----+ | n | str | +---+-----+ | 1 | abc | | 2 | abc | | 3 | abc | +---+-----+
或者报错：‘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;
+---+--------------+ | n | str | +---+--------------+ | 1 | abc | | 2 | abcabc | | 3 | abcabcabcabc | +---+--------------+
当然，如果上一行的值有多个，我们还可以对多个值进行重新组合得到我们想要的结果，比如下面这个例子。
例子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;
+----+---------------------+---------------------+ | 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)
试了下最多可以生成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;
+----+---------------------+----------------------+ | 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)
可以生成到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
当然也可以设置为global,也就是set global cte_max_recursion_depth = 1000000;这样子就对全局的递归都有限制
max_execution_time :设置最近的递归时间
SET max_execution_time = 1000; -- impose one second timeout
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;
报错Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
