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

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 操作过程中发生错误，可能会导致数据库处于不一致的状态。例如，如果在添加一个新列的过程中，数据库服务器崩溃了，可能只有一部分数据被更新，导致数据损坏。

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

PIA

全面的AI聚合平台，一站式访问所有顶级AI模型

226 查看详情 PIA

原子 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 定义一个中间结果集，然后在窗口函数中使用它。

原子DDL对数据库运维和开发有哪些影响？

原子 DDL 极大地简化了数据库运维和开发。

运维方面：原子 DDL 提高了数据库的可靠性，减少了数据损坏的风险。运维人员不再需要手动处理 DDL 操作失败的情况，降低了运维成本。
开发方面：原子 DDL 使得开发人员可以更放心地执行 DDL 操作，而不用担心数据一致性问题。这提高了开发效率，降低了开发风险。

总的来说，MySQL 8.0 的原子 DDL 是一项非常重要的改进，它提高了数据库的可靠性、简化了数据库的管理，并提高了开发效率。

以上就是MySQL 8.0新特性全面解读：窗口函数、CTE、原子DDL等的详细内容，更多请关注知识资源分享宝库其它相关文章！

相关标签： mysql 工具代码可读性 sql mysql count 封装递归数据库大家都在看： MySQL内存使用过高（OOM）的诊断与优化配置 MySQL与NoSQL的融合：探索MySQL Document Store的应用如何通过canal等工具实现MySQL到其他数据源的实时同步？使用Debezium进行MySQL变更数据捕获（CDC）实战如何设计和优化MySQL中的大表分页查询方案