当前位置：首页 > article >正文

MySQL CTE：解锁SQL查询新模式

article 2025/1/31 5:35:27

一、CTE 初相识

二、CTE 基础语法

（一）基本语法结构

（二）语法规则详解

三、非递归 CTE 应用实例

（一）单 CTE 简单查询

（二）多 CTE 联合查询

四、递归 CTE 深入探索

（一）递归 CTE 原理剖析

（二）经典递归场景示例

五、CTE 与子查询的比较

（一）性能差异分析

（二）可读性和维护性对比

六、CTE 使用注意事项

（二）潜在问题及解决方案

一、CTE 初相识

在 MySQL 的世界里，当我们处理复杂的数据查询时，公用表表达式（Common Table Expressions，简称 CTE）就像是一位得力助手，悄然改变着我们编写 SQL 语句的方式。从 MySQL 8.0 版本开始，CTE 正式加入 MySQL 的大家庭，为开发者们提供了更强大、更灵活的数据处理能力。

那么，CTE 究竟是什么呢？简单来说，它是一个命名的临时结果集，仅在单个 SQL 语句（如 select、insert、delete 和 update）的执行范围内存在。我们可以把它想象成一个临时搭建的小舞台，在这个舞台上，我们可以进行各种数据的准备和预处理工作，而这些工作的成果只在当前 SQL 语句的 “表演” 中有效。

举个简单的例子，假设我们要从一个员工表employees和部门表departments中查询每个部门的员工人数以及部门名称。如果不使用 CTE，我们可能会写出这样的子查询语句：

