Teach you how to use MySQL8 recursive method

I have previously written an article about recursive querying tree structures in MySQL using custom functions. Since MySQL 8.0, recursive query syntax has finally been supported.

CTE

First, let's understand what CTE is. The 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, cte2 are the CTEs we defined, which can be referenced in the current query

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

Recursive query

Let's first look at the syntax of recursive queries.

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

• Define a CTE whose final result set is the "recursive tree structure" we want. RECURSIVE means that the current CTE is recursive.
• The first SELECT is the "initial result set"
• The second SELECT is the recursive part, which uses the "initial result set/result set returned by the last recursion" to query and obtain the "new result set"
• The query ends when the recursive result set returns null.
• Finally, UNION ALL will combine all the result sets in the above steps (UNION DISTINCT will remove duplicates), and then get all the result sets through SELECT * FROM cte;

The recursive part cannot include:

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

The above explanation may be a bit abstract, so let’s slowly understand it through examples.

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


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

• The initial result set is n = 1
• Now let's look at the recursive part. The first time the CTE is executed, the result set is n = 1. The condition is found that n < 5 is not satisfied, so n + 1 is returned.
• The recursive part is executed for the second time, and the CTE result set is n = 2. Recursion... until the condition is not met
• Finally merge the result set

EXAMPLE

Finally, let's look at an example of a 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 inner join of R0 and c_tree is used to get R1
• R1 is then inner joined with c_tree to get R2
• ...
• Merge all result sets R0 + ... + Ri

More information

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

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.