mysql8 Common Table Expression CTE usage example analysis

This article uses an example to describe how to use the MySQL 8 common table expression (CTE). Share with you for your reference, the details are as follows:

Common table expression CTE is a named temporary result set whose scope is the current statement.

To put it simply, you can think of it as a reusable subquery. Of course, it is a little different from a subquery. CTE can reference other CTEs, but subqueries cannot reference other subqueries.

1. The syntax format of cte:

with_clause:
 WITH [RECURSIVE]
  cte_name [(col_name [, col_name] ...)] AS (subquery)
  [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

2. Where can you use the with statement to create CTE

1. The beginning of select, update, and delete statements

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

2. At the beginning of a subquery or a derived table subquery

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

3. Immediately after SELECT, before the statement containing the SELECT statement

INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

3. Let's build a table and prepare some data

CREATE TABLE `menu` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT '' COMMENT 'name',
 `url` varchar(255) DEFAULT '' COMMENT 'url address',
 `pid` int(11) DEFAULT '0' COMMENT 'Parent ID',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert point data:

INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('1', 'Background Management', '/manage', '0');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('2', 'User Management', '/manage/user', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('3', 'Article Management', '/manage/article', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('4', 'Add user', '/manage/user/add', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('5', 'User List', '/manage/user/list', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('6', 'Add article', '/manage/article/add', '3');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('7', 'Article List', '/manage/article/list', '3');

4. Non-recursive CTE

Here, the direct superior name corresponding to each menu is queried through subquery.

select m.*, (select name from menu where id = m.pid) as pname from menu as m;

Here we use cte to complete the above function

with cte as (
 select * from menu
) 
select m.*, (select cte.name from cte where cte.id = m.pid) as pname from menu as m;

The above example is not very good, it is just used to demonstrate the use of CTE. You just need to know that CTE is a reusable result set.

Compared to some subqueries, CTEs are more efficient because non-recursive CTEs are only queried once and reused.

CTE can reference the results of other CTEs. For example, in the following statement, CTE2 references the results in CTE1.

with cte1 as (
 select * from menu
), cte2 as (
 select m.*, cte1.name as pname from menu as m left join cte1 on m.pid = cte1.id 
)
select * from cte2;

5. Recursive CTE

A recursive CTE is a special CTE whose subquery references itself, and the with clause must start with with recursive.

The CTE recursive subquery consists of two parts: a seed query and a recursive query, separated by union [all] or union distinct.

The seed query is executed once to create the initial subset of data.

A recursive query is executed repeatedly to return subsets of data until the full result set is obtained. The recursion stops when the iteration does not generate any new rows.

with recursive cte(n) as (
 select 1
 union all
 select n + 1 from cte where n < 10
)
select * from cte;

The above statement will recursively display 10 lines, each line showing numbers 1-10.

The recursive process is as follows:

1. First, execute select 1 to get result 1, then the current value of n is 1.

2. Then execute select n + 1 from cte where n < 10. Since the current value of n is 1, the where condition is met, a new row is generated, and select n + 1 obtains the result 2. The current value of n is 2.

3. Continue to execute select n + 1 from cte where n < 10. Since the current value of n is 2, the where condition is met, a new row is generated, and select n + 1 obtains the result 3. The current value of n is 3.

4. Keep recursing

5. When n is 10, the where condition is not met and a new row cannot be generated, and the recursion stops.

For some data with hierarchical relationships, recursive CTE can be used to handle them well.

For example, we want to query the path from each menu to the top-level menu

with recursive cte as (
 select id, name, cast('0' as char(255)) as path from menu where pid = 0
 union all
 select menu.id, menu.name, concat(cte.path, ',', cte.id) as path from menu inner join cte on menu.pid = cte.id
)
select * from cte;

The recursive process is as follows:

1. First, query all menu data with pid = 0 and set path to '0'. At this time, the result set of cte is all menu data with pid = 0.

2. Execute menu inner join cte on menu.pid = cte.id. At this time, the menu table is inner joined with cte (the result set obtained in step 1) to obtain the data whose parent is the top-level menu.

3. Continue to execute menu inner join cte on menu.pid = cte.id. At this time, the table menu is inner joined with cte (the result set obtained in step 2) to obtain the data of the top-level menu whose parent is the parent of the menu.

4. Keep recursing

5. The recursion stops when no rows are returned.

Query all parent menus of a specified menu

with recursive cte as (
 select id, name, pid from menu where id = 7
 union all
 select menu.id, menu.name, menu.pid from menu inner join cte on cte.pid = menu.id
)
select * from cte;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.