SELECT 
    department_name,
    (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS employee_count
FROM 
    departments d;

这个查询虽然能得到我们想要的结果，但是子查询的结构使得代码的可读性和维护性都大打折扣。而且，当子查询变得复杂时，性能也会受到影响。

现在，让我们看看使用 CTE 如何实现同样的功能：

WITH employee_count_per_department AS (
    SELECT 
        department_id,
        COUNT(*) AS employee_count
    FROM 
        employees
    GROUP BY 
        department_id
)
SELECT 
    d.department_name,
    ecpd.employee_count
FROM 
    departments d
JOIN 
    employee_count_per_department ecpd ON d.department_id = ecpd.department_id;

在这个例子中，我们首先使用WITH关键字定义了一个名为employee_count_per_department的 CTE，它计算了每个部门的员工人数。然后，在主查询中，我们直接引用这个 CTE，就像引用一个普通的表一样，将它与departments表进行连接，从而得到每个部门的名称和员工人数。

通过这个对比，我们可以发现，CTE 与子查询的不同之处在于：CTE 可以被多次引用，并且可以在不同的 CTE 之间相互引用，使得代码的结构更加清晰，逻辑更加连贯。同时，CTE 还可以提高 SQL 的性能，因为它可以避免在子查询中重复计算相同的结果。这就好比我们在搭建积木时，CTE 可以让我们把一些常用的积木组合先搭建好，然后在需要的时候直接拿过来使用，而不需要每次都重新搭建。

看到这里，你是不是已经对 CTE 产生了浓厚的兴趣？接下来，就让我们深入探索 CTE 的语法和更多强大的功能，一起揭开它神秘的面纱。

二、CTE 基础语法

（一）基本语法结构

CTE 的基本语法结构围绕WITH关键字展开，其核心框架如下：

WITH cte_name AS (
    SELECT column1, column2,...
    FROM table_name
    WHERE condition
)
SELECT column1, column2,...
FROM cte_name
WHERE another_condition;

在这个结构中，WITH关键字宣告了 CTE 的开始，它就像是一个 “开场哨”，告诉 MySQL 接下来要定义一个临时的结果集。cte_name是我们给这个临时结果集取的名字，就好比给一个新组建的团队取个响亮的队名，这个名字在后续的查询中会被用来引用这个 CTE。AS关键字则像是一个等号，用来连接 CTE 的名称和它的定义内容。

括号内的SELECT语句是 CTE 的核心内容，它定义了这个临时结果集的数据来源和筛选条件。这里的SELECT语句和我们平时写的普通查询语句类似，从指定的table_name中选择需要的列column1, column2,...，并通过WHERE条件筛选出符合要求的数据。

在定义好 CTE 之后，紧接着是一个主查询。主查询从cte_name中选择数据，就像从一个普通的表中查询数据一样，并且可以根据需要添加更多的筛选条件another_condition。

为了让大家更直观地理解，我们来看一个简单的示例。假设有一个students表，包含student_id（学生 ID）、student_name（学生姓名）、age（年龄）和grade（年级）等列，我们要查询年龄大于 18 岁的学生姓名和年级，使用 CTE 可以这样写：

WITH filtered_students AS (
    SELECT student_name, grade
    FROM students
    WHERE age > 18
)
SELECT *
FROM filtered_students;

在这个例子中，filtered_students就是我们定义的 CTE，它从students表中筛选出年龄大于 18 岁的学生的姓名和年级。主查询则直接从filtered_students中选择所有列，获取我们想要的结果。

（二）语法规则详解

CTE 名称的命名规则：CTE 名称必须遵循 MySQL 的标识符命名规则，首字符必须是字母、下划线（_）或美元符号（$），后续字符可以是字母、数字、下划线或美元符号。例如，my_cte、cte_1都是合法的 CTE 名称，而1_cte则是不合法的。同时，在同一个WITH子句中，CTE 名称不能重复，否则 MySQL 会抛出错误，就像在一个班级里不能有两个相同名字的学生一样。
子查询的要求：CTE 定义中的子查询必须是一个有效的SELECT语句，它可以包含各种SELECT子句，如SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等。但需要注意的是，子查询中不能包含FOR UPDATE和LOCK IN SHARE MODE语句，因为 CTE 是一个只读的临时结果集，不支持对数据进行锁定和更新操作。
在主查询中引用 CTE：在主查询中，我们可以像引用普通表一样引用 CTE。可以在FROM子句中直接使用 CTE 的名称，然后根据需要进行连接、筛选、聚合等操作。例如，我们可以在前面的例子基础上，对筛选出的学生按年级进行分组统计人数：
```
WITH filtered_students AS (
    SELECT student_name, grade
    FROM students
    WHERE age > 18
)
SELECT grade, COUNT(*) AS student_count
FROM filtered_students
GROUP BY grade;
```
在这个查询中，主查询从filtered_students中选择数据，按grade列进行分组，并统计每个年级的学生人数。

此外，我们还可以在一个WITH子句中定义多个 CTE，这些 CTE 之间可以相互引用。例如：
```
WITH cte1 AS (
    SELECT column1, column2
    FROM table1
),
cte2 AS (
    SELECT column3, cte1.column1
    FROM table2
    JOIN cte1 ON table2.id = cte1.id
)
SELECT *
FROM cte2;
```
在这个例子中，cte2引用了前面定义的cte1，通过这种方式，我们可以将复杂的查询逻辑分解成多个步骤，每个 CTE 负责一个特定的计算或筛选任务，使整个查询更加清晰和易于维护。

三、非递归 CTE 应用实例

（一）单 CTE 简单查询

假设我们有一个员工表employees，其中包含employee_id（员工 ID）、employee_name （员工姓名）、department_id（部门 ID）和salary（工资）等字段。现在我们要查询 department_id为 10 的所有员工信息，使用 CTE 可以这样实现：

WITH department_10_employees AS (
    SELECT employee_id, employee_name, department_id, salary
    FROM employees
    WHERE department_id = 10
)
SELECT *
FROM department_10_employees;

在这个例子中，我们首先定义了一个名为department_10_employees的 CTE，它从employees表中筛选出department_id为 10 的员工信息。然后在主查询中，直接从这个 CTE 中选择所有列，获取我们需要的员工数据。

这种单 CTE 简单查询的方式，使得代码结构更加清晰。我们可以将复杂的筛选条件封装在 CTE 中，主查询只需关注如何获取和展示数据。就好比我们在整理书架时，先把某一类书籍（如小说类）挑选出来放在一个临时的位置（CTE），然后再从这个临时位置挑选我们想要阅读的具体书籍（主查询获取数据）。

（二）多 CTE 联合查询

还是以员工表employees和部门表departments为例，departments表包含department_id（部门 ID）和department_name（部门名称）字段。现在我们要查询每个员工及其所在部门的平均工资，这就需要多个 CTE 联合使用了。

WITH employee_department AS (
    SELECT e.employee_id, e.employee_name, d.department_name, e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
),
department_avg_salary AS (
    SELECT department_name, AVG(salary) AS avg_salary
    FROM employee_department
    GROUP BY department_name
)
SELECT ed.employee_name, ed.department_name, das.avg_salary
FROM employee_department ed
JOIN department_avg_salary das ON ed.department_name = das.department_name;

在这个查询中，我们定义了两个 CTE。第一个employee_departmentCTE 通过连接employees表和departments表，获取每个员工的姓名、所在部门名称以及工资。第二个department_avg_salaryCTE 则基于employee_departmentCTE 的结果，计算每个部门的平均工资。最后，在主查询中，通过连接这两个 CTE，我们得到了每个员工及其所在部门的平均工资信息。

这种多 CTE 联合查询的方式，将复杂的查询逻辑分解成多个步骤。每个 CTE 专注于一个特定的计算或关联任务，使得整个查询过程更加清晰、易于理解和维护。就像搭建一座复杂的建筑，每个 CTE 就像是建筑中的一个模块，先分别构建好各个模块，然后再将它们组合在一起，形成一个完整的结构。

四、递归 CTE 深入探索

（一）递归 CTE 原理剖析

递归 CTE 是 CTE 家族中的一个特殊成员，它允许在查询中引用自身，就像一个拥有 “自我复制” 能力的神奇工具，特别适用于处理具有层次结构的数据，如组织结构图、文件目录树、家族谱系等。其原理的核心在于巧妙地结合了种子查询（也称为锚成员）和递归查询（递归成员），并通过UNION [ALL]运算符将它们连接起来。

种子查询就像是递归旅程的起点，它是一个普通的查询语句，负责生成初始数据集。这个初始数据集是递归的基础，后续的递归操作都将基于它展开。例如，在查询员工上下级关系时，种子查询可以选择出公司的最高层级领导，作为整个递归查询的起始点。

递归查询则是这场旅程的 “推进器”，它不断引用 CTE 自身，通过与种子查询或上一次递归查询的结果进行关联，生成新的数据集。每一次递归，都会基于上一次的结果集进行扩展，就像在一棵树上不断长出新的枝叶。例如，在员工上下级关系的例子中，递归查询可以根据种子查询中选出的领导，找到其直接下属，然后再根据这些下属，找到他们的下属，以此类推，逐步构建出整个层级关系。

UNION [ALL]运算符在递归 CTE 中扮演着关键的角色，它负责将种子查询和递归查询的结果合并在一起。UNION会去除合并结果中的重复行，而UNION ALL则会保留所有行，包括重复行。在递归 CTE 中，通常使用UNION ALL，因为在处理层次结构数据时，重复行往往是有意义的，代表着不同层级之间的关联关系。而且，UNION ALL的性能通常比UNION更好，因为它不需要进行去重操作，减少了计算开销。

递归 CTE 的执行过程就像是一场层层递进的探索。首先，执行种子查询，得到初始结果集。然后，进入递归阶段，递归查询基于上一次的结果集进行计算，将新生成的结果与上一次的结果通过UNION [ALL]合并，形成新的结果集。这个过程会不断重复，直到递归查询不再返回新的数据，即满足了递归终止条件。例如，在员工上下级关系查询中，当所有层级的员工都被包含在结果集中，没有新的下属可以被找到时，递归终止。

（二）经典递归场景示例

以员工上下级关系表为例，假设我们有一个employees表，包含employee_id（员工 ID）、employee_name（员工姓名）、manager_id（上级 ID）字段，现在我们要查询某个员工的所有上级。比如，我们要查询员工 ID 为 5 的所有上级，使用递归 CTE 可以这样实现：

WITH RECURSIVE manager_hierarchy AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE employee_id = 5 -- 初始条件，选择员工ID为5的记录作为种子
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    INNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id -- 递归条件，通过关联找到上级
)
SELECT *
FROM manager_hierarchy;

