Teach you how to use MySQL8 recursive method

I wrote an article on MySQL before, which recursively queries the tree structure by means of custom function, and finally supports the syntax of recursive query from MySQL 8.0

CTE

First understand what CTE is under 1, and its full name is Common Table Expressions

WITH
 cte1 AS (SELECT a, b FROM table1),
 cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

cte1 and cte2 are the CTE defined by us and can be referenced in the current query

It can be seen that CTE is a temporary result set, which is similar to the derived table. The difference between the two is not described in detail here. Please refer to the following MySQL development document: https://dev.mysql.com/doc/refman/8. 0/en/with.html # common-table-expressions-recursive-examples

Recursive query

Let's look at the syntax of recursive query first

WITH RECURSIVE cte_name AS
(
  SELECT ...   -- return initial row set
  UNION ALL / UNION DISTINCT
  SELECT ...   -- return additional row sets
)
SELECT * FROM cte;

Define an CTE, the final result set of this CTE is the "tree structure obtained by recursion" we want, and RECURSIVE means that the current CTE is recursive The first SELECT is "initial result set" The second SELECT is the recursive part, which uses the "initial result set/result set returned from the last recursion" to query and get the "new result set" The query ends until the recursive partial result set returns to null Finally, UNION ALL will combine all the result sets in the above steps (UNION DISTINCT will deduplicate), and then pass SELECT * FROM cte; Get all the result sets

The recursive section cannot include:

Aggregate functions such as SUM () GROUP BY ORDER BY LIMIT DISTINCT

The above explanation may be a bit abstract, so take your time to understand it through examples

WITH RECURSIVE cte (n) AS --  Defined here n The column name equivalent to the result set, which can also be defined in the following query 
(
 SELECT 1
 UNION ALL
 SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;


-- result
+------+
| n  |
+------+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+------+

The initial result set is n = 1 At this time, look at the recursive part. The result set of CTE for the first time is n = 1, and the condition found does not satisfy n < 5. Return n + 1 The second time the recursive part is executed, the CTE result set is n = 2, recursive... until the condition is not satisfied Finally merge the result set

EXAMPLE

Finally, let's look at an example of tree structure

CREATE TABLE `c_tree` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `cname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `parent_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mysql> select * from c_tree;
+----+---------+-----------+
| id | cname  | parent_id |
+----+---------+-----------+
| 1 | 1    |     0 |
| 2 | 2    |     0 |
| 3 | 3    |     0 |
| 4 | 1-1   |     1 |
| 5 | 1-2   |     1 |
| 6 | 2-1   |     2 |
| 7 | 2-2   |     2 |
| 8 | 3-1   |     3 |
| 9 | 3-1-1  |     8 |
| 10 | 3-1-2  |     8 |
| 11 | 3-1-1-1 |     9 |
| 12 | 3-2   |     3 |
+----+---------+-----------+

mysql> 
WITH RECURSIVE tree_cte as
(
  select * from c_tree where parent_id = 3
  UNION ALL
  select t.* from c_tree t inner join tree_cte tcte on t.parent_id = tcte.id
)
SELECT * FROM tree_cte;
+----+---------+-----------+
| id | cname  | parent_id |
+----+---------+-----------+
| 8 | 3-1   |     3 |
| 12 | 3-2   |     3 |
| 9 | 3-1-1  |     8 |
| 10 | 3-1-2  |     8 |
| 11 | 3-1-1-1 |     9 |
+----+---------+-----------+

Initial result set R0 = select * from c_tree where parent_id = 3 In the recursive part, the first time R0 and c_tree inner join get R1 R2 is obtained by recombining R1 with c_tree inner join ... Merge all result sets R0 +... + Ri

More information

https://dev.mysql.com/doc/refman/8.0/en/with.html