WITH在MYSQL中的用法
WITH 子句（也称为公共表表达式，Common Table Expression，简称 CTE）是 SQL 中一种强大的查询构建工具，它可以显著提高复杂查询的可读性和可维护性。
一、基本语法结构
WITH cte_name AS (SELECT ... -- 定义CTE的查询
)
SELECT ... FROM cte_name; -- 主查询使用CTE
二、CTE 的核心特点
- 临时结果集：CTE 只在当前查询执行期间存在
- 可引用性：定义后可在主查询中多次引用
- 作用域限制：仅在紧随其后的单个语句中有效
三、MySQL 中 CTE 的具体用法
1. 基本 CTE（单表表达式）
WITH sales_summary AS (SELECT product_id, SUM(quantity) AS total_soldFROM ordersGROUP BY product_id
)
SELECT p.product_name, s.total_sold
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;
2. 多 CTE 定义（逗号分隔）
WITH
customer_orders AS (SELECT customer_id, COUNT(*) AS order_countFROM ordersGROUP BY customer_id
),
high_value_customers AS (SELECT customer_idFROM customer_ordersWHERE order_count > 5
)
SELECT c.customer_name
FROM customers c
JOIN high_value_customers h ON c.customer_id = h.customer_id;
3. 递归 CTE（MySQL 8.0+ 支持）
递归 CTE 用于处理层次结构数据：
WITH RECURSIVE org_hierarchy AS (-- 基础查询（锚成员）SELECT id, name, parent_id, 1 AS levelFROM organizationWHERE parent_id IS NULLUNION ALL-- 递归查询（递归成员）SELECT o.id, o.name, o.parent_id, h.level + 1FROM organization oJOIN org_hierarchy h ON o.parent_id = h.id
)
SELECT * FROM org_hierarchy;
四、CTE 的优势
-
提高可读性：
- 将复杂查询分解为逻辑块
- 类似编程中的变量定义
-
避免重复子查询：
-- 不使用CTE（重复子查询） SELECT * FROM (SELECT ... FROM table1) AS t1 JOIN (SELECT ... FROM table1) AS t2...-- 使用CTE（避免重复） WITH t1 AS (SELECT ... FROM table1) SELECT * FROM t1 JOIN t1 AS t2... -
支持递归查询：处理树形/层次结构数据
五、CTE 与临时表的区别
| 特性 | CTE | 临时表 |
|---|---|---|
| 生命周期 | 仅当前语句有效 | 会话结束前有效 |
| 存储 | 不物理存储 | 可能存储在内存或磁盘 |
| 索引 | 不能创建索引 | 可以创建索引 |
| 可见性 | 仅定义它的查询可见 | 同一会话的后续查询可见 |
| 性能 | 优化器可能内联展开 | 需要实际创建和填充 |
六、实际应用场景
1. 复杂报表查询
WITH
monthly_sales AS (...),
product_ranking AS (...)
SELECT ... FROM monthly_sales JOIN product_ranking...
2. 数据清洗管道
WITH
raw_data AS (...),
cleaned_data AS (...),
enriched_data AS (...)
SELECT * FROM enriched_data;
3. 层次结构遍历（组织架构、评论线程等）
WITH RECURSIVE comment_tree AS (...)
SELECT * FROM comment_tree;
七、性能注意事项
-
物化提示：
WITH cte_name AS (SELECT /*+ MATERIALIZE */ ... -- 强制物化 ) -
合并提示：
WITH cte_name AS (SELECT /*+ MERGE */ ... -- 强制合并到主查询 ) -
递归深度控制（MySQL 默认 1000）：
SET @@cte_max_recursion_depth = 2000;
八、版本兼容性
- MySQL 8.0+ 完整支持 CTE 和递归 CTE
- MySQL 5.7 及更早版本不支持 CTE
WITH 子句是现代 SQL 开发中不可或缺的工具，合理使用可以大幅提升查询的清晰度和维护性，特别是在处理多层嵌套或递归数据时。