在这个查询中，种子查询部分SELECT employee_id, employee_name, manager_id FROM employees WHERE employee_id = 5选择出了员工 ID 为 5 的记录，作为递归的起始点。递归查询部分SELECT e.employee_id, e.employee_name, e.manager_id FROM employees e INNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id通过将employees表与manager_hierarchy CTE 进行内连接，根据manager_id找到当前员工的上级，不断递归，直到没有上级为止。

如果我们要查询某个员工的所有下属，查询思路类似，只是递归条件中的关联关系变为根据当前员工的 ID 找到其下属的记录。例如，查询员工 ID 为 1 的所有下属：

WITH RECURSIVE subordinate_hierarchy AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE employee_id = 1 -- 初始条件，选择员工ID为1的记录作为种子
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    INNER JOIN subordinate_hierarchy sh ON e.manager_id = sh.employee_id -- 递归条件，通过关联找到下属
)
SELECT *
FROM subordinate_hierarchy;

通过这些示例，我们可以看到递归 CTE 在处理层次结构数据时的强大功能，它能够简洁而高效地实现复杂的层级查询，帮助我们轻松地探索数据中的层级关系。

五、CTE 与子查询的比较

（一）性能差异分析

在性能方面，CTE 和子查询有着不同的表现。从查询执行次数来看，子查询在主查询执行时，会根据其在查询语句中的位置和逻辑，被多次执行。例如，当子查询位于WHERE子句中，并且主查询对每一行数据都需要进行条件判断时，子查询可能会随着主查询的每一次行扫描而被执行一次。这就好比一个工人在完成一项任务时，需要不断地重复做同一件小事，效率自然不高。

