mysql with（超详细）

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战(已更新的所有项目都能学习) / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

• 新开坑项目:《Spring AI 项目实战》 正在持续爆肝中，基于 Spring AI + Spring Boot 3.x + JDK 21...， 点击查看 ;
• 《从零手撸：仿小红书（微服务架构）》 已完结，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;演示链接： http://116.62.199.48:7070 ;
• 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习 ，欢迎点击围观

前言

在数据库开发领域，MySQL 作为广泛应用的关系型数据库管理系统，其查询优化与复杂逻辑实现一直是开发者关注的核心。而 MySQL WITH 子句（即 Common Table Expressions，简称 CTE）的引入，为解决多层嵌套查询、递归查询等问题提供了简洁高效的方案。本文将从基础语法到实战案例，深入浅出地解析 MySQL WITH 的使用场景、优势及注意事项，帮助编程初学者和中级开发者快速掌握这一工具。

一、MySQL WITH 子句基础语法

1.1 什么是 WITH 子句？

MySQL WITH 子句允许开发者在单个查询中定义一个临时命名的结果集（即 CTE），并在此基础上进行多次引用。它类似于“临时表”，但生命周期仅限于当前查询，无需显式创建或删除，语法更简洁。

基础语法结构：

WITH cte_name AS (  
    SELECT ...  -- 定义临时结果集的查询  
)  
SELECT ...  
FROM cte_name  
JOIN other_tables ...;  

示例：计算订单总金额

假设有一个订单表 orders，包含 order_id 和 amount 字段：

-- 直接查询总金额  
SELECT SUM(amount) AS total FROM orders;  

-- 使用 WITH 子句封装中间结果  
WITH total_amount AS (  
    SELECT SUM(amount) AS total FROM orders  
)  
SELECT * FROM total_amount;  

通过 WITH 子句，可以将中间计算结果命名为 total_amount，后续查询直接引用，代码逻辑更清晰。

1.2 WITH 子句的临时性与作用域

CTE 的结果集仅在定义它的查询中有效，不会持久化存储。例如：

WITH cte AS (SELECT 1 AS value)  
SELECT * FROM cte;  -- 正常执行  

SELECT * FROM cte;  -- 报错：cte 不存在  

这种特性避免了临时表的资源占用问题，适合需要多次引用但无需长期保存的场景。

二、WITH 子句在递归查询中的应用

2.1 递归查询的典型场景

递归查询常用于处理层级数据，例如：

• 用户权限的树形结构（如部门层级）
• 商品分类的父子关系
• 社交网络中的好友关系链

MySQL WITH 子句支持 递归查询，通过 UNION ALL 将非递归部分与递归部分结合。

语法结构：

WITH RECURSIVE cte_name AS (  
    non_recursive_query  -- 初始查询  
    UNION ALL  
    recursive_query      -- 递归引用 CTE 自身  
)  
SELECT * FROM cte_name;  

案例：查询用户部门层级

假设有一个 employees 表，字段包括 employee_id, name, manager_id（直属上级的 ID）：

-- 非递归部分：初始查询（根节点）  
SELECT employee_id, name, manager_id, 1 AS level  
FROM employees  
WHERE manager_id IS NULL;  

-- 递归部分：引用 CTE 自身  
UNION ALL  
SELECT e.employee_id, e.name, e.manager_id, c.level + 1  
FROM employees e  
JOIN cte_name c ON e.manager_id = c.employee_id;  

完整查询需用 WITH RECURSIVE 包裹：

WITH RECURSIVE dept_hierarchy AS (  
    SELECT employee_id, name, manager_id, 1 AS level  
    FROM employees  
    WHERE manager_id IS NULL  
    UNION ALL  
    SELECT e.employee_id, e.name, e.manager_id, dh.level + 1  
    FROM employees e  
    JOIN dept_hierarchy dh ON e.manager_id = dh.employee_id  
)  
SELECT * FROM dept_hierarchy;  

此查询将输出所有员工及其层级深度，例如：
| employee_id | name | manager_id | level |
|-------------|--------|------------|-------|
| 1 | Alice | NULL | 1 |
| 2 | Bob | 1 | 2 |
| 3 | Charlie| 2 | 3 |

2.2 递归查询的比喻理解

可以将递归查询想象为“剥洋葱”：

• 非递归部分 是洋葱的最外层（根节点）。
• 递归部分 是每次剥开一层后，继续寻找下一层的关系。
• 直到无法继续剥开（无更深层节点），递归终止。

