从MySQL 5.7到8.0，有哪些让你印象深刻的新特性？-mysql教程-PHP中文网

MySQL 8.0在开发效率、数据分析和系统稳定性上实现全面飞跃，CTE和窗口函数显著提升复杂查询的可读性与分析能力，JSON功能增强支持灵活的半结构化数据处理，原子DDL保障元数据安全，UTF8mb4默认字符集解决编码问题，角色管理和caching_sha2_password强化权限与认证安全。

从mysql 5.7到8.0，有哪些让你印象深刻的新特性？

从MySQL 5.7到8.0，最让我印象深刻的，莫过于其在开发效率、数据分析能力和系统稳定性上的全面飞跃。CTE、窗口函数这些曾经在其他数据库中羡慕不已的特性终于加入，极大地提升了复杂查询的编写效率和可读性。同时，JSON功能的增强、原子DDL的引入以及默认UTF8mb4的设定，都让日常开发和运维工作变得更加顺手和安心。

解决方案

MySQL 8.0带来的新特性，就像是给数据库领域注入了一剂强心针，它不只是版本号的简单迭代，更是一次对现代应用需求的深刻回应。我个人感受最深的，是它让MySQL在处理复杂数据和分析场景时，不再显得那么“捉襟见肘”。

首先，通用表表达式（CTE）的出现，简直是复杂SQL查询的救星。以前，为了实现一些逻辑分层或递归查询，我们可能需要嵌套一大堆子查询，或者创建临时表，代码写出来又臭又长，调试起来更是痛苦不堪。CTE让SQL逻辑清晰地分块，可读性瞬间提升了好几个档次，尤其是递归CTE，解决了很多层级数据处理的难题，比如组织架构树的遍历。

接着是窗口函数，这玩意儿一上线，直接让MySQL在数据分析领域的竞争力飙升。过去，要计算累计销售额、排名、移动平均这些指标，要么写复杂的子查询，要么把数据拉出来在应用层处理。现在，

OVER()

登录后复制

子句一出，这些分析型任务变得简洁明了，性能也得到了优化。它让我在处理报表和BI需求时，能更专注于业务逻辑，而不是绞尽脑汁去“欺骗”数据库。

再来聊聊JSON功能的增强。从5.7开始MySQL就支持JSON，但8.0把它推向了一个新高度。新增的JSON聚合函数（如

JSON_ARRAYAGG

登录后复制

和

JSON_OBJECTAGG

登录后复制

）以及

JSON_TABLE

登录后复制

函数，让半结构化数据和关系型数据之间的转换和查询变得异常灵活。很多时候，我们为了追求开发速度，会将一些不那么规范的配置信息或者日志数据直接存成JSON格式。8.0的这些功能，让我可以直接在数据库层面高效地查询、转换这些数据，减少了应用层的复杂性。

当然，还有那些看似不那么“炫酷”但实际意义重大的改进，比如原子DDL。以前，一个DDL操作如果中途失败，可能会导致元数据损坏或者表处于一个不确定状态，让人心惊胆战。8.0的原子DDL保证了DDL操作的事务性，要么完全成功，要么完全回滚，大大提升了数据库的可靠性。默认的UTF8mb4字符集也省去了很多字符编码上的烦恼，尤其是在处理emoji表情或者多语言内容时，这简直是福音。

性能方面，8.0在InnoDB存储引擎、字典缓存、资源组等多个层面进行了优化，虽然这些细节不总是直接体现在SQL语句上，但体现在了系统的稳定性和响应速度上。安全性的提升，如角色管理和更强的默认密码认证插件

caching_sha2_password

登录后复制

，也让权限管理更加精细和安全。

MySQL 8.0的JSON功能究竟强大到什么程度，能解决哪些实际问题？

MySQL 8.0在JSON处理上的进步，可以说让它在面对半结构化数据时，拥有了前所未有的灵活性和表达力。这不只是简单地存储JSON字符串，而是能够像操作关系型数据一样，对JSON文档进行查询、修改、聚合和转换。我印象最深的是

JSON_TABLE

登录后复制

函数，它简直是JSON数据和关系型数据之间的一座桥梁。

想象一下，你有一个用户表，其中有一个

preferences

登录后复制

字段存储了用户的各种偏好设置，这些设置的结构并不固定，比如：