而 CTE 在整个查询执行过程中，只会被计算一次。无论主查询引用它多少次，它都不会重新计算。这是因为 CTE 在执行前会被解析并生成一个临时结果集，后续的引用直接从这个临时结果集中获取数据。就像我们提前准备好了一份工具清单，在执行任务时，每次需要工具时都直接从清单中选取，而不需要重新去准备，大大提高了效率。

在资源占用方面，子查询的多次执行会导致更多的 CPU 资源被消耗，因为每次执行都需要进行查询解析、数据检索等操作。同时，由于可能会多次扫描相关表，也会增加磁盘 I/O 的负担。如果子查询的结果集较大，还会占用较多的内存来存储中间结果。

CTE 虽然也会占用一定的资源来生成临时结果集，但由于其只计算一次，在资源利用上相对更高效。尤其是当 CTE 的结果集被多次引用时，其资源优势更加明显。不过，如果 CTE 的定义中包含复杂的计算或大量数据的筛选，生成临时结果集的过程可能会消耗较多资源，但从整体查询执行的角度来看，后续的多次引用可以避免重复计算带来的资源浪费。

（二）可读性和维护性对比

通过实际代码示例，我们能更直观地感受到 CTE 在提高查询可读性和维护性方面的优势。假设我们有一个电商数据库，其中包含orders表（订单表，包含order_id、customer_id、order_date、total_amount等字段）和customers表（客户表，包含customer_id、customer_name、contact_email等字段）。现在我们要查询每个客户的订单总金额以及客户的联系方式，并且只显示订单总金额大于 1000 的客户信息。

如果使用子查询，代码可能如下：

SELECT 
    sub.customer_name,
    sub.contact_email,
    sub.total_amount
FROM 
    (
        SELECT 
            c.customer_id,
            c.customer_name,
            c.contact_email,
            SUM(o.total_amount) AS total_amount
        FROM 
            customers c
        JOIN 
            orders o ON c.customer_id = o.customer_id
        GROUP BY 
            c.customer_id, c.customer_name, c.contact_email
    ) sub
WHERE 
    sub.total_amount > 1000;

在这个子查询中，我们首先在子查询内部进行了客户和订单表的连接，以及按客户分组计算订单总金额的操作。然后在主查询中，从子查询的结果中筛选出订单总金额大于 1000 的记录。这种嵌套结构使得代码的阅读和理解变得困难，尤其是当子查询内部的逻辑更加复杂时，我们需要从内到外逐步解析代码的含义。

现在，让我们看看使用 CTE 如何实现同样的功能：

WITH customer_order_summary AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        c.contact_email,
        SUM(o.total_amount) AS total_amount
    FROM 
        customers c
    JOIN 
        orders o ON c.customer_id = o.customer_id
    GROUP BY 
        c.customer_id, c.customer_name, c.contact_email
)
SELECT 
    cos.customer_name,
    cos.contact_email,
    cos.total_amount
