MySQL 从 8.0 开始才支持 WITH 语法(Common Table Expressions, CTE)。
支持递归公用表表达式！
本文重点以文件夹父节点递归查找为例简单介绍 CTE 递归用法，详情参考第4节。
1.公用表达式(CTE) WITH用法简单介绍
要指定公用表表达式，请使用具有一个或多个逗号分隔子条款的 WITH 子句。 每个子条款都提供一个子查询，用于生成结果集，并将名称与子查询相关联。
以下示例在WITH子句中定义名为cte1和cte2 CTE，并在WITH子句cte2的顶级SELECT中引用它们：
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
- 在包含 WITH 子句的语句中，可以引用每个 CTE 名称以访问相应的 CTE 结果集。
- CTE 名称可以在其他 CTE 中引用，从而可以基于其他 CTE 定义 CTE。
- CTE 可以引用自身来定义递归 CTE。递归CTE的常见应用包括分层或树状结构数据的序列生成和遍历。
公用表表达式是DML语句语法的可选部分，它们使用WITH子句定义：
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
例如：
WITH folders AS
(
SELECT *
FROM folder
)
SELECT * FROM folders;
# 还可以指定字段
WITH folders(folder_id, folder_name) AS
(
SELECT folder_id, folder_name
FROM folder
)
1.1 CTE 列名的确定
- 如果带括号的名称列表遵循CTE名称，则这些名称是列名称：列表中的名称数必须与结果集中的列数相同。
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;
- 否则，列名称来自 AS (subquery) 部分中第一个 SELECT 的选择列表。
WITH cte AS
(
SELECT 1 AS col1, 2 AS col2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;
1.2 允许的使用场景
- 在 SELECT\UPDATE\DELETE 语句的开头。
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
- 在子查询的开头（包括派生表子查询）。
SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...
- 对于包含SELECT语句的语句，紧接在SELECT之前：
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
1.3 注意事项
- 在同一级别只允许一个WITH子句。
要使语句合法，请使用单个WITH子句，用逗号分隔子句：
WITH cte1 AS (...), cte2 AS (...) SELECT ...
- 如果语句出现在不同级别，则语句可以包含多个 WITH 子句。
WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;
- WITH 子句可以定义一个或多个公用表表达式，但每个 CTE 名称对于该子句必须是唯一的。
WITH cte1 AS (...), cte2 AS (...) SELECT ...
CTE 可以指自己或其他 CTE。
- 1.自引用 CTE 是递归的。
2.CTE 可以引用先前在同一个 WITH 子句中定义的 CTE，但不能引用后面定义的 CTE。
此约束排除了相互递归的 CTE，假设有cte1引用cte2，并且cte2引用cte1，由于其中一个引用必须是稍后定义的CTE，所以这是不允许的。
3.一个查询块中的 CTE 可以引用在更外层的查询块中定义的 CTE，但不能引用在更内层的查询块中定义的CTE。
与派生表一样，CTE 在 MySQL 8.0.14 之前不能包含外部引用。 这是在 MySQL 8.0.14 中解除的 MySQL 限制，而不是 SQL 标准的限制。
2.递归公用表达式WITH RECURSIVE用法介绍
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
2.1 递归 CTE 的结构
- 1.如果 WITH 子句中的任何 CTE 引用自身，则 WITH 子句必须以
WITH RECURSIVE开头。
如果您忘记了递归CTE的RECURSIVE ，则可能会出现此错误：
ERROR 1146 (42S02): Table 'cte_name' doesn't exist
2.递归 CTE 子查询有两部分，由
UNION [ALL]或UNION DISTINCT分隔。第一个 SELECT 为 CTE 生成一行或多行，而不引用 CTE 名称。
第二个 SELECT 通过引用其 FROM 子句中的 CTE 名称来生成其他行和递归。当此部分不产生新行时，递归结束。
因此，递归 CTE 由非递归 SELECT 部分和递归 SELECT 部分组成（每个 SELECT 部分本身可以是多个 SELECT 语句的并集）。3.CTE 结果列的类型仅从非递归 SELECT 部分的列类型推断，并且列都可以为空。对于类型确定，将忽略递归 SELECT 部分。
- 4.如果非递归和递归部分由
UNION DISTINCT分隔，则删除重复的行。这对于执行传递闭包的查询很有用，可以避免无限循环。 - 5.递归部分的每次迭代仅对前一次迭代产生的行进行操作。
如果递归部分具有多个查询块，则以未指定的顺序调度每个查询块的迭代，并且每个查询块对由其先前迭代或自上一次迭代结束以来的其他查询块产生的行进行操作。
2.2 递归CTE子查询的一些语法约束
递归SELECT部分不得包含以下结构：
- 1.SUM()等聚合函数
- 2.窗口函数
- 3.GROUP BY
- 4.ORDER BY
- 5.LIMIT
- 6.DISTINCT
此约束不适用于递归 CTE 的非递归 SELECT 部分。
禁止DISTINCT仅适用于UNION成员;UNION DISTINCT是允许的。递归 SELECT 部分必须仅引用 CTE 一次且仅在其 FROM 子句中引用，而不是在任何子查询中引用。
它可以引用 CTE 以外的表，并将它们与 CTE 连接起来。如果在这样的连接中使用，则 CTE 不能位于
LEFT JOIN的右侧。
这些约束来自 SQL 标准，此外 MySQL 还特定排除了ORDER BY,LIMIT和DISTINCT。
对于递归 CTE，递归 SELECT 部分的 EXPLAIN 输出行在 Extra 列中显示 Recursive。
EXPLAIN 显示的成本估算表示每次迭代的成本，这可能与总成本有很大差异。优化程序无法预测迭代次数，因为它无法预测何时 WHERE 子句将变为false。
CTE 实际成本也可能受结果集大小的影响。产生许多行的 CTE 可能需要一个足够大的内部临时表，以便从内存转换为磁盘格式，并且可能会受到性能损失。如果是这样，增加允许的内存临时表大小可以提高性能，详情可参见官方文档第8.4.4节 MySQL中的内部临时表使用。
2.3 限制公用表表达式递归
对于递归CTE来说，递归SELECT部分包括终止递归的条件是很重要的。 作为防止失控递归CTE的开发技术，可以通过限制执行时间来强制终止：
cte_max_recursion_depth系统变量强制限制CTE的递归级别数。
服务器终止执行任何CTE，该CTE递归的级别高于此变量的值。max_execution_time系统变量强制执行当前会话中执行的SELECT语句的执行超时。MAX_EXECUTION_TIME优化程序提示对其出现的SELECT语句强制执行每查询执行超时。
此处不再赘述。。。
3. 实战-递归查询父级节点列表
实际开发中经常会遇到查询所有父级节点的需求，在MySQL 8.0以前一般都是通过(Java)程序处理，现在有了 CTE RECURSIVE 支持，可以方便的使用 sql 查询取出了。
例如：查询 id 为 10 的文件夹的所有父文件夹（包含文件夹本身）
3.1 创建相关测试表和数据
CREATE DATABASE `dev` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `dev`;
CREATE TABLE `dev`.`folder` (
`folder_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`father_id` BIGINT DEFAULT 0 COMMENT '父id',
`folder_name` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '文件夹id',
PRIMARY KEY (`folder_id`),
INDEX `idx_father_id` (`father_id`)
);
# 插入测试数据
TRUNCATE TABLE `dev`.`folder`;
INSERT INTO `dev`.`folder` (`folder_name`) VALUES ('文件夹1');
INSERT INTO `dev`.`folder` (`folder_name`) VALUES ('文件夹2');
INSERT INTO `dev`.`folder` (`folder_name`) VALUES ('文件夹3');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (1, '文件夹1_1');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (1, '文件夹1_2');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (1, '文件夹1_3');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (4, '文件夹1_1_1');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (4, '文件夹1_1_2');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (4, '文件夹1_1_3');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (7, '文件夹1_1_1_1');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (7, '文件夹1_1_1_2');
INSERT INTO `dev`.`folder` (`father_id`, `folder_name`) VALUES (7, '文件夹1_1_1_3');
3.2 CTE sql 编写
WITH RECURSIVE folder_fathers (folder_id, father_id, folder_name) AS
(
SELECT f1.folder_id, f1.father_id, f1.folder_name
FROM folder f1
WHERE f1.folder_id='10'
UNION ALL
SELECT f2.folder_id, f2.father_id, f2.folder_name
FROM folder AS f2
JOIN folder_fathers ff
ON f2.folder_id=ff.father_id
where ff.father_id > 0
)
SELECT * FROM folder_fathers;
查询结果如下：
folder_id father_id folder_name
--------------------------------------
10 7 文件夹1_1_1_1
7 4 文件夹1_1_1
4 1 文件夹1_1
1 0 文件夹1
4.公用表表达式(CTE)与派生表的比较
公用表表达式（CTE）在某些方面类似于派生表：
- 两个结构都被命名。
- 这两种结构都存在于单个语句的范围内。
由于这些相似性，CTE和派生表通常可以互换使用。 作为一个简单的例子，下述两种写法是等价的：
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;
但是，CTE比派生表有一些优势：
- 派生表只能在查询中一次引用，CTE 可以多次引用。要使用派生表结果的多个实例，必须派生多次。
- CTE 可以是自引用（递归）。
- 一个 CTE 可以引用另一个。
- 当 CTE 的定义出现在语句的开头而不是嵌入其中时，CTE 可能更容易阅读。
CTE 与使用CREATE [TEMPORARY] TABLE创建的表类似，但不需要显式定义或删除。对于CTE，您无需创建表的权限。
评论区