WITH在MYSQL中的用法_mysql with

WITH 子句（也称为公共表表达式，Common Table Expression，简称 CTE）是 SQL 中一种强大的查询构建工具，它可以显著提高复杂查询的可读性和可维护性。

一、基本语法结构

WITH cte_name AS ( SELECT ... -- 定义CTE的查询)SELECT ... FROM cte_name; -- 主查询使用CTE

二、CTE 的核心特点

1. 临时结果集：CTE 只在当前查询执行期间存在
2. 可引用性：定义后可在主查询中多次引用
3. 作用域限制：仅在紧随其后的单个语句中有效

三、MySQL 中 CTE 的具体用法

1. 基本 CTE（单表表达式）

WITH sales_summary AS ( SELECT product_id, SUM(quantity) AS total_sold FROM orders GROUP BY product_id)SELECT p.product_name, s.total_soldFROM products pJOIN sales_summary s ON p.product_id = s.product_id;

2. 多 CTE 定义（逗号分隔）

WITH customer_orders AS ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id),high_value_customers AS ( SELECT customer_id FROM customer_orders WHERE order_count > 5)SELECT c.customer_nameFROM customers cJOIN 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 level FROM organization WHERE parent_id IS NULL UNION ALL -- 递归查询（递归成员） SELECT o.id, o.name, o.parent_id, h.level + 1 FROM organization o JOIN org_hierarchy h ON o.parent_id = h.id)SELECT * FROM org_hierarchy;

四、CTE 的优势

1. 提高可读性：

   • 将复杂查询分解为逻辑块
   • 类似编程中的变量定义

2. 避免重复子查询：

   -- 不使用CTE（重复子查询）SELECT * FROM (SELECT ... FROM table1) AS t1JOIN (SELECT ... FROM table1) AS t2...-- 使用CTE（避免重复）WITH t1 AS (SELECT ... FROM table1)SELECT * FROM t1 JOIN t1 AS t2...

3. 支持递归查询：处理树形/层次结构数据

五、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;

七、性能注意事项

1. 物化提示：

   WITH cte_name AS ( SELECT /*+ MATERIALIZE */ ... -- 强制物化)

2. 合并提示：

   WITH cte_name AS ( SELECT /*+ MERGE */ ... -- 强制合并到主查询)

3. 递归深度控制（MySQL 默认 1000）：

   SET @@cte_max_recursion_depth = 2000;

八、版本兼容性

• MySQL 8.0+ 完整支持 CTE 和递归 CTE
• MySQL 5.7 及更早版本不支持 CTE

WITH 子句是现代 SQL 开发中不可或缺的工具，合理使用可以大幅提升查询的清晰度和维护性，特别是在处理多层嵌套或递归数据时。