FROM 
    customer_order_summary cos
WHERE 
    cos.total_amount > 1000;

使用 CTE 时，我们首先定义了一个名为customer_order_summary的 CTE，在这个 CTE 中完成了客户和订单表的连接以及订单总金额的计算。然后在主查询中，直接从这个 CTE 中获取数据，并进行筛选。这种结构使得代码的逻辑更加清晰，每个部分的功能一目了然。如果我们需要修改计算订单总金额的逻辑，或者调整筛选条件，只需要在对应的 CTE 或主查询部分进行修改，而不会影响到其他部分，大大提高了代码的维护性。

六、CTE 使用注意事项

一）适用版本说明

CTE 是从 MySQL 8.0 版本开始引入的强大功能，这意味着如果你的 MySQL 版本低于 8.0，很遗憾，你将无法使用 CTE 来优化你的查询。就像你拥有一辆老式汽车，却无法安装最新的高科技导航系统一样。在这种情况下，你只能继续使用传统的子查询等方式来实现数据查询和处理。

所以，在决定使用 CTE 之前，请务必确认你的 MySQL 版本。你可以通过以下命令来查看当前 MySQL 的版本：

SELECT VERSION();

如果你的版本低于 8.0，并且希望使用 CTE 带来的便利，那么考虑升级你的 MySQL 版本是一个不错的选择。不过，在升级之前，一定要做好数据备份和测试工作，确保升级过程不会对现有业务造成影响。

（二）潜在问题及解决方案

递归 CTE 的无限循环问题：在使用递归 CTE 时，设置正确的终止条件至关重要。如果终止条件设置不当，就可能导致无限循环。例如，在查询员工上下级关系时，如果没有正确判断递归的结束点，就会出现一直递归下去的情况，这不仅会消耗大量的系统资源，还可能导致数据库崩溃。为了避免这种情况，我们必须确保递归查询中有明确的终止条件。比如在之前查询员工上级的示例中，当没有找到更高层级的上级时，递归就应该停止。同时，我们可以通过LIMIT语句来限制递归的深度，防止因数据异常导致的无限递归。例如：

WITH RECURSIVE manager_hierarchy AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE employee_id = 5
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    INNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id
    LIMIT 100 -- 限制递归深度为100
)
SELECT *
FROM manager_hierarchy;

CTE 结果集过大问题：如果 CTE 生成的结果集非常大，可能会占用大量的内存和磁盘空间，影响查询性能。例如，当我们使用 CTE 对一个包含数百万条记录的表进行复杂的聚合操作时，生成的临时结果集可能会超出系统的承载能力。为了解决这个问题，我们可以尽量减少 CTE 中不必要的列选择，只选择查询中真正需要的列。同时，可以考虑对 CTE 的结果进行适当的筛选和分组，减少数据量。例如，在计算每个部门的平均工资时，如果部门表非常大，我们可以先在 CTE 中对部门表进行必要的筛选，只保留需要计算平均工资的部门数据：

WITH filtered_departments AS (
    SELECT department_id, department_name
    FROM departments
    WHERE department_name LIKE '某特定部门%' -- 筛选特定部门
),
department_avg_salary AS (
    SELECT fd.department_id, fd.department_name, AVG(e.salary) AS avg_salary
    FROM filtered_departments fd
    JOIN employees e ON fd.department_id = e.department_id
    GROUP BY fd.department_id, fd.department_name
)
SELECT *
FROM department_avg_salary;

CTE 名称冲突问题：在同一个WITH子句中，CTE 的名称必须是唯一的，否则会导致语法错误。就像在一个班级里不能有两个完全相同名字的学生一样，在 SQL 查询的这个 “小世界” 里，每个 CTE 也都要有独一无二的名字。例如，以下代码就会因为 CTE 名称冲突而报错：

WITH cte AS (
    SELECT column1, column2
    FROM table1
),
cte AS ( -- 这里的cte名称与上面冲突
    SELECT column3, column4
    FROM table2
)
SELECT *
FROM cte;

为了避免这个问题，在定义 CTE 时，一定要仔细检查名称，确保每个 CTE 都有一个独特且有意义的名字，这样不仅能避免错误，还能让代码更易于理解和维护。

查看全文

http://www.kler.cn/a/525529.html