MySQL 8 新特性盘点：从功能升级到常用 SQL 示例

MySQL 8（8.0 系列）相对 MySQL 5.7 的变化非常大：既有面向开发者的 SQL 能力增强（CTE、窗口函数等），也有面向运维与安全的系统升级（统一数据字典、角色、默认认证方式等）。本文按“你用得上的”优先级梳理新特性，并配套常用 SQL 示例，适合收藏做速查。

---

TL;DR（先给结论）

• 写分析报表：优先用 窗口函数（OVER(PARTITION BY ...)），少写自连接/子查询。
• 写复杂查询：优先用 CTE（WITH），可读性和可维护性明显提升。
• JSON 入库：不只是 JSON_EXTRACT，还可以用 JSON_TABLE 把 JSON “摊平”成关系表来查。
• 性能排查：用 EXPLAIN ANALYZE 看真实执行开销；必要时配合 直方图（Histogram）、不可见索引（Invisible Index） 做安全优化。
• 权限治理：用 角色（Roles） 管理权限集合，少写一堆重复 GRANT。

---

1) MySQL 8 的核心变化（相对 5.7）

1.1 数据字典（Data Dictionary）统一

MySQL 8 把系统元数据从零散的文件/表结构升级为统一的数据字典（存储在 InnoDB 系统表中），对 DBA 来说更一致、更容易维护，也提升了 DDL 一致性。

你体感最明显的变化通常是：元数据更“数据库化”，一些 INFORMATION_SCHEMA / performance_schema 的信息更完整、更可用。

---

1.2 默认字符集/排序规则升级

MySQL 8 默认字符集为 utf8mb4，默认排序规则通常为 utf8mb4_0900_ai_ci（基于 Unicode 9.0）。这会影响：

• 字符串比较、排序结果（尤其是大小写、重音符号、中文排序差异）
• 与旧系统/旧排序规则混用时的兼容性

查看当前库/表的字符集与排序规则：

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

SHOW CREATE DATABASE your_db;
SHOW CREATE TABLE your_table;

---

1.3 认证与安全：caching_sha2_password、角色（Roles）

MySQL 8 默认认证插件是 caching_sha2_password（比旧的 mysql_native_password 更安全）。在老客户端（尤其老驱动）连接时可能需要升级驱动或显式配置。

同时 MySQL 8 引入了 角色（Roles），让权限治理从“给人授权”升级为“给角色授权，再把角色赋给人”。

---

2) 面向开发者的 SQL 能力升级（最值得学）

2.1 CTE（Common Table Expression，WITH）

CTE 的价值是：把复杂查询拆成“具名的中间结果”，可读性与可维护性显著提升。

例 1：用 CTE 拆分复杂逻辑

WITH paid_orders AS (
  SELECT id, user_id, amount, paid_at
  FROM orders
  WHERE status = 'PAID'
),
recent_paid AS (
  SELECT *
  FROM paid_orders
  WHERE paid_at >= NOW() - INTERVAL 30 DAY
)
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM recent_paid
GROUP BY user_id
ORDER BY total DESC;

例 2：递归 CTE（树/层级结构）

适合查组织架构、分类树、评论树等。

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories
  WHERE id = 100

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN category_tree t ON c.parent_id = t.id
)
SELECT *
FROM category_tree
ORDER BY depth, id;

---

2.2 窗口函数（Window Functions）

窗口函数是 MySQL 8 的“生产力神器”：在不丢失明细行的情况下，做排名、累计、分组统计。

例 1：每个部门工资排名

SELECT
  emp_id,
  dept_id,
  salary,
  DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employee;

例 2：按日期做累计（running total）

SELECT
  dt,
  amount,
  SUM(amount) OVER (ORDER BY dt) AS running_total
FROM daily_sales
ORDER BY dt;

例 3：取“每组 Top N”（常见报表需求）

WITH ranked AS (
  SELECT
    user_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM payments
)
SELECT *
FROM ranked
WHERE rn <= 3
ORDER BY user_id, rn;

---

2.3 JSON 增强：JSON_TABLE（把 JSON 变成表）

MySQL 5.7 虽然支持 JSON，但很多场景要写大量 JSON_EXTRACT。MySQL 8 的 JSON_TABLE 可以把 JSON 数组/对象“摊平”为行列，查询体验更像关系表。

假设 orders.items 存的是 JSON 数组：

[
  {"sku":"A001","qty":2,"price":10.5},
  {"sku":"B002","qty":1,"price":99}
]

查询每个订单的明细行：

SELECT
  o.id AS order_id,
  jt.sku,
  jt.qty,
  jt.price,
  jt.qty * jt.price AS line_total
FROM orders o
JOIN JSON_TABLE(
  o.items,
  '$[*]' COLUMNS (
    sku   VARCHAR(32)  PATH '$.sku',
    qty   INT          PATH '$.qty',
    price DECIMAL(10,2) PATH '$.price'
  )
) AS jt
ORDER BY o.id;

