网站导航
> 技术文档 > SQL基础⑯ | MySQL8新特性篇

SQL基础⑯ | MySQL8新特性篇

网友: 技术文档 2025-07-26 20:36:00

0 序言

本文介绍MySQL 8的主要新特性及移除的旧特性，详解核心功能的定义、用法与示例。

算是作为SQL基础篇的最后一篇补充。

本文的内容可作为一个内容补充，

基本知识在前15篇中都覆盖到了，这次只是介绍MySQL8的一些新特性。

如果不是很深入，只是日常普通运用的话，

5.7版本基本上也够用。

1 MySQL 8 新特性概述

MySQL 8在功能上有显著改进，重构了源代码，优化了优化器，提升了性能与用户体验，同时移除了部分旧特性。

1.1 新增特性

  1. NoSQL支持优化：更灵活实现NoSQL功能，不依赖模式。
  2. 索引增强：新增隐藏索引（用于测试索引必要性）和降序索引（提升多列混合排序性能）。
  3. JSON支持完善：新增JSON_ARRAYAGG()、JSON_OBJECTAGG()聚合函数，增强->>操作符，优化JSON排序与更新。
  4. 安全与账户管理：新增caching_sha2_password插件、角色管理、密码历史记录等，提升安全性。
  5. InnoDB改进：在自增、索引、加密等方面优化，支持原子DDL，确保事务安全。
  6. 事务数据字典：存储数据库对象信息于内部事务表，替代旧版元数据文件。
  7. 原子DDL：将DDL相关操作（数据字典更新、存储引擎操作等）纳入原子事务，确保要么全成、要么全滚（仅InnoDB支持）。
  8. 资源管理：支持创建资源组，分配线程与CPU资源，控制资源消耗（受系统与配置限制）。
  9. 字符集调整：默认字符集从latin1改为utf8mb4，新增日语特定字符集utf8mb4_ja_0900_as_cs。
  10. 优化器增强：支持隐藏索引和降序索引，提升查询性能。
  11. 公用表表达式（CTE）：支持递归与非递归CTE，通过WITH语句命名临时结果集，简化复杂查询。
  12. 窗口函数：支持多种窗口函数，可在分组内排序并保留原表行数，提升统计效率。
  13. 正则表达式增强：采用Unicode组件库，新增REGEXP_LIKE()等函数，支持资源消耗控制。
  14. 内部临时表：TempTable引擎替代MEMORY成为默认引擎，高效存储VARCHAR等类型。
  15. 日志记录：错误日志由组件构成，可通过log_error_services配置过滤与写入。
  16. 备份锁：支持备份锁，确保备份时数据一致性。
  17. 复制增强：支持JSON文档部分更新的二进制日志记录，节省空间。

1.2 移除的旧特性

  1. 查询缓存：移除查询缓存相关语句（如FLUSH QUERY CACHE）、系统变量（如query_cache_size）等。
  2. 加密函数：移除ENCODE()、DES_ENCRYPT()等，建议用SHA2()、AES_ENCRYPT()替代。
  3. 空间函数：移除旧版非ST_和MBR前缀的空间函数。
  4. \\N与NULL：SQL解析器不再将\\N视为NULL，需用NULL替代（LOAD DATA等操作除外）。
  5. mysql_install_db：移除该程序，数据字典初始化通过mysqld --initialize实现。
  6. 通用分区处理程序：仅保留InnoDB的本地分区支持。
  7. 系统变量表：移除INFORMATION_SCHEMA中GLOBAL_VARIABLES等表，改用性能模式替代。
  8. mysql_plugin工具：通过--plugin-load或INSTALL PLUGIN替代。

2 新特性1：窗口函数

窗口函数用于在查询中对分组数据进行统计，同时保留原表行数，避免传统分组统计丢失细节的问题。

2.1 与传统方法对比

传统方法需通过临时表分步计算分组统计（如计算各区销售额占比），步骤繁琐；窗口函数可直接在一行中完成分组统计与占比计算，高效简洁。

示例：计算各区销售额占城市与全国总额的比例

  • 传统方法：需创建临时表存储城市总额、全国总额，再关联查询。
  • 窗口函数： 
    SELECT city AS 城市, county AS 区, sales_value AS 区销售额, SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 按城市分组统计 sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率, SUM(sales_value) OVER() AS 总销售额, -- 全局统计 sales_value/SUM(sales_value) OVER() AS 总比率FROM salesORDER BY city, county;

