上一篇介绍了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.

例如:

  1. mysql> WITH RECURSIVE cte (n) AS
  2.     -> (
  3.     ->   SELECT 1
  4.     ->   UNION ALL
  5.     ->   SELECT n + 1 FROM cte WHERE n < 5
  6.     -> )
  7.     -> SELECT * FROM cte;
  8. +------+
  9. | n    |
  10. +------+
  11. |    1 |
  12. |    2 |
  13. |    3 |
  14. |    4 |
  15. |    5 |
  16. +------+
  17. 5 rows in set (0.00 sec)

注意字符串长度:

  1. mysql> WITH RECURSIVE cte AS
  2.     -> (
  3.     ->   SELECT 1 AS n, 'abc' AS str
  4.     ->   UNION ALL
  5.     ->   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
  6.     -> )
  7.     -> SELECT * FROM cte;
  8. ERROR 1406 (22001): Data too long for column 'str' at row 1
  9. mysql> WITH RECURSIVE cte AS
  10.     -> (
  11.     ->   SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  12.     ->   UNION ALL
  13.     ->   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
  14.     -> )
  15.     -> SELECT * FROM cte;
  16. +------+--------------+
  17. | n    | str          |
  18. +------+--------------+
  19. |    1 | abc          |
  20. |    2 | abcabc       |
  21. |    3 | abcabcabcabc |
  22. +------+--------------+
  23. 3 rows in set (0.00 sec)


2、CTE Recurive递归中的参数限制

(1)cte_max_recursion_depth 控制调用递归的次数,默认1000次

例如:

  1. 当调用cte为1001次的时候,查询报错
  2. mysql> WITH RECURSIVE cte (n) AS
  3.     -> (
  4.     ->   SELECT 1
  5.     ->   UNION ALL
  6.     ->   SELECT n + 1 FROM cte where n<1001
  7.     -> )
  8.     -> SELECT * FROM cte;
  9. 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.

例如:

  1. 把参数设置为5s,执行超时并报错:
  2. mysql> SET max_execution_time = 5000;   #5s
  3. Query OK, 0 rows affected (0.00 sec)
  4. 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');
  5. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
  6. 把参数设置为50s,执行成功:
  7. SET max_execution_time = 50000;
  8. 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');
  9. 2718 rows in set (21.70 sec)


3、CTE Recurive递归的几个经典示例

(1)斐波纳契数列问题

  1. mysql> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
  2.     -> (
  3.     ->   SELECT 101
  4.     ->   UNION ALL
  5.     ->   SELECT n + 1, next_fib_n, fib_n + next_fib_n
  6.     ->     FROM fibonacci WHERE n < 10
  7.     -> )
  8.     -> SELECT * FROM fibonacci;
  9. +------+-------+------------+
  10. | n    | fib_n | next_fib_n |
  11. +------+-------+------------+
  12. |    1 |     0 |          1 |
  13. |    2 |     1 |          1 |
  14. |    3 |     1 |          2 |
  15. |    4 |     2 |          3 |
  16. |    5 |     3 |          5 |
  17. |    6 |     5 |          8 |
  18. |    7 |     8 |         13 |
  19. |    8 |    13 |         21 |
  20. |    9 |    21 |         34 |
  21. |   10 |    34 |         55 |
  22. +------+-------+------------+
  23. 10 rows in set (0.00 sec)

