MySQL 8 通用表表达式

通用表表达式简称为CTE（Common Table Expressions）。CTE是命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。

CTE的语法格式如下：

使用WITH语句创建CTE的情况如下：

（1）SELECT、UPDATE、DELETE语句的开头：

（2）在子查询的开头：

（3）紧接SELECT，在包含SELECT声明的语句之前：

下面通过案例来讲述通用表表达式的使用方法。

创建商品表goods，该数据表包含上下级关系的数据，具体字段包含商品编号(id)、商品名称（name）、上级商品的编号(gid)。创建语句如下：

CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('1', '商品', '0');
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('2', '水果', '1');
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('3', '蔬菜', '1');
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('4', '苹果', '2');
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('5', '香蕉', '2');
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('6', '菠菜', '3');
INSERT INTO `boot`.`goods` (`id`, `name`, `gid`) VALUES ('7', '萝卜', '3');

下面开始查询每个商品对应的上级商品名称。这里使用子查询的方式：

SELECT g.*,(select name from goods where id = g.gid) as pname FROM goods g;

接着使用CTE的方式，完成上述功能：

with cte as (
    select * from goods
)
SELECT g.*,(select name from cte where id = g.gid) as pname FROM goods g;

从结果可以看出，CTE是一个可以重复使用的结果集。相比于子查询，CTE的效率会更高，因为非递归的CTE只会查询一次并可以重复使用。

CTE可以引用其他CTE的结果。例如，下面的语句中，cte2就引用了cte1中的结果。

with cte1 as (
    select * from goods
), cte2 as (
    SELECT g.*,cte1.name as pname 
    FROM goods g
    LEFT JOIN cte1 on g.gid = cte1.id
)
select * from cte2;

还有一种特殊的CTE，就是递归CTE，其子查询会引用自身。WITH子句必须以WITH RECURSIVE开头。

CTE递归子查询包括两部分：seed查询和recursive查询，中间由union [all]或union distinct分隔。seed查询会被执行一次，以创建初始数据子集。recursive查询会被重复执行以返回数据子集，直到获得完整结果集。当迭代不会生成任何新行时，递归会停止。可以参看下面的案例：

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

上面的语句会递归显示8行，每行分别显示1~8数字。递归的过程如下：

（1）首先执行SELECT 1得到结果1，即当前n的值为1。

（2）接着执行SELECT N+1 FROM cte WHERE n < 8，因为当前n为1，所以WHERE条件成立，生成新行，SELECT n+1得到结果2，即当前n的值为2。

（3）继续执行SELECT n+1 FROM cte WHERE n < 8，因为当前n为2，所以WHERE条件成立，生成新行，SELECT n+1得到结果3，即当前n的值为3。

（4）一直递归下去。

（5）直到当n为8时，where条件不成立，无法生成新行，递归停止。

下面使用递归CTE来查询每个商品到顶级商品的层次。

with recursive cte as (
    select id,name,cast('0' as char(255)) as path from goods where gid = 0
    union all
    select g.id,g.name,CONCAT(cte.path,',',cte.id)
    from goods g
    join cte on g.gid = cte.id
)
select * from cte;

查询一个指定商品的所有父级商品。

with recursive cte as (
    select id,name,gid from goods where id = 7
    union all
    select g.id,g.name,g.gid
    from goods g
    join cte on cte.gid = g.id
)
select * from cte;

文章来源:MySQL8从入门到精通-7.10