{
  "theme": "dark",
  "notifications": {
    "email": true,
    "sms": false
  },
  "locale": "en_US"
}

登录后复制

或者

{
  "theme": "light",
  "notifications": {
    "email": false
    // sms key might be missing
  },
  "timezone": "Asia/Shanghai",
  "widgets": ["weather", "calendar"]
}

登录后复制

在8.0之前，你可能需要写复杂的

JSON_EXTRACT

登录后复制

表达式，或者在应用层解析。现在有了

JSON_TABLE

登录后复制

，你可以直接将这个JSON字段“展开”成一张虚拟的表，然后像查询普通表一样进行操作。

SELECT
    jt.user_id,
    jt.theme,
    jt.email_notify,
    jt.sms_notify
FROM
    users,
    JSON_TABLE(
        users.preferences,
        '$' COLUMNS (
            user_id FOR ORDINALITY,
            theme VARCHAR(20) PATH '$.theme',
            email_notify BOOLEAN PATH '$.notifications.email',
            sms_notify BOOLEAN PATH '$.notifications.sms' DEFAULT FALSE ON EMPTY
        )
    ) AS jt
WHERE
    jt.theme = 'dark';

登录后复制

这个例子里，

DEFAULT FALSE ON EMPTY

登录后复制

特别实用，它能处理JSON路径不存在的情况，避免了空值带来的困扰。

此外，JSON聚合函数如

JSON_ARRAYAGG

登录后复制

和

JSON_OBJECTAGG

登录后复制

也极大地简化了数据的聚合和重构。比如，你可能想把某个用户的所有订单项聚合成一个JSON数组，或者将某个产品的所有评论聚合成一个JSON对象。

SELECT
    p.product_name,
    JSON_ARRAYAGG(
        JSON_OBJECT('review_id', r.id, 'rating', r.rating, 'comment', r.comment)
    ) AS reviews_json
FROM
    products p
JOIN
    reviews r ON p.id = r.product_id
GROUP BY
    p.product_name;

登录后复制

这在构建API响应时非常方便，减少了应用层的数据组装工作。还有

JSON_OVERLAP

登录后复制

可以判断两个JSON数组或对象是否有交集，

JSON_SCHEMA_VALID

登录后复制

用于验证JSON文档是否符合给定的JSON Schema，这些都让MySQL在处理日益复杂的、半结构化数据场景时，变得更加游刃有余。它不再仅仅是一个严格的关系型数据库，而是向着更灵活的数据管理平台迈进了一大步。

CTE和窗口函数如何彻底改变了我们在MySQL中编写复杂查询的方式？

CTE（Common Table Expressions）和窗口函数（Window Functions）的引入，对于我这种经常需要处理复杂报表和数据分析任务的人来说，简直是生产力工具的革命。它们改变的不仅仅是SQL的写法，更是我们思考和构建查询逻辑的方式。

CTE，也就是

WITH

登录后复制

子句，它允许你定义一个临时的、命名的结果集，这个结果集可以在后续的查询中被引用。最直接的好处就是提升了查询的可读性和模块化。以前，如果一个子查询的结果需要在多个地方被用到，你可能不得不重复写这个子查询，或者层层嵌套，导致SQL语句冗长且难以理解。CTE把这些中间结果独立出来，让整个查询逻辑变得像搭积木一样清晰。

有道小P

有道小P，新一代AI全科学习助手，在学习中遇到任何问题都可以问我。

查看详情

举个例子，假设我们要找出每个部门销售额最高的员工：

-- 传统写法，可能需要子查询或者临时表
SELECT
    e.employee_name,
    e.department_id,
    s.sales_amount
FROM
    employees e
JOIN
    sales s ON e.employee_id = s.employee_id
WHERE
    (e.department_id, s.sales_amount) IN (
        SELECT
            department_id,
            MAX(sales_amount)
        FROM
            employees e2
        JOIN
            sales s2 ON e2.employee_id = s2.employee_id
        GROUP BY
            department_id
    );

登录后复制

用CTE，逻辑就清晰很多：

WITH DepartmentMaxSales AS (
    SELECT
        e.department_id,
        MAX(s.sales_amount) AS max_sales
    FROM
        employees e
    JOIN
        sales s ON e.employee_id = s.employee_id
    GROUP BY
        e.department_id
)
SELECT
    e.employee_name,
    e.department_id,
    s.sales_amount