(2)连续日期问题

  1. mysql> WITH RECURSIVE dates (dateAS
  2. (
  3.   SELECT MIN(dateFROM sales
  4.   UNION ALL
  5.   SELECT date + INTERVAL 1 DAY FROM dates
  6.   WHERE date + INTERVAL 1 DAY <= (SELECT MAX(dateFROM sales)
  7. )
  8. SELECT * FROM dates;
  9. +------------+
  10. | date       |
  11. +------------+
  12. | 2017-01-03 |
  13. | 2017-01-04 |
  14. | 2017-01-05 |
  15. | 2017-01-06 |
  16. | 2017-01-07 |
  17. | 2017-01-08 |
  18. | 2017-01-09 |
  19. | 2017-01-10 |
  20. +------------+
  21. mysql> WITH RECURSIVE dates (dateAS
  22. (
  23.   SELECT MIN(dateFROM sales
  24.   UNION ALL
  25.   SELECT date + INTERVAL 1 DAY FROM dates
  26.   WHERE date + INTERVAL 1 DAY <= (SELECT MAX(dateFROM sales)
  27. )
  28. SELECT dates.date, COALESCE(SUM(price), 0AS sum_price
  29. FROM dates LEFT JOIN sales ON dates.date = sales.date
  30. GROUP BY dates.date
  31. ORDER BY dates.date;
  32. +------------+-----------+
  33. | date       | sum_price |
  34. +------------+-----------+
  35. | 2017-01-03 |    300.00 |
  36. | 2017-01-04 |      0.00 |
  37. | 2017-01-05 |      0.00 |
  38. | 2017-01-06 |     50.00 |
  39. | 2017-01-07 |      0.00 |
  40. | 2017-01-08 |    180.00 |
  41. | 2017-01-09 |      0.00 |
  42. | 2017-01-10 |      5.00 |
  43. +------------+-----------+

(3)分层数据遍历问题

  1. mysql> CREATE TABLE employees (
  2.     ->   id         INT PRIMARY KEY NOT NULL,
  3.     ->   name       VARCHAR(100NOT NULL,
  4.     ->   manager_id INT NULL,
  5.     ->   INDEX (manager_id),
  6.     -> FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (id)
  7.     -> );
  8. Query OK, 0 rows affected (0.44 sec)
  9. mysql> INSERT INTO employees VALUES
  10.     -> (333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
  11.     -> (198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
  12.     -> (692, "Tarek", 333),
  13.     -> (29, "Pedro", 198),
  14.     -> (4610, "Sarah", 29),
  15.     -> (72, "Pierre", 29),
  16.     -> (123, "Adil", 692);
  17. Query OK, 7 rows affected (0.09 sec)
  18. Records: 7  Duplicates: 0  Warnings: 0
  19. mysql> SELECT * FROM employees ORDER BY id;
  20. +------+---------+------------+
  21. | id   | name    | manager_id |
  22. +------+---------+------------+
  23. |   29 | Pedro   |        198 |
  24. |   72 | Pierre  |         29 |
  25. |  123 | Adil    |        692 |
  26. |  198 | John    |        333 |
  27. |  333 | Yasmina |       NULL |
  28. |  692 | Tarek   |        333 |
  29. | 4610 | Sarah   |         29 |
  30. +------+---------+------------+
  31. 7 rows in set (0.00 sec)
  32. mysql> WITH RECURSIVE employee_paths (id, name, path) AS
  33.     -> (
  34.     ->   SELECT id, name, CAST(id AS CHAR(200))
  35.     ->     FROM employees
  36.     ->     WHERE manager_id IS NULL
  37.     ->   UNION ALL
  38.     ->   SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
  39.     ->     FROM employee_paths AS ep JOIN employees AS e
  40.     ->       ON ep.id = e.manager_id
  41.     -> )
  42.     -> SELECT * FROM employee_paths ORDER BY path;
  43. +------+---------+-----------------+
  44. | id   | name    | path            |
  45. +------+---------+-----------------+
  46. |  333 | Yasmina | 333             |
  47. |  198 | John    | 333,198         |
  48. |   29 | Pedro   | 333,198,29      |
  49. | 4610 | Sarah   | 333,198,29,4610 |
  50. |   72 | Pierre  | 333,198,29,72   |
  51. |  692 | Tarek   | 333,692         |
  52. |  123 | Adil    | 333,692,123     |
  53. +------+---------+-----------------+
  54. 7 rows in set (0.00 sec)


参考链接

13.2.13 WITH Syntax (Common Table Expressions)