三、与临时表的对比及性能优化

3.1 WITH 子句 vs 临时表

示例对比：计算月度销售总和

WITH 子句：

WITH monthly_sales AS (  
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,  
           SUM(amount) AS total  
    FROM orders  
    GROUP BY month  
)  
SELECT * FROM monthly_sales;  

临时表：

CREATE TEMPORARY TABLE temp_monthly_sales AS  
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,  
       SUM(amount) AS total  
FROM orders  
GROUP BY month;  

SELECT * FROM temp_monthly_sales;  
DROP TEMPORARY TABLE temp_monthly_sales;  

3.2 性能优化技巧

• 避免重复计算：若多个子查询需要同一中间结果，用 CTE 替代重复的 SELECT 语句。
• 合理使用索引：在 CTE 的底层表上添加索引，可显著提升递归查询性能。
• 限制递归深度：通过 MAXRECURSION（MySQL 8.0+ 支持）避免无限循环。

四、实战案例：电商订单分析

4.1 案例背景

假设某电商平台需要分析用户购买行为，需求包括：

1. 统计用户近三个月的订单数量及总金额。
2. 根据订单金额划分用户等级（如普通、VIP、超级VIP）。

4.2 实现步骤

步骤1：定义时间范围

WITH RECURSIVE months AS (  
    SELECT DATE(NOW() - INTERVAL 2 MONTH) AS start_date,  
           DATE(NOW()) AS end_date  
)  
SELECT * FROM months;  

步骤2：计算用户订单数据

WITH user_orders AS (  
    SELECT  
        user_id,  
        COUNT(order_id) AS order_count,  
        SUM(amount) AS total_amount  
    FROM orders  
    WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31'  
    GROUP BY user_id  
)  
SELECT * FROM user_orders;  

步骤3：根据金额划分等级

WITH user_orders AS (...),  
     user_rank AS (  
        SELECT  
            user_id,  
            CASE  
                WHEN total_amount >= 10000 THEN '超级VIP'  
                WHEN total_amount >= 5000 THEN 'VIP'  
                ELSE '普通用户'  
            END AS user_level  
        FROM user_orders  
    )  
SELECT * FROM user_rank;  

4.3 完整查询整合

通过多层 CTE 串联逻辑：

WITH months AS (  
    SELECT DATE(NOW() - INTERVAL 2 MONTH) AS start_date,  
           DATE(NOW()) AS end_date  
),  
user_orders AS (  
    SELECT  
        user_id,  
        COUNT(order_id) AS order_count,  
        SUM(amount) AS total_amount  
    FROM orders  
    WHERE order_date BETWEEN (SELECT start_date FROM months)  
          AND (SELECT end_date FROM months)  
    GROUP BY user_id  
),  
user_rank AS (  
    SELECT  
        user_id,  
        CASE  
            WHEN total_amount >= 10000 THEN '超级VIP'  
            WHEN total_amount >= 5000 THEN 'VIP'  
            ELSE '普通用户'  
        END AS user_level  
    FROM user_orders  
)  
SELECT  
    uo.user_id,  
    uo.order_count,  
    uo.total_amount,  
    ur.user_level  
FROM user_orders uo  
JOIN user_rank ur USING (user_id);  

五、注意事项与最佳实践

5.1 CTE 的局限性

• 不可修改数据：CTE 仅用于查询，无法直接执行 INSERT 或 UPDATE。
• 递归深度限制：默认递归层级受 max_sp_recursion_depth 系统变量控制（默认 1000）。
• 性能风险：复杂递归可能导致内存或时间开销过大，需谨慎测试。

5.2 开发者建议

• 分层拆解逻辑：将复杂查询拆分为多个 CTE，提升可读性。
• 优先选择 WITH：在单次查询中需要复用结果时，优先使用 CTE 而非子查询。
• 结合索引优化：对 CTE 底层表的字段添加索引，避免全表扫描。

结论

通过本文的讲解，我们深入理解了 MySQL WITH 子句的核心功能、语法结构及实际应用场景。无论是简化复杂查询、处理层级数据，还是优化代码逻辑，CTE 均展现了其独特的优势。对于编程初学者，建议从基础语法开始，逐步尝试递归查询等高级用法；中级开发者则可通过多层 CTE 设计更复杂的业务逻辑。

在实际开发中，合理运用 MySQL WITH 子句不仅能提升代码的可维护性，还能显著减少重复计算，是数据库优化的必备工具之一。希望本文能为你的数据库学习与实践提供有价值的参考！