2.2 分类

分类 函数 说明 序号函数 ROW_NUMBER() 顺序排序，序号唯一 RANK() 并列排序，跳过重复序号（如1,1,3） DENSE_RANK() 并列排序，不跳过重复序号（如1,1,2） 分布函数 PERCENT_RANK() 计算等级百分比：(rank-1)/(总行数-1) CUME_DIST() 计算小于等于当前值的比例 前后函数 LAG(expr, n) 返回当前行前n行的expr值 LEAD(expr, n) 返回当前行后n行的expr值 首尾函数 FIRST_VALUE(expr) 返回窗口首行的expr值 LAST_VALUE(expr) 返回窗口尾行的expr值 其他函数 NTH_VALUE(expr, n) 返回第n行的expr值 NTILE(n) 将分区数据分为n组，返回组编号

2.3 语法结构

-- 方式1：直接定义窗口函数 OVER([PARTITION BY 字段 ORDER BY 字段 ASC|DESC])-- 方式2：通过窗口名复用函数 OVER 窗口名...WINDOW 窗口名 AS ([PARTITION BY 字段 ORDER BY 字段 ASC|DESC])
  • PARTITION BY：按字段分组，每组独立计算。
  • ORDER BY：组内排序，影响序号类函数结果。

2.4 示例

以goods表（含商品分类、价格等）为例：

  1. ROW_NUMBER()：查询每类商品价格降序的序号

    SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category, NAME, priceFROM goods;

  2. RANK()：查询每类商品价格排名（允许并列，跳过重复序号）

    SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category, NAME, priceFROM goods;

  3. LAG()：查询当前商品与前一个商品的价格差

    SELECT id, NAME, price, LAG(price, 1) OVER(PARTITION BY category_id ORDER BY price) AS pre_price, price - LAG(price, 1) OVER(PARTITION BY category_id ORDER BY price) AS diffFROM goods;

3 新特性2：公用表表达式（CTE）

CTE是命名的临时结果集，作用范围为当前语句，可替代子查询且支持复用，分为普通和递归两种。

3.1 普通公用表表达式

3.1.1 语法

WITH CTE名称 AS (子查询)SELECT|DELETE|UPDATE 语句;

3.1.2 示例：查询员工所在部门详情

-- 定义CTE获取员工所属部门IDWITH emp_dept_id AS (SELECT DISTINCT department_id FROM employees)-- 关联部门表查询详情SELECT d.* FROM departments dJOIN emp_dept_id e ON d.department_id = e.department_id;

3.2 递归公用表表达式

3.2.1 语法（含种子查询与递归查询）

WITH RECURSIVE CTE名称 AS ( 种子查询 -- 初始数据集（仅执行一次） UNION ALL 递归查询 -- 引用CTE自身，直至无新数据)SELECT|DELETE|UPDATE 语句;

3.2.2 示例：查询所有“下下属”（第三代及以上下属）

WITH RECURSIVE cte AS ( -- 种子查询：初代管理者（如employee_id=100） SELECT employee_id, last_name, manager_id, 1 AS n FROM employees WHERE employee_id = 100 UNION ALL -- 递归查询：找出以CTE中人员为管理者的下属，代次+1 SELECT a.employee_id, a.last_name, a.manager_id, n+1 FROM employees a JOIN cte ON a.manager_id = cte.employee_id)-- 筛选代次≥3的下下属SELECT employee_id, last_name FROM cte WHERE n >= 3;

4 小结

MySQL 8的新特性显著提升了数据库性能与开发效率。

窗口函数简化了分组统计与排序场景，避免临时表操作；

公用表表达式，尤其是递归CTE简化了复杂查询，支持树形结构数据处理。

同时，需注意移除的旧特性（如查询缓存），避免兼容性问题。

不过，在实际使用中，MySQL 5.7 和 8.0 对于多数基础业务场景差异不大！！

两者在常规的增删改查、事务处理等核心功能上表现相近，

对于中小型应用或对新特性需求不高的场景，5.7 完全能满足需求，

而 8.0 的新特性更多是为复杂场景和性能优化提供支持。

网络标签：

相关问题