上一篇介绍了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)
参考链接