上一篇介绍了CTE的基本用法，参考MySQL 8.0新特性--CTE(一)，本篇再来介绍一下CTE Recurive递归。
网站建设哪家好，找成都创新互联！专注于网页设计、网站建设、微信开发、成都微信小程序、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了宜良免费建站欢迎大家使用！
1、什么是CTE Recurive?
A recursive common table expression is one having a subquery that refers to its own name.
个人理解：在CTE定义中调用先前定义的CTE，并且在查询的时候，循环调用CTE.
例如：
mysql> 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 | +------+ 5 rows in set (0.00 sec)
注意字符串长度：
mysql> 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;
ERROR 1406 (22001): Data too long for column 'str' at row 1
mysql> 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 rows in set (0.00 sec)2、CTE Recurive递归中的参数限制
(1)cte_max_recursion_depth 控制调用递归的次数，默认1000次
例如：
当调用cte为1001次的时候，查询报错 mysql> WITH RECURSIVE cte (n) AS -> ( -> SELECT 1 -> UNION ALL -> SELECT n + 1 FROM cte where n<1001 -> ) -> SELECT * FROM cte; ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
(2)max_execution_time 强制会话超时时间，默认0，表示没有开启此功能，单位ms.
例如：
把参数设置为5s,执行超时并报错： mysql> SET max_execution_time = 5000; #5s Query OK, 0 rows affected (0.00 sec) mysql> select s.* from salaries s where s.emp_no in (select emp_no from employees e where e.first_name='Georgi' union all select emp_no from employees e where e.hire_date='1992-12-18'); ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded 把参数设置为50s,执行成功： SET max_execution_time = 50000; mysql> select s.* from salaries s where s.emp_no in (select emp_no from employees e where e.first_name='Georgi' union all select emp_no from employees e where e.hire_date='1992-12-18'); 2718 rows in set (21.70 sec)
3、CTE Recurive递归的几个经典示例
(1)斐波纳契数列问题
mysql> 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 < 10 -> ) -> 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 | +------+-------+------------+ 10 rows in set (0.00 sec)
(2)连续日期问题
mysql> WITH RECURSIVE dates (date) AS ( SELECT MIN(date) FROM sales UNION ALL SELECT date + INTERVAL 1 DAY FROM dates WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales) ) SELECT * FROM dates; +------------+ | date | +------------+ | 2017-01-03 | | 2017-01-04 | | 2017-01-05 | | 2017-01-06 | | 2017-01-07 | | 2017-01-08 | | 2017-01-09 | | 2017-01-10 | +------------+ mysql> WITH RECURSIVE dates (date) AS ( SELECT MIN(date) FROM sales UNION ALL SELECT date + INTERVAL 1 DAY FROM dates WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales) ) SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price FROM dates LEFT JOIN sales ON dates.date = sales.date GROUP BY dates.date ORDER BY dates.date; +------------+-----------+ | date | sum_price | +------------+-----------+ | 2017-01-03 | 300.00 | | 2017-01-04 | 0.00 | | 2017-01-05 | 0.00 | | 2017-01-06 | 50.00 | | 2017-01-07 | 0.00 | | 2017-01-08 | 180.00 | | 2017-01-09 | 0.00 | | 2017-01-10 | 5.00 | +------------+-----------+
(3)分层数据遍历问题
mysql> CREATE TABLE employees ( -> id INT PRIMARY KEY NOT NULL, -> name VARCHAR(100) NOT NULL, -> manager_id INT NULL, -> INDEX (manager_id), -> FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (id) -> ); Query OK, 0 rows affected (0.44 sec) mysql> INSERT INTO employees VALUES -> (333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL) -> (198, "John", 333), # John has ID 198 and reports to 333 (Yasmina) -> (692, "Tarek", 333), -> (29, "Pedro", 198), -> (4610, "Sarah", 29), -> (72, "Pierre", 29), -> (123, "Adil", 692); Query OK, 7 rows affected (0.09 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employees ORDER BY id; +------+---------+------------+ | id | name | manager_id | +------+---------+------------+ | 29 | Pedro | 198 | | 72 | Pierre | 29 | | 123 | Adil | 692 | | 198 | John | 333 | | 333 | Yasmina | NULL | | 692 | Tarek | 333 | | 4610 | Sarah | 29 | +------+---------+------------+ 7 rows in set (0.00 sec) mysql> WITH RECURSIVE employee_paths (id, name, path) AS -> ( -> SELECT id, name, CAST(id AS CHAR(200)) -> FROM employees -> WHERE manager_id IS NULL -> UNION ALL -> SELECT e.id, e.name, CONCAT(ep.path, ',', e.id) -> FROM employee_paths AS ep JOIN employees AS e -> ON ep.id = e.manager_id -> ) -> SELECT * FROM employee_paths ORDER BY path; +------+---------+-----------------+ | id | name | path | +------+---------+-----------------+ | 333 | Yasmina | 333 | | 198 | John | 333,198 | | 29 | Pedro | 333,198,29 | | 4610 | Sarah | 333,198,29,4610 | | 72 | Pierre | 333,198,29,72 | | 692 | Tarek | 333,692 | | 123 | Adil | 333,692,123 | +------+---------+-----------------+ 7 rows in set (0.00 sec)
参考链接
13.2.13 WITH Syntax (Common Table Expressions)