MySQL 8 的 SQL 语法新特性
下面是一个表格，汇总了 MySQL 8 的主要 SQL 语法新特性：
| 功能类别 | 新增的 SQL 语法/功能点 | 简要说明与示例 | |
|---|---|---|---|
| 公共表表达式 (CTE) | WITH ... AS (...) | 定义临时结果集供主查询使用。支持递归（Recursive CTE）125。 | |
| 窗口函数 | OVER() 子句及相关函数 | 对查询结果的子集（窗口）进行计算，如 ROW_NUMBER(), RANK(), LEAD(), LAG() 等125。 | |
| JSON 功能增强 | JSON_ARRAYAGG(), JSON_OBJECTAGG() | 聚合函数，将行数据聚合为 JSON 数组或对象125。 | |
->> (内联路径操作符) | 等价于 JSON_UNQUOTE(JSON_EXTRACT())，直接输出无引号的 JSON 值125。 | ||
| 正则表达式增强 | REGEXP_LIKE(), REGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_SUBSTR() | 提供更强大且符合 Unicode 标准的正则表达式处理125。 | |
| 聚合函数增强 | GROUPING() 函数 | 与 GROUP BY ... WITH ROLLUP 一起使用，区分结果中的汇总行和明细行。 | |
| 函数索引 | 在索引中使用表达式 | 对函数或表达式的计算结果创建索引，例如 CREATE INDEX idx_func ON t (UPPER(col));68。 | |
| 降序索引 | 明确支持降序索引 DESC（在索引定义中） | 优化按降序排序的查询。例如 CREATE INDEX idx_desc ON t (col DESC);126。 | |
| 隐藏索引 | `VISIBLE | INVISIBLE`（在索引定义中） | 将索引设置为对优化器不可见，用于测试索引必要性而不删除它。例如 ALTER TABLE t ALTER INDEX idx_name INVISIBLE;6。 |
| 权限管理增强 | 角色 (Role) | 创建角色并授予权限，然后将角色分配给用户，简化权限管理128。 |
🧠 一些细节和示例
- 公共表表达式 (CTE) ：允许在查询前定义一个临时的命名结果集，提高复杂查询的可读性和可维护性。支持递归查询，处理树状或层次结构数据非常方便125。
-- 非递归CTE
WITH department_salaries AS (SELECT department_id, SUM(salary) AS total_salaryFROM employeesGROUP BY department_id
)
SELECT * FROM department_salaries WHERE total_salary > 100000;-- 递归CTE（示例：生成数字序列）
WITH RECURSIVE number_sequence(n) AS (SELECT 1UNION ALLSELECT n + 1 FROM number_sequence WHERE n < 10
)
SELECT * FROM number_sequence;
-
窗口函数：允许你对一个与当前行相关的“窗口”内的行进行计算，而不会将结果集合并成单一行125。
-- 计算每个部门内员工的工资排名 SELECT employee_name, department_id, salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank FROM employees; -
JSON 功能增强：提供了更多操作和构建 JSON 文档的函数125。
-- 将行聚合为JSON数组和对象
SELECT JSON_ARRAYAGG(employee_name) FROM employees WHERE department_id = 1;
SELECT department_id, JSON_OBJECTAGG(employee_name, salary) FROM employees GROUP BY department_id;-- 使用->>操作符直接提取并取消引用JSON值
SELECT data->>'$.name' AS name FROM users;
- 正则表达式增强：新的正则表达式函数提供了更强大、更符合标准的功能125。
-- 使用新REGEXP函数进行匹配和替换SELECT REGEXP_REPLACE(phone_number, '^(\d{3})(\d{4})(\d{4})$', '\1-\2-\3') AS formatted_phone FROM customers;
-
函数索引：可以对列的函数表达式创建索引，加速基于这些表达式的查询68。
-- 对名字的大写形式创建索引，加速不区分大小写的查询 CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name)); -- 查询时使用索引 SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH'; -
降序索引与隐藏索引：
-- 创建降序索引优化ORDER BY ... DESC查询
CREATE INDEX idx_created_at_desc ON orders (created_at DESC);-- 隐藏一个索引测试性能
ALTER TABLE orders ALTER INDEX idx_created_at_desc INVISIBLE;
-- 再次显示索引
ALTER TABLE orders ALTER INDEX idx_created_at_desc VISIBLE;
-
角色管理：
-- 创建角色并授予权限 CREATE ROLE 'report_read_only'; GRANT SELECT ON report_db.* TO 'report_read_only'; -- 将角色授予用户 GRANT 'report_read_only' TO 'some_user'@'%'; -- 设置默认角色（可选，MySQL 8.0需要） SET DEFAULT ROLE 'report_read_only' TO 'some_user'@'%';
⚡ 使用注意事项
- 兼容性：在升级到 MySQL 8 或使用新语法前，务必评估其对现有应用的影响。例如，MySQL 8 中
GROUP BY子句不再进行隐式排序，若需排序必须显式加上ORDER BY36。 - 性能：虽然新特性（如函数索引、降序索引）能提升性能，但也需合理使用，避免不必要的资源消耗。
- 学习成本：团队需要时间熟悉和掌握这些新语法和特性。
本文由 dblens.com 知识分享，🚀 dblens for MySQL - AI大模型深度融合的一款免费的MySQL可视化GUI数据库连接管理软件。