MySQL 8.0 CTE（WITH）查询详解：简化复杂SQL与递归技巧

基础概念：为何使用 WITH

WITH 语句允许为单个 SELECT 语句定义多个临时结果集，称为公用表表达式（CTE）。其核心价值在于：

• 提升可读性：将复杂的嵌套查询拆解为线性的步骤，逻辑更清晰。
• 减少冗余：避免在同一个主查询中重复编写相同的子查询逻辑。
• 支持递归：通过 RECURSIVE 关键字处理树状或层级数据结构。

普通 CTE：链式数据构建

普通 CTE 可以层层引用，前一个临时表的结果可作为后一个临时表的输入源。这种模式在处理需要逐步转换数据的场景非常有效。

第1步：理解多表依赖链

以下示例展示了四个 CTE 如何依次生成字符串并组合。需要注意的是，cte4 引用了 cte2 和 cte3 的数据，而最终结果基于 cte4 进行聚合。

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;

第2步：观察执行结果

上述查询将产生如下结果，显示了拼接后的最大与最小字符串值：

+----------------------------+----------------------+
| MAX(txt)                   | MIN(txt)             |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)

递归 CTE：核心原理与语法

递归查询是 CTE 最强大的功能之一，适用于处理组织架构图、分类树等具有父子关系的层级数据。其结构分为“初始查询”和“递归查询”两部分，由 UNION ALL 连接。

第1步：最简单的数字递归

我们可以通过一个简单的示例来拆解递归 CTE 的工作流程。定义名为 my_cte 的表达式：

1. 初始部分：SELECT 1 AS n。这确定了临时表的列为 n，初始值为 1。
2. 递归部分：SELECT 1+n FROM my_cte WHERE n<10。系统会重复执行此语句，直到不满足 n<10 的条件为止。每次执行将上一轮的结果加 1 追加到临时表中。
3. 外部查询：SELECT * FROM my_cte。取出所有生成的数据。

第2步：完整递归代码与输出

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;

执行后将生成 1 到 10 的序列：

+----+
| n  |
+----+
|  1 |
|  2 |
| ...
| 10 |
+----+
10 rows in set (0,00 sec)

第3步：递归中的列名定义规范

在定义递归 CTE 时，列名可以有两种定义方式，必须保证初始集与递归集的列数量和类型严格一致，否则报错：

• 方式一（推荐）：在 CTE 头定义列名。 WITH RECURSIVE my_cte(a,b,c) AS (...) ，此时括号内无需再指定别名。
• 方式二：在初始查询中定义别名。 WITH RECURSIVE my_cte AS (SELECT 1 AS a, ...) ，列名由第一行查询结果的别名决定。

实战应用：层级数据递归查询

在实际业务中，最典型的 CTE 应用是处理包含 pid（父级ID）的层级表，例如地区信息、部门架构等。

第1步：准备层级测试数据

假设有一张存储省市区信息的表 tb，其中 pid 为 0 表示顶级节点：

CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
INSERT INTO tb VALUES('002', 0, '浙江省');
INSERT INTO tb VALUES('001', 0, '广东省');
-- ... 省略中间市县插入语句，需包含上级关联记录 ...

第2步：执行递归查询

以下查询旨在找出浙江省（ID='002'）及其所有下辖市区的层级路径：

WITH RECURSIVE cte AS (
    -- 1. 初始集：选中浙江省根节点
    SELECT id, name FROM tb WHERE id='002'
    UNION ALL
    -- 2. 递归集：查找当前节点的所有下级，并拼接路径
    SELECT k.id, CONCAT(c.name,'->',k.name) AS name 
    FROM tb k 
    INNER JOIN cte c ON c.id = k.pid
)
SELECT * FROM cte;

该逻辑的工作原理如下：

• 第一轮：取出 '002' 浙江省，此时结果集中仅一行。
• 第二轮：以 '002' 为基准，查找所有 pid 等于 '002' 的记录（如衢州、杭州等），并将名称拼接为 “浙江省->衢州市”。
• 第三轮及之后：继续以新生成的市级 ID 为基准，查找其下辖区县，直到没有下级节点为止。

总结

MySQL 8.0 的 WITH 语法不仅优化了复杂查询的代码结构，更赋予了数据库处理递归层级关系的能力。对于包含大量临时表或嵌套子查询的业务场景，迁移至 CTE 写法不仅能降低维护成本，也能让 SQL 意图更加明确。

以上就是 MySQL 8.0 的 WITH 查询详情的详细内容，更多关于 MySQL CTE 递归查询的资料请关注本站其它相关文章！