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

This paper gives an example of how to use the mysql8 common table expression CTE. Share with you for your reference, the details are as follows:

The common table expression CTE is a named temporary result set that is scoped to the current statement.

To put it bluntly, you can understand it as a reusable subquery. Of course, it is a little different from a subquery. CTE can refer to other CTE, but a subquery cannot refer to 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 I use the with statement to create a cte

1. Select, the beginning of the update,delete statement

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

2. At the beginning of a subquery or at the beginning of 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 declaration

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

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 'DEFAULT' 'COMMENT' url address', `pid`int (11) DEFAULT'0' COMMENT 'parent ID', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insertion point data:

INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('1Qing,' background management','/ manage','0'); INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('2percent,' user management','/ manage/user','1'); INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('3VALUES,' article management','/ manage/article','1') INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('44th,' add users','/ manage/user/add','2'); INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('5percent,' user list','/ manage/user/list','2'); INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('6VALUES,' add articles','/ manage/article/add','3') INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('74th,' article list','/ manage/article/list','3')

4. Non-recursive CTE

Here query each menu corresponding to the direct superior name, through the way of subquery.

Select M.E., (select name from menu where id = m.pid) as pname from menu as m

Here, use cte to complete the above functions.

With cte as (select * from menu) select M.E., (select cte.name from cte where cte.id = m.pid) as pname from menu as m

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

Cte is more efficient than some subqueries because non-recursive cte is queried only once and reused.

Cte can refer to the results of other cte, such as the following statement, cte2 refers to the results in cte1.

With cte1 as (select * from menu), cte2 as (select M.E., 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 subqueries refer to themselves, and the with clause must start with with recursive.

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

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

The recursive query is repeated to return a subset of the data until the complete result set is obtained. 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; 上面的语句，会递归显示10行，每行分别显示1-10数字。 递归的过程如下： 1、首先执行 select 1 得到结果 1， 则当前 n 的值为 1。 2、接着执行 select n + 1 from cte where n < 10，因为当前 n 为 1，所以where条件成立，生成新行，select n + 1 得到结果 2，则当前 n 的值为 2。 3、继续执行 select n + 1 from cte where n < 10，因为当前 n 为 2，所以where条件成立，生成新行，select n + 1 得到结果 3，则当前 n 的值为 3。 4、一直递归下去 5、直到当 n 为 10 时，where条件不成立，无法生成新行，则递归停止。 对于一些有上下级关系的数据，通过递归cte就可以很好的处理了。 比如我们要查询每个菜单到顶级菜单的路径 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 process of recursion is as follows:

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

2. Execute menu inner join cte on menu.pid = cte.id, when the table menu is connected internally 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, when the table menu is connected internally with cte (the result set obtained in step 2) to get the data of the top-level menu whose parent is the menu parent.

4. Go on recursively

5. Recursion stops until 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

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.