FROM
    employees e
JOIN
    sales s ON e.employee_id = s.employee_id
JOIN
    DepartmentMaxSales dms ON e.department_id = dms.department_id AND s.sales_amount = dms.max_sales;

登录后复制

更厉害的是递归CTE，它能处理层级数据，比如组织架构、评论回复树等。这在以前几乎是MySQL的痛点，现在可以优雅地解决。

而窗口函数，则让MySQL具备了强大的分析能力。它允许你在与当前行相关的“窗口”内执行聚合函数（如

SUM

登录后复制

AVG

登录后复制

COUNT

登录后复制

MAX

登录后复制

MIN

登录后复制

）或专用窗口函数（如

ROW_NUMBER

登录后复制

RANK

登录后复制

LEAD

登录后复制

LAG

登录后复制

），而不会将结果集折叠成单个行，这与

GROUP BY

登录后复制

截然不同。

比如，计算每个员工在其部门内的销售排名：

SELECT
    employee_name,
    department_id,
    sales_amount,
    RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS dept_sales_rank
FROM
    employees e
JOIN
    sales s ON e.employee_id = s.employee_id;

登录后复制

这里

PARTITION BY department_id

登录后复制

定义了窗口的边界，

ORDER BY sales_amount DESC

登录后复制

则定义了窗口内的排序规则。你可以轻松地计算累计和（

SUM(sales_amount) OVER (PARTITION BY department_id ORDER BY sale_date)

登录后复制

）、移动平均（

AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

登录后复制

），或者获取前一行/后一行的数据（

LAG

登录后复制

LEAD

登录后复制

）。这些功能在数据趋势分析、用户行为分析、金融数据处理等场景中，简直是不可或缺的利器。

总的来说，CTE和窗口函数让SQL查询的表达能力达到了一个新的高度。它们让复杂的业务逻辑可以直接在数据库层面高效实现，减少了应用层的代码量和数据传输，也让数据分析师和开发者能够用更直观、更强大的方式与数据交互。

除了查询优化，MySQL 8.0在数据可靠性和安全性上做了哪些关键升级？

MySQL 8.0在数据可靠性和安全性上的升级，虽然不像CTE或窗口函数那样直接改变查询体验，但它们是构建健壮、安全的企业级应用基石。这些改进往往在幕后默默工作，但其重要性不言而喻。

首先，原子DDL（Atomic DDL）是我认为在可靠性方面最关键的改进之一。在8.0之前，DDL（数据定义语言）操作，比如

ALTER TABLE

登录后复制

，并不是事务性的。这意味着如果一个DDL操作在执行过程中失败（比如服务器崩溃、断电），可能会导致表处于一种不一致的中间状态，元数据可能损坏，甚至需要手动干预才能恢复。这种不确定性对于生产环境来说是巨大的风险。

8.0引入的原子DDL，保证了DDL操作的“全有或全无”特性。这意味着一个DDL语句要么完全成功并提交，要么在失败时完全回滚，不会留下任何中间状态或损坏。这极大地提升了数据库在DDL操作时的可靠性，减少了潜在的宕机风险和运维人员的压力。它通过内部事务机制和元数据锁来实现，让我在执行结构变更时，心里踏实了不少。

在安全性方面，角色管理（Roles）的引入是一个里程碑式的改进。以前，管理用户权限常常让人头疼。你需要为每个用户单独授予或撤销一系列权限，当用户数量增多或者权限模型复杂时，管理起来非常繁琐且容易出错。8.0的角色管理借鉴了企业级数据库的最佳实践，允许你创建命名角色，然后将一组权限授予给这个角色，再将角色授予给一个或多个用户。

例如：

CREATE ROLE 'app_developer', 'app_admin';

GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'app_developer';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_admin';

CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'password';
GRANT 'app_developer' TO 'dev_user'@'localhost';

SET DEFAULT ROLE 'app_developer' TO 'dev_user'@'localhost';

登录后复制

这样，当一个用户的职责发生变化时，你只需要调整他们所属的角色，而不是逐一修改权限。这大大简化了权限管理，提升了安全策略的一致性和可维护性。

此外，默认的认证插件从