New feature of MySQL 8.0-- CTE Recurive (2)

The previous article introduced the basic usage of CTE, refer to the new feature of MySQL 8.0-CTE (1), and this article will introduce CTE Recurive recursion.

1. What is CTE Recurive?

A recursive common table expression is one having a subquery that refers to its own name.

Personal understanding: call the previously defined CTE in the CTE definition, and call CTE.

For example:

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)

Note the length of the string:

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. Parameter restrictions in CTE Recurive recursion

(1) cte_max_recursion_depth controls the number of recursive calls. Default is 1000.

For example:

When calling cte 1001 times, query error mysql > WITH RECURSIVE cte (n) AS-> (- > SELECT 1-> UNION ALL-> SELECT n + 1 FROM cte where n)-> 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 forces session timeout. Default is 0, which means this feature is not enabled (in ms.)

For example:

Set the parameter to 5s, execute the timeout and report an error: 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. Firstborn name names in Georgia '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 set the parameter to 50s, and successfully executed: SET max_execution_time = 50000X MySQL > select s.* from salaries s where s.emp_no in (select emp_no from employees e where. Firstborn nameplate Georgia 'union all select emp_no from employees e where e.hire_date='1992-12-18'); 2718 rows in set (21.70 sec)

3. Some classic examples of CTE Recurive recursion

(1) Fibonacci sequence problem

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) continuous date problem

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 WITH RECURSIVE dates (date) AS (SELECT MIN (date) FROM sales UNION ALL SELECT date + INTERVAL 1 DAY FROM dates WHERE date + INTERVAL 1 DAY 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 | 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)-> 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 | 333198 | 29 | Pedro | 333, 198,529 | 4610 | Sarah | | 333 rows in set | 72 | Pierre | 333 rows in set | 29410 | | 692 | Tarek | 333692 | 123 | Adil | 333692123 | +-+ 7 rows in set (0.00 sec) |

Reference link

13.2.13 WITH Syntax (Common Table Expressions)