Shulou(Shulou.com)06/01 Report--

I have written an article that MySQL finally supports the syntax of recursive query through the way of user-defined function, recursive query tree structure, starting from MySQL 8.0.

CTE

First of all, let's learn what is CTE, full name Common Table Expressions.

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

Cte1, cte2 is the CTE defined by us, which can be referenced in the current query

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

Recursive query

Let's first take a look at the syntax of a recursive query.

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, the final result set of this CTE is the "recursive tree structure" we want, RECURSIVE represents the first SELECT of the current CTE is the "initial result set", the second SELECT is the recursive part, and use the "initial result set / the last recursive result set" to query to get the "new result set" until the recursive partial result set is returned to null. At the end of the query, UNION ALL will merge all the result sets in the above steps (UNION DISTINCT will remove duplicates), and then pass SELECT * FROM cte Get all the result sets

The recursive part cannot include:

Aggregate functions such as SUM () GROUP BYORDER BYLIMITDISTINCT

The above explanation may be a little abstract, which can be understood slowly by examples.

WITH RECURSIVE cte (n) AS-the n defined here is equivalent to the column name of the result set, or it can 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 |+------+初始结果集为 n =1这时候看递归部分，第一次执行 CTE结果集即是 n =1，条件发现并不满足 n < 5，返回 n + 1第二次执行递归部分，CTE结果集为 n = 2，递归... 直至条件不满足最后合并结果集 EXAMPLE 最后来看一个树结构的例子 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 +-| 2 | | 7 | 2-2 | 2 | 8 | 3-1 | 3 | 9 | 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 Recursive part The first time R 0 and c_tree inner join get R1R1 and then c_tree inner join get R 2. Merge all result sets R0 +... + Ri

More information

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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.