[SQL学习笔记]递归函数recursive

参考文章https://blog.csdn.net/jiey0407/article/details/126773783?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167858576516782425159617%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=167858576516782425159617&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-1-126773783-null-null.142^v73^insert_down2,201^v4^add_ask,239^v2^insert_chatgpt&utm_term=sql%20recursive%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0&spm=1018.2226.3001.4187

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

# 案例1
with recursive d(ymd) as (
    select date_sub(current_date+1,interval dayofmonth(current_date) day)
    union all
    select date_add(ymd,interval 1 day)
    from d ymd
    where ymd <=last_day(current_date)
    )

select * from d;

# 案例2
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 |
+------+--------------+

并且recursive(第二个select)不能使用的结构如下，官网的描述为:

The recursive SELECT part must not contain these constructs:

Aggregate functions such as SUM()、Window functions、GROUP BY、ORDER BY、DISTINCT

限制迭代次数可以用如下几种方法：

1. 在迭代层设置where子句，不满足条件时会停止迭代

2. 在迭代层设置limit

3. 设置会话or全局变量

会话变量：

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

全局变量：

max_execution_time :设置最近的递归时间

SET max_execution_time = 1000; – impose one second timeout