文章导读

MySQL 8.0的通用表表达式（CTE）如何使用？

作者 2025年9月11日 12

mysql 8.0的通用表表达式（CTE）允许你在一个查询中定义一个命名的临时结果集，然后在该查询中多次引用它。这能简化复杂查询，提高可读性。

使用WITH子句定义CTE，后面跟着CTE的名称和定义。定义可以是任何有效的select语句。然后在主查询中像一个普通表一样引用CTE。

解决方案：

通用表表达式（CTE）本质上是在单个查询中创建临时命名的结果集，这允许你以更模块化和可读的方式构建复杂的查询。MySQL 8.0引入了对CTE的支持，极大地增强了SQL的表达能力。下面是一些关于如何在MySQL 8.0中使用CTE的示例和最佳实践：

CTE的基本语法：

WITH   cte_name AS (     SELECT column1, column2     FROM table1     WHERE condition   ) SELECT column1, column2 FROM cte_name WHERE condition;

这个语法结构清晰地展示了CTE的定义和使用。

WITH

关键字开始，

cte_name

是你给CTE起的名字，

AS

后面的括号里是定义CTE的SELECT语句。之后，你可以在主查询中像引用普通表一样引用

cte_name

。

如何使用递归CTE？

递归CTE允许你查询层次结构数据，例如组织结构或类别树。递归CTE由两部分组成：一个

anchor

成员和一个

recursive

成员。

anchor

成员返回基本结果集，

recursive

成员引用CTE本身并迭代地构建结果集，直到满足终止条件。

一个示例：

假设我们有一个

employees

表，其中包含

id

name

, 和

manager_id

列。要查找所有员工及其经理的姓名，可以使用递归CTE：

WITH RECURSIVE EmployeeHierarchy AS (   SELECT id, name, manager_id, 0 AS level   FROM employees   WHERE manager_id IS NULL  -- Anchor member：找到最高层级的员工    union ALL    SELECT e.id, e.name, e.manager_id, eh.level + 1   FROM employees e   JOIN EmployeeHierarchy eh ON e.manager_id = eh.id  -- Recursive member：递归查找下属员工 ) SELECT id, name, manager_id, level FROM EmployeeHierarchy ORDER BY level, name;

在这个例子中，

anchor

成员选择所有

manager_id

为

NULL

的员工（即最高级别的员工）。

recursive

成员通过

manager_id

连接

employees

表和

EmployeeHierarchy

CTE，并增加层级

level

。

UNION ALL

合并两个结果集。递归会一直进行，直到没有更多的员工可以添加到层级结构中。

使用递归CTE时需要注意循环依赖。如果数据中存在循环依赖，递归CTE可能会无限循环。MySQL 8.0默认限制递归深度为1000。可以通过设置

max_execution_time

变量来防止无限循环：

SET Session max_execution_time = 10000; -- 设置最大执行时间为10秒

CTE和临时表有什么区别？

CTE和临时表都允许你存储中间结果集，但它们在使用方式和生命周期上有所不同。

生命周期： CTE的生命周期仅限于定义它的查询。查询执行完毕后，CTE会自动消失。临时表则在创建后一直存在，直到显式删除或会话结束。
可见性： CTE仅在定义它的查询中可见。临时表可以在创建它的会话中的任何查询中访问。

Raphael AI

免费无限制AI图像生成工具

439

查看详情
语法： CTE使用
```
WITH
```
子句定义，而临时表使用
```
CREATE TEMPORARY TABLE
```
语句创建。
性能： 在某些情况下，CTE可能比临时表更有效率，因为优化器可以更好地优化包含CTE的查询。然而，在其他情况下，临时表可能更适合，特别是当需要在多个查询中重用中间结果集时。

选择CTE还是临时表取决于具体的需求。如果只需要在单个查询中使用中间结果集，CTE通常是更好的选择。如果需要在多个查询中重用中间结果集，或者需要长时间存储中间结果集，临时表可能更适合。

如何在复杂的查询中使用多个CTE？

在MySQL 8.0中，你可以使用逗号分隔多个CTE定义，在一个查询中使用多个CTE。这使得可以分解复杂的查询，使其更易于理解和维护。

例如，假设你有一个

orders

表和一个

customers

表。你想找到所有订单总额超过1000元的客户，并按订单总额排序。可以使用多个CTE来实现：

WITH   OrderTotals AS (     SELECT customer_id, SUM(amount) AS total_amount     FROM orders     GROUP BY customer_id   ),   HighValueCustomers AS (     SELECT customer_id     FROM OrderTotals     WHERE total_amount > 1000   ) SELECT c.id, c.name, ot.total_amount FROM customers c JOIN HighValueCustomers hvc ON c.id = hvc.customer_id JOIN OrderTotals ot ON c.id = ot.customer_id ORDER BY ot.total_amount DESC;

在这个例子中，

OrderTotals

CTE计算每个客户的订单总额。

HighValueCustomers

CTE选择订单总额超过1000元的客户。然后，主查询连接

customers

表和两个CTE，以获取高价值客户的姓名和订单总额，并按订单总额排序。

使用多个CTE可以使复杂的查询更易于理解和维护。每个CTE可以专注于查询的一个特定方面，从而提高代码的可读性。

CTE的性能注意事项

虽然CTE可以简化查询，但需要注意其性能影响。Mysql优化器会尝试优化包含CTE的查询，但在某些情况下，CTE可能会导致性能问题。

物化： 默认情况下，MySQL可能会物化CTE，这意味着它会将CTE的结果集存储在临时表中。这可能会导致额外的I/O开销，特别是对于大型结果集。可以使用
```
MATERIALIZED
```
和
```
NO MATERIALIZED
```
提示来控制CTE的物化行为。
索引： CTE本身不能创建索引。如果CTE返回的结果集很大，并且需要在主查询中进行连接或过滤，可以考虑在主查询中创建索引。
复杂性： 过度复杂的CTE可能会导致优化器难以优化查询。如果查询性能不佳，可以尝试将CTE分解为更小的部分，或者使用临时表代替CTE。

总的来说，CTE是一个强大的工具，可以简化复杂的查询，提高代码的可读性。然而，需要注意其性能影响，并根据具体情况选择合适的实现方式。

评论（已关闭）

评论已关闭

Hello! 欢迎来到悠悠畅享网！