mysql8 公用表表達式CTE的使用方法實例分析

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

這篇文章主要介紹了mysql8 公用表表達式CTE的使用方法,結合實例形式分析了mysql8 公用表表達式CTE的基本功能、原理使用方法及相關操作注意事項,需要的朋友可以參考下

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

公用表表達式CTE就是命名的臨時結果集，作用范圍是當前語句。

說白點你可以理解成一個可以復用的子查詢，當然跟子查詢還是有點區(qū)別的，CTE可以引用其他CTE，但子查詢不能引用其他子查詢。

一、cte的語法格式：

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

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

1、select， update，delete 語句的開頭

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

2、在子查詢的開頭或派生表子查詢的開頭

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

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

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

三、我們先建個表，準備點數據

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

插入點數據:

INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('1', '后臺管理', '/manage', '0');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('2', '用戶管理', '/manage/user', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('3', '文章管理', '/manage/article', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('4', '添加用戶', '/manage/user/add', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('5', '用戶列表', '/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

這里查詢每個菜單對應的直接上級名稱，通過子查詢的方式。

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;

上面的示例并不是很好，只是用來演示cte的使用。你只需要知道 cte 就是一個可復用的結果集就好了。

相比較某些子查詢，cte 的效率會更高，因為非遞歸的 cte 只會查詢一次并復用。

cte 可以引用其他 cte 的結果，比如下面的語句，cte2 就引用了 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;

五、遞歸CTE

遞歸cte是一種特殊的cte，其子查詢會引用自身，with子句必須以 with recursive 開頭。

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

seed 查詢會被執(zhí)行一次，以創(chuàng)建初始數據子集。

recursive 查詢會被重復執(zhí)行以返回數據子集，直到獲得完整結果集。當迭代不會生成任何新行時，遞歸會停止。

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

上面的語句，會遞歸顯示10行，每行分別顯示1-10數字。

遞歸的過程如下：

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

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

3、繼續(xù)執(zhí)行 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;

遞歸的過程如下：

1、首先查詢出所有 pid = 0 的菜單數據，并設置path 為 '0'，此時cte的結果集為 pid = 0 的所有菜單數據。

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

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

4、一直遞歸下去

5、直到沒有返回任何行時，遞歸停止。

查詢一個指定菜單所有的父級菜單

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;