---

3) 索引与优化相关的新能力（更安全地调优）

3.1 不可见索引（Invisible Index）

不可见索引让你可以在“不影响现网查询计划”的情况下做验证：

• 先把索引设为不可见，观察是否会影响查询（不会被优化器使用）
• 再设回可见，或删除

ALTER TABLE t ALTER INDEX idx_user_id INVISIBLE;
ALTER TABLE t ALTER INDEX idx_user_id VISIBLE;

---

3.2 降序索引（Descending Index）

MySQL 8 支持真正的降序索引，有利于这类查询：

SELECT *
FROM logs
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;

建立复合索引时可显式指定排序方向：

CREATE INDEX idx_user_time ON logs(user_id, created_at DESC);

---

3.3 函数索引（Functional Index）/ 生成列（Generated Column）

当你经常按某个表达式过滤（例如对 email 做小写、对 JSON 某字段过滤），MySQL 8 可以：

• 用 函数索引（本质是对表达式建索引）
• 或用 生成列 + 索引（更通用，也便于解释）

例：对 email 小写做索引（生成列方式）：

ALTER TABLE users
  ADD COLUMN email_lc VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(email)) STORED,
  ADD INDEX idx_email_lc (email_lc);

SELECT *
FROM users
WHERE email_lc = LOWER('Test@Example.com');

---

3.4 直方图（Histogram）：帮优化器理解数据分布

当某列数据分布非常不均匀（例如 90% 是同一个值），优化器可能误判选择性。直方图能提供更真实的分布信息，提升计划选择准确度。

ANALYZE TABLE orders UPDATE HISTOGRAM ON status;

查看直方图（存在于 information_schema 相关视图中，具体字段随版本变化）：

SELECT *
FROM information_schema.column_statistics
WHERE schema_name = DATABASE();

---

3.5 EXPLAIN ANALYZE：看“真实执行”而不是估算

EXPLAIN 很重要，但它更多是估算；EXPLAIN ANALYZE 会实际执行并给出更真实的开销信息（适合定位慢点）。

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 1
ORDER BY paid_at DESC
LIMIT 20;

---

4) 权限治理：Roles（角色）怎么用

4.1 创建角色并授权

CREATE ROLE 'app_read', 'app_write';

GRANT SELECT ON your_db.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON your_db.* TO 'app_write';

4.2 把角色赋给用户并设默认角色

GRANT 'app_read' TO 'app_user'@'%';
SET DEFAULT ROLE 'app_read' TO 'app_user'@'%';

查看当前权限：

SHOW GRANTS FOR 'app_user'@'%';

---

5) 常用 SQL 示例（按真实业务场景）

5.1 去重与最近一条（每个用户最新订单）

MySQL 8 之前常用“自连接 + MAX”，MySQL 8 可以直接用窗口函数：

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

---

5.2 分段统计（按月汇总 + 同比/环比）

WITH m AS (
  SELECT
    DATE_FORMAT(paid_at, '%Y-%m') AS ym,
    SUM(amount) AS total
  FROM orders
  WHERE status = 'PAID'
  GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
)
SELECT
  ym,
  total,
  LAG(total, 1) OVER (ORDER BY ym) AS prev_month_total,
  total - LAG(total, 1) OVER (ORDER BY ym) AS mom_delta
FROM m
ORDER BY ym;

---

5.3 防止“分页越翻越慢”（seek pagination）

当数据量大时，LIMIT offset, size 会越来越慢。可以用“上一页最后一条”的游标做翻页：

SELECT *
FROM logs
WHERE (created_at, id) < ('2026-06-01 12:00:00', 100000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

配合索引：

CREATE INDEX idx_logs_cursor ON logs(created_at DESC, id DESC);

---

5.4 JSON 字段过滤 + 结构化查询

如果 profile 是 JSON，且你经常按其中某字段查询：

SELECT *
FROM users
WHERE JSON_EXTRACT(profile, '$.country') = 'CN';

更可维护的做法是：生成列 + 索引（见 §3.3）。

---

6) 升级/迁移小贴士（少踩坑）

• 驱动兼容：确认客户端/驱动支持 caching_sha2_password，或在特定用户上改回旧插件（不推荐长期使用）。
• 排序规则变化：若你依赖旧排序行为（例如某些特殊字符排序），需要明确指定 collation。
• SQL 模式与保留字：MySQL 8 新增了一些关键字，旧字段名可能冲突（用反引号或改名）。
• 执行计划变化：升级后同一 SQL 可能选择不同索引；建议对关键 SQL 做 EXPLAIN ANALYZE 对比与回归。

---

参考与延伸阅读

• MySQL 8.0 Reference Manual（官方文档）
• MySQL 8.0 Window Functions（官方文档）
• MySQL 8.0 CTE / Recursive CTE（官方文档）
• JSON_TABLE（官方文档）