MySQL 8.0新特性全面解读：窗口函数、CTE、原子DDL等

落辰姑娘_3627

发布时间：2025-09-11 11:34:01

1120人浏览过

来源于php中文网

原创

MySQL 8.0引入窗口函数、CTE和原子DDL，提升查询能力、代码可读性与数据一致性；窗口函数支持分区计算，CTE简化复杂查询，原子DDL确保操作的原子性，增强系统可靠性与开发效率。

mysql 8.0新特性全面解读：窗口函数、cte、原子ddl等

MySQL 8.0带来了许多令人兴奋的新特性，显著提升了数据库的性能、安全性和易用性。其中，窗口函数、公共表表达式（CTE）和原子DDL是尤为重要的几个方面，它们改变了我们编写和管理SQL的方式。

窗口函数、CTE、原子DDL的详细解读和应用场景。

窗口函数是什么？它能解决什么问题？

窗口函数允许我们在一个结果集的分区（窗口）上执行计算，而无需像GROUP BY那样折叠行。想象一下，你想知道每个员工的工资与部门平均工资的比较，或者想计算每个产品的销售额占总销售额的百分比。使用传统的SQL，这些操作通常需要复杂的子查询或自连接才能实现。窗口函数则可以轻松解决这些问题。

例如，假设我们有一个

employees

表，包含

id

name

department

salary

字段。我们可以使用窗口函数计算每个部门的平均工资，并将其与每个员工的工资进行比较：

SELECT
    id,
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
FROM
    employees;

AVG(salary) OVER (PARTITION BY department)

这部分就是窗口函数。

PARTITION BY department

定义了窗口，即每个部门。

AVG(salary)

在每个窗口内计算平均工资。结果集将包含每个员工的工资以及其所在部门的平均工资，而不会像

GROUP BY

那样减少行数。

窗口函数还支持各种其他函数，如

RANK()

DENSE_RANK()

ROW_NUMBER()

LAG()

LEAD()

等，可以用于排名、分页、计算差值等。它们极大地简化了复杂的SQL查询，提高了代码的可读性和可维护性。

CTE（公共表表达式）的实际应用场景有哪些？

CTE 允许我们定义一个临时的、命名的结果集，可以在一个查询中多次引用。它可以看作是一个命名的子查询，但比子查询更易于阅读和维护。 CTE使用

WITH

关键字定义。

一个常见的应用场景是处理递归数据。例如，假设我们有一个

employee_hierarchy

表，表示员工的上下级关系：

CREATE TABLE employee_hierarchy (
    employee_id INT,
    manager_id INT,
    employee_name VARCHAR(255)
);

INSERT INTO employee_hierarchy (employee_id, manager_id, employee_name) VALUES
(1, NULL, 'John CEO'),
(2, 1, 'Alice Manager'),
(3, 1, 'Bob Manager'),
(4, 2, 'Charlie Developer'),
(5, 2, 'David Developer'),
(6, 3, 'Eve Analyst');

我们可以使用 CTE 递归地查询某个员工的所有下属：

WITH RECURSIVE subordinate_tree AS (
    SELECT employee_id, manager_id, employee_name
    FROM employee_hierarchy
    WHERE employee_id = 1  -- 找到CEO

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employee_hierarchy e
    INNER JOIN subordinate_tree st ON e.manager_id = st.employee_id
)
SELECT * FROM subordinate_tree;

这个 CTE 首先选择 CEO 作为根节点，然后递归地连接

employee_hierarchy

表，找到所有直接或间接下属。 CTE 还可以用于简化复杂的查询，提高可读性。比如，可以将一个复杂的子查询定义为一个 CTE，然后在主查询中引用它。

原子DDL是什么？它的优势是什么？

在 MySQL 5.7 及更早版本中，DDL 操作（如创建表、修改表结构等）不是原子性的。这意味着如果在 DDL 操作过程中发生错误，可能会导致数据库处于不一致的状态。例如，如果在添加一个新列的过程中，数据库服务器崩溃了，可能只有一部分数据被更新，导致数据损坏。

win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录，或需要将该能力封装成可公开复用的 Codex skill。

下载

MySQL 8.0 引入了原子 DDL，通过将 DDL 操作封装在一个事务中，确保 DDL 操作要么完全成功，要么完全失败。如果在 DDL 操作过程中发生错误，数据库会自动回滚到之前的状态，保证数据的一致性。

原子 DDL 极大地提高了数据库的可靠性。即使在 DDL 操作过程中发生意外情况，也不会导致数据损坏。此外，原子 DDL 还简化了数据库的管理，因为不再需要手动处理 DDL 操作失败的情况。

例如，创建一个带有原子 DDL 的表：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=InnoDB;

如果在创建表的过程中发生错误（例如磁盘空间不足），MySQL 会自动回滚操作，保证数据库的完整性。

窗口函数有哪些常见的类型和用法？

窗口函数根据功能可以分为多种类型，常见的包括：

聚合窗口函数： 如
```
AVG()
```
,
```
SUM()
```
,
```
MIN()
```
,
```
MAX()
```
,
```
COUNT()
```
。它们在窗口内计算聚合值，但不会像
```
GROUP BY
```
那样减少行数。
排名窗口函数： 如
```
RANK()
```
,
```
DENSE_RANK()
```
,
```
ROW_NUMBER()
```
。它们用于在窗口内对行进行排名。
```
RANK()
```
会跳过排名，而
```
DENSE_RANK()
```
不会。
```
ROW_NUMBER()
```
为每一行分配一个唯一的序号。
值窗口函数： 如
```
LAG()
```
,
```
LEAD()
```
,
```
FIRST_VALUE()
```
,
```
LAST_VALUE()
```
,
```
NTH_VALUE()
```
。它们用于访问窗口内其他行的数据。
```
LAG()
```
和
```
LEAD()
```
可以访问前一行和后一行的数据，
```
FIRST_VALUE()
```
和
```
LAST_VALUE()
```
可以访问第一行和最后一行的数据，
```
NTH_VALUE()
```
可以访问指定行的数据。

这些窗口函数可以结合

PARTITION BY

子句和

ORDER BY

子句，实现各种复杂的查询需求。例如，可以使用

LAG()

函数计算每个月的销售额与上个月的销售额的差值：

SELECT
    month,
    sales,
    sales - LAG(sales, 1, 0) OVER (ORDER BY month) AS sales_difference
FROM
    monthly_sales;

LAG(sales, 1, 0) OVER (ORDER BY month)

表示访问前一行的

sales

值。

表示偏移量，即前一行。

表示默认值，如果前一行不存在，则返回 0。

如何在实际项目中选择使用窗口函数还是 CTE？

选择使用窗口函数还是 CTE 取决于具体的查询需求。

如果需要在结果集的分区上执行计算，并且不需要减少行数，则应该使用窗口函数。窗口函数可以简化复杂的聚合查询，提高代码的可读性和可维护性。
如果需要定义一个临时的、命名的结果集，并在一个查询中多次引用，则应该使用 CTE。 CTE 可以简化复杂的查询，提高可读性。尤其是在处理递归数据时，CTE 是一个非常强大的工具。

在某些情况下，可以同时使用窗口函数和 CTE。例如，可以使用 CTE 定义一个中间结果集，然后在窗口函数中使用它。