MySQL 8.0新特性--CTE Recurive(二)

上一篇介绍了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 1mysql> 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;   #5sQuery 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_priceFROM dates LEFT JOIN sales ON dates.date = sales.dateGROUP BY dates.dateORDER 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: 0mysql> 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)