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 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)