mysql8 公用表表達(dá)式CTE的使用方法實(shí)例分析

更新時(shí)間：2020年02月19日 11:25:44 作者：懷素真

這篇文章主要介紹了mysql8 公用表表達(dá)式CTE的使用方法,結(jié)合實(shí)例形式分析了mysql8 公用表表達(dá)式CTE的基本功能、原理使用方法及相關(guān)操作注意事項(xiàng),需要的朋友可以參考下

本文實(shí)例講述了mysql8 公用表表達(dá)式CTE的使用方法。分享給大家供大家參考，具體如下：

公用表表達(dá)式CTE就是命名的臨時(shí)結(jié)果集，作用范圍是當(dāng)前語(yǔ)句。

說(shuō)白點(diǎn)你可以理解成一個(gè)可以復(fù)用的子查詢(xún)，當(dāng)然跟子查詢(xún)還是有點(diǎn)區(qū)別的，CTE可以引用其他CTE，但子查詢(xún)不能引用其他子查詢(xún)。

一、cte的語(yǔ)法格式：

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

二、哪些地方可以使用with語(yǔ)句創(chuàng)建cte

1、select， update，delete 語(yǔ)句的開(kāi)頭

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

2、在子查詢(xún)的開(kāi)頭或派生表子查詢(xún)的開(kāi)頭

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

3、緊接SELECT，在包含 SELECT聲明的語(yǔ)句之前

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

三、我們先建個(gè)表，準(zhǔn)備點(diǎn)數(shù)據(jù)

CREATE TABLE `menu` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT '' COMMENT '名稱(chēng)',
 `url` varchar(255) DEFAULT '' COMMENT 'url地址',
 `pid` int(11) DEFAULT '0' COMMENT '父級(jí)ID',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入點(diǎn)數(shù)據(jù):

INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('1', '后臺(tái)管理', '/manage', '0');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('2', '用戶(hù)管理', '/manage/user', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('3', '文章管理', '/manage/article', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('4', '添加用戶(hù)', '/manage/user/add', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('5', '用戶(hù)列表', '/manage/user/list', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('6', '添加文章', '/manage/article/add', '3');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('7', '文章列表', '/manage/article/list', '3');

四、非遞歸CTE

這里查詢(xún)每個(gè)菜單對(duì)應(yīng)的直接上級(jí)名稱(chēng)，通過(guò)子查詢(xún)的方式。

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

這里換成用cte完成上面的功能

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

上面的示例并不是很好，只是用來(lái)演示cte的使用。你只需要知道 cte 就是一個(gè)可復(fù)用的結(jié)果集就好了。

相比較某些子查詢(xún)，cte 的效率會(huì)更高，因?yàn)榉沁f歸的 cte 只會(huì)查詢(xún)一次并復(fù)用。

cte 可以引用其他 cte 的結(jié)果，比如下面的語(yǔ)句，cte2 就引用了 cte1 中的結(jié)果。

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;

五、遞歸CTE

遞歸cte是一種特殊的cte，其子查詢(xún)會(huì)引用自身，with子句必須以 with recursive 開(kāi)頭。

cte遞歸子查詢(xún)包括兩部分：seed 查詢(xún) 和 recursive 查詢(xún)，中間由union [all] 或 union distinct 分隔。

seed 查詢(xún)會(huì)被執(zhí)行一次，以創(chuàng)建初始數(shù)據(jù)子集。

recursive 查詢(xún)會(huì)被重復(fù)執(zhí)行以返回?cái)?shù)據(jù)子集，直到獲得完整結(jié)果集。當(dāng)?shù)粫?huì)生成任何新行時(shí)，遞歸會(huì)停止。

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

上面的語(yǔ)句，會(huì)遞歸顯示10行，每行分別顯示1-10數(shù)字。

遞歸的過(guò)程如下：

1、首先執(zhí)行 select 1 得到結(jié)果 1，則當(dāng)前 n 的值為 1。

2、接著執(zhí)行 select n + 1 from cte where n < 10，因?yàn)楫?dāng)前 n 為 1，所以where條件成立，生成新行，select n + 1 得到結(jié)果 2，則當(dāng)前 n 的值為 2。

3、繼續(xù)執(zhí)行 select n + 1 from cte where n < 10，因?yàn)楫?dāng)前 n 為 2，所以where條件成立，生成新行，select n + 1 得到結(jié)果 3，則當(dāng)前 n 的值為 3。

4、一直遞歸下去

5、直到當(dāng) n 為 10 時(shí)，where條件不成立，無(wú)法生成新行，則遞歸停止。

對(duì)于一些有上下級(jí)關(guān)系的數(shù)據(jù)，通過(guò)遞歸cte就可以很好的處理了。

比如我們要查詢(xún)每個(gè)菜單到頂級(jí)菜單的路徑

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;

遞歸的過(guò)程如下：

1、首先查詢(xún)出所有 pid = 0 的菜單數(shù)據(jù)，并設(shè)置path 為 '0'，此時(shí)cte的結(jié)果集為 pid = 0 的所有菜單數(shù)據(jù)。

2、執(zhí)行 menu inner join cte on menu.pid = cte.id ，這時(shí)表 menu 與 cte (步驟1中獲取的結(jié)果集) 進(jìn)行內(nèi)連接，獲取菜單父級(jí)為頂級(jí)菜單的數(shù)據(jù)。

3、繼續(xù)執(zhí)行 menu inner join cte on menu.pid = cte.id，這時(shí)表 menu 與 cte (步驟2中獲取的結(jié)果集) 進(jìn)行內(nèi)連接，獲取菜單父級(jí)的父級(jí)為頂級(jí)菜單的數(shù)據(jù)。

4、一直遞歸下去

5、直到?jīng)]有返回任何行時(shí)，遞歸停止。

查詢(xún)一個(gè)指定菜單所有的父級(jí)菜單

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;