DevCN开发者的创作与交流社区
RSS
菜单
全部文章快讯开发科技深度热点
技术文章/开发

第 52 章 8.0新特性踩坑

站长
内容摘要

MySQL 8.0 是当前生产主流，但它带来的新特性既是福利也是新坑——原子 DDL、降序索引、隐藏列、窗口函数、CTE、角色、字符集默认值变化、sql_mode 严格化，每一项都可能让从 5.7 迁移过来的团队翻车。本章梳理 8.0 使用与升级中的 11 个高频踩坑。示例基于 MySQL 8.0（本机无 MySQL 服务，DDL 语法以官方 8.0 文档为准书写）。读完你能避开"升了 8.0 却处处不对劲"的那些坑。

📖 本篇简介：MySQL 8.0 是当前生产主流，但它带来的新特性既是福利也是新坑——原子 DDL、降序索引、隐藏列、窗口函数、CTE、角色、字符集默认值变化、sql_mode 严格化，每一项都可能让从 5.7 迁移过来的团队翻车。本章梳理 8.0 使用与升级中的 11 个高频踩坑。示例基于 MySQL 8.0（本机无 MySQL 服务，DDL 语法以官方 8.0 文档为准书写）。读完你能避开"升了 8.0 却处处不对劲"的那些坑。

目录

  • 坑 1：还把 utf8 当成 utf8mb4
  • 坑 2：以为 8.0 还能随便留匿名账号
  • 坑 3：降序索引以为是把普通索引反着扫
  • 坑 4：隐藏列以为它不存在
  • 坑 5：递归 CTE 忘了写终止条件
  • 坑 6：以为崩溃后 DDL 和 5.7 一样留残表
  • 坑 7：默认字符集变了导致老连接乱码
  • 坑 8：窗口函数在 5.7 老库上直接报错
  • 坑 9：sql_mode 默认严格化老数据被拒
  • 坑 10：AUTO_INCREMENT 重启后回退
  • 坑 11：GROUP BY 不再隐含排序
  • 速查表：8.0 与 5.7 差异速记
  • 注意事项：升级 8.0 前的 Checklist
  • 自测：你了解 8.0 新坑吗

坑 1：还把 utf8 当成 utf8mb4

场景 / 错误示范：

-- 建库建表还在写 utf8，以为它就是完整的 UTF-8
CREATE DATABASE db DEFAULT CHARACTER SET utf8;
CREATE TABLE t(name VARCHAR(100)) CHARSET=utf8;
-- 插入 emoji 表情 👍 直接报 "Incorrect string value" 或存成 ?

为什么是坑：MySQL 里的 utf8 是个历史遗留的"阉割版"，最多存 3 字节字符，emoji（4 字节）存不下。真正完整的 UTF-8 叫 utf8mb4。8.0 虽然默认字符集改成了 utf8mb4，但老脚本里手写的 utf8 依旧是阉割版，迁移时悄悄埋下 emoji 存不进的雷。

正确做法：

-- 一律用 utf8mb4 + 正确的排序规则
CREATE DATABASE db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE t(
    name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 8.0 默认排序规则是 utf8mb4_0900_ai_ci，比 5.7 的 utf8mb4_general_ci 更快更准

-- 存量表检查：找出还在用老字符集的表
SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
  AND table_collation NOT LIKE 'utf8mb4%';

-- 升级脚本：把库里所有 utf8/utf8mb3 的表统一转成 utf8mb4
-- 生成批量转换语句（先在测试库验证）
SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name,
              ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.tables
WHERE table_schema = 'your_db'
  AND table_collation LIKE 'utf8%';
-- 把生成的 ALTER 语句批量执行；大表要在线用 pt-online-schema-change

加分点：8.0 的 utf8mb4_0900_ai_ci 基于 Unicode 9.0 排序规则，比 5.7 的 utf8mb4_general_ci 排序更准确、性能更好，新库直接用它。

坑 2：以为 8.0 还能随便留匿名账号

场景 / 错误示范：

-- 老习惯：装完库不删匿名用户，以为无所谓
-- 5.7 以前 mysql.user 里常有 ''@'localhost' 这种匿名账号
SELECT user, host FROM mysql.user;
-- 应用连不上或连到了匿名账号，权限莫名不对

为什么是坑：8.0 改了权限模型，取消了老的匿名账号行为，认证插件默认从 mysql_native_password 换成 caching_sha2_password。老脚本里 GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '...' 的写法在新插件下，老客户端连不上，报认证插件错误。

正确做法：

-- 8.0 查看账号和认证插件
SELECT user, host, plugin FROM mysql.user;

-- 老应用连不上时，按需把账号改回兼容插件（新应用建议升级客户端支持 caching_sha2）
ALTER USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY '强密码';

-- 及时删除多余/匿名账号
DROP USER IF EXISTS ''@'localhost';

-- 8.0 不能再像 5.7 那样直接 UPDATE mysql.user 改密码（权限表是数据字典，禁止手工改）
-- 正确方式一律用 ALTER USER / CREATE USER
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass!';
FLUSH PRIVILEGES;   -- 8.0 下大部分权限变更自动生效，手工改表才需要 FLUSH

加分点：8.0 默认 caching_sha2_password 更安全，但要求 JDBC/驱动是较新版本。老驱动连不上时，先确认驱动版本再考虑是否回退插件，别一上来就改密码策略。

坑 3：降序索引以为是把普通索引反着扫

场景 / 错误示范：

-- 5.7 时代：INDEX(a DESC) 写了等于没写，索引总是正向存
-- 到了 8.0，开发者以为降序索引没区别，还是普通索引反着扫
CREATE TABLE t(a INT, b INT, INDEX idx(a DESC, b ASC));
-- 查询 ORDER BY a DESC, b ASC 时，开发者以为还要 filesort

为什么是坑：5.7 里 DESC 在索引定义里被忽略，索引永远正向存储，双向排序都得靠反向扫描+可能的 filesort。8.0 真正支持降序索引，索引键可以按指定方向物理存储，ORDER BY a DESC, b ASC 这种"混合方向"的排序也能直接走索引，省掉 filesort。不了解这一点，就会错过 8.0 的优化红利。

正确做法：

-- 8.0：按查询的排序方向建复合索引，混合方向也能走索引
CREATE TABLE t(
    a INT, b INT,
    INDEX idx(a DESC, b ASC)     -- a 降序存、b 升序存
);
-- 这条 ORDER BY 直接命中索引，无需 filesort：
SELECT * FROM t ORDER BY a DESC, b ASC LIMIT 10;

-- EXPLAIN 验证降序索引是否真的省掉了 filesort
EXPLAIN SELECT * FROM t ORDER BY a DESC, b ASC LIMIT 10;
-- 关注两行：key 应命中 idx；Extra 不应出现 "Using filesort"
-- 若还是 filesort，说明索引列顺序/方向和 ORDER BY 不完全匹配，要对齐

加分点：EXPLAIN 里看到 Extra 没有 Using filesort，且 key 命中该复合索引，就说明降序索引生效了。

坑 4：隐藏列以为它不存在

场景 / 错误示范：

-- 8.0 支持 INVISIBLE 隐藏列，SELECT * 不显示它
ALTER TABLE t ADD COLUMN secret_code VARCHAR(50) INVISIBLE;
SELECT * FROM t;        -- 查不到 secret_code，开发者以为列没加成功
INSERT INTO t VALUES (...);  -- 老 INSERT 不带列名，列数对不上直接报错

为什么是坑：隐藏列对 SELECT * 和不带列名的 INSERT 不可见，但它真实存在、占存储、可以建索引。老脚本如果用 INSERT INTO t VALUES (...) 按位置写值，遇到隐藏列就会因列数不匹配报错，且排查半天找不到原因。

正确做法：

-- 隐藏列要显式引用才能看到/写入
SELECT * FROM t;
SELECT secret_code FROM t;               -- 显式列名才能查出

-- INSERT 永远带列名，避免被隐藏列坑
INSERT INTO t(id, name, secret_code) VALUES (1, '甲', 'ABC');

-- 不想隐藏了随时改回可见
ALTER TABLE t ALTER COLUMN secret_code VISIBLE;

加分点：隐藏列常用来做"函数索引的替身"（如把生成列藏起来），业务侧别用 SELECT *，永远显式列名就能避开。

坑 5：递归 CTE 忘了写终止条件

场景 / 错误示范：

-- 想递归查部门树，递归部分没写收敛条件
WITH RECURSIVE dept AS (
    SELECT id, name, parent_id FROM department WHERE id = 1
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM department d JOIN dept p ON d.parent_id = p.id
    -- 忘了：如果数据里有环（parent 互相指向），这里死循环
)
SELECT * FROM dept;
-- 报错：Recursive CTE has more than max_recursion_depth

为什么是坑：递归 CTE = 锚点查询 + 递归查询反复自连直到空。如果数据有环（父子互相指），或忘了限制层数，递归会无限进行直到触发 cte_max_recursion_depth（默认 1000）报错。这是写树形结构最常见的事故。

正确做法：

WITH RECURSIVE dept AS (
    SELECT id, name, parent_id, 1 AS lvl FROM department WHERE id = 1
    UNION ALL
    SELECT d.id, d.name, d.parent_id, p.lvl + 1
    FROM department d JOIN dept p ON d.parent_id = p.id
    WHERE p.lvl < 10          -- 加层数上限，防止环/无限递归
)
SELECT * FROM dept;

-- 临时调大递归深度（确认是数据量需要，不是死循环）
SET SESSION cte_max_recursion_depth = 5000;

-- 完整树形查询示例：查出 id=1 部门及其所有子孙，带层级和路径
WITH RECURSIVE dept AS (
    SELECT id, name, parent_id, 1 AS lvl, CAST(name AS CHAR(200)) AS path
    FROM department WHERE id = 1
    UNION ALL
    SELECT d.id, d.name, d.parent_id, p.lvl + 1,
           CONCAT(p.path, ' > ', d.name)
    FROM department d JOIN dept p ON d.parent_id = p.id
    WHERE p.lvl < 10
)
SELECT id, name, lvl, path FROM dept ORDER BY lvl;
-- path 列把层级路径拼出来，报表非常直观

加分点：递归 CTE 是查树形/图结构的利器，但永远带一个 lvl 层数列和上限，既防死循环又能看层级。

坑 6：以为崩溃后 DDL 和 5.7 一样留残表

场景 / 错误示范：

-- 老经验：5.7 上 ALTER TABLE 中途崩溃，重启后可能留半张.frm残表，要手动删
-- 升级到 8.0 后还按老办法，崩溃后到处找残表文件

为什么是坑：8.0 实现了原子 DDL（atomic DDL），DDL 操作也写 redo/undo，要么全成功要么全回滚，不会留下半完成的残表。开发者抱着 5.7 的"崩溃要手动清残表"的肌肉记忆，反而做了多余甚至错误的清理操作。

正确做法：

-- 8.0：DDL 是原子的，崩溃重启后自动恢复，表要么改完要么没动
-- 直接正常使用即可，不用手动删 .frm / 残表
ALTER TABLE t ADD COLUMN new_col INT;   -- 崩溃了也会自动回滚干净

加分点：原子 DDL 是 8.0 重要可靠性提升，面试可说"8.0 的 DDL 是事务性的，崩溃不留残表"，体现你真用过。

坑 7：默认字符集变了导致老连接乱码

场景 / 错误示范：

-- 5.7 库默认 latin1，升到 8.0 默认 utf8mb4
-- 老应用连接串没指定字符集，按老习惯以为服务端定
SHOW VARIABLES LIKE 'character_set_server';  -- 8.0 默认 utf8mb4
-- 老数据是 latin1 存的中文，连接字符集一变，读出来全是乱码

为什么是坑：8.0 服务器默认字符集从 latin1 改成 utf8mb4，意味着"连接没指定字符集时"的默认值变了。5.7 时代靠服务端默认凑合的老应用，升级后连接字符集跟着变，和历史数据的存储字符集对不上，中文立刻乱码。

正确做法：

-- 根治：应用连接串里显式指定字符集，不要依赖服务端默认
-- JDBC: jdbc:mysql://host/db?characterEncoding=utf8mb4
-- 排查：逐条核对连接、库、表、列的字符集
SHOW VARIABLES LIKE 'character_set%';
-- 老 latin1 数据转 utf8mb4 要用 CONVERT 正确转换，不能直接改列字符集
ALTER TABLE t MODIFY col VARCHAR(100) CHARSET utf8mb4;

加分点：乱码排查口诀——"连接、库、表、列"四处字符集要一致，任何一处对不上都会乱。连接串永远显式指定，别赌服务端默认。

坑 8：窗口函数在 5.7 老库上直接报错

场景 / 错误示范：

-- 8.0 本地写得很顺的窗口函数，拿到 5.7 老生产库跑
SELECT RANK() OVER (PARTITION BY cls ORDER BY score DESC) FROM score;
-- 5.7 直接语法错误：near '(PARTITION BY ...'

为什么是坑：窗口函数、CTE 都是 8.0 才有的能力。很多公司生产还有 5.7，跨环境部署时语法不兼容。这类错误本地不复现，上线才炸。

正确做法：

-- 部署前先确认版本：SELECT VERSION();
-- 5.7 环境：排名用用户变量模拟（见第 51 章坑 8），递归用自连接/应用层
SET @rk := 0;
SELECT name, score, @rk := @rk + 1 AS rn FROM score ORDER BY score DESC;

加分点：写跨版本 SQL 时，把"依赖 8.0 语法"的部分单独抽出来，部署前在目标库跑一遍语法检查。

坑 9：sql_mode 默认严格化老数据被拒

场景 / 错误示范：

-- 8.0 默认 sql_mode 含 STRICT_TRANS_TABLES、NO_ZERO_DATE、ONLY_FULL_GROUP_BY
-- 老数据里有 '0000-00-00' 这种零日期，INSERT/SELECT 老脚本直接报错
INSERT INTO t(create_time) VALUES ('0000-00-00');
-- 8.0 报错：Invalid datetime format

为什么是坑：5.7 默认 sql_mode 较宽松，允许零日期、截断字符串、分组乱选列。8.0 默认严格模式，这些"脏数据"操作直接被拒。老导入脚本、老 ORM 语句在 8.0 上批量报错，升级时集中爆发。

正确做法：

-- 先看当前模式
SELECT @@sql_mode;
-- 迁移前清理老的零日期脏数据
UPDATE t SET create_time = NULL WHERE create_time = '0000-00-00';
-- 确有兼容需要时，临时调整（不建议长期放宽）：
SET GLOBAL sql_mode = '...';   -- 严格评估后再改

-- 对比：5.7 老模式 vs 8.0 默认模式
-- 5.7 典型：ONLY_FULL_GROUP_BY 可能没开、允许零日期
-- 8.0 默认：STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
-- 升级后先在测试库把老脚本全部跑一遍，把报错逐条列出来：
-- 1) 零日期 -> 改 NULL；2) 截断字符串 -> 放宽列长度；3) GROUP BY 非聚合列 -> 补聚合或 ANY_VALUE()
SELECT @@global.sql_mode;

加分点：升级 8.0 前，先在测试库用老脚本全量跑一遍导入，把 STRICT 报的错集中修掉，别等生产升级当天才暴露。

坑 10：AUTO_INCREMENT 重启后回退

场景 / 错误示范：

-- 5.7 的老坑：InnoDB 自增值存在内存，重启后 SELECT MAX(id)+1，删了最大 id 就回退
-- 升级 8.0 后还担心这个问题

为什么是坑：这其实是 5.7 的坑、8.0 已经修复——8.0 把 AUTO_INCREMENT 计数器持久化为 redo 的一部分，重启后不会回退。但很多老 DBA 还在按 5.7 的经验"别删最大 id"，是过度担心了。反过来，如果你以为 8.0 还会回退，那才是不了解新特性。

正确做法：

-- 8.0：自增值持久化，重启不回退，可放心删除最大 id
DELETE FROM t WHERE id = 100;   -- 下次自增不会回到 100
-- 5.7 环境才需要担心这个问题

加分点：这是面试"5.7 和 8.0 自增差异"的标准答案——8.0 持久化自增计数器，重启不回退。

坑 11：GROUP BY 不再隐含排序

场景 / 错误示范：

-- 老脚本依赖 GROUP BY 的隐式排序结果输出报表
SELECT dept, COUNT(*) FROM emp GROUP BY dept;
-- 5.7 上结果恰好按 dept 升序；8.0 优化器不再保证，顺序乱了，报表排错

为什么是坑：5.7 的 GROUP BY 碰巧会按分组列排序输出（因为要排序分组），老脚本把这个"副作用"当成了正式排序。8.0 优化器可以用哈希分组，不排序，GROUP BY 结果顺序不再保证，依赖它的报表就乱了。

正确做法：

-- 需要排序就显式 ORDER BY，别赌 GROUP BY 的副作用
SELECT dept, COUNT(*) FROM emp GROUP BY dept ORDER BY dept;
-- 这是 ANSI SQL 的正确姿势，5.7/8.0 行为一致

-- 延伸：如果报表要"按分组列排序后取每组前 N"，直接显式 ORDER BY + 窗口函数
SELECT dept, name, sal
FROM (
    SELECT dept, name, sal,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) AS rn
    FROM emp
) t
WHERE rn <= 3
ORDER BY dept, rn;     -- 最终输出顺序也显式锁定

加分点：任何依赖"GROUP BY 输出顺序"的老逻辑，升级 8.0 前都要补显式 ORDER BY。这是升级后报表错乱的高频原因。

速查表：8.0 与 5.7 差异速记

| 特性 | 5.7 | 8.0 | 踩坑点 |

|---|---|---|---|

| utf8 | 阉割 3 字节 | 默认 utf8mb4 | 老脚本手写 utf8 存不了 emoji |

| 认证插件 | mysql_native_password | caching_sha2 | 老驱动连不上 |

| 降序索引 | DESC 被忽略 | 真支持 | 混合排序可免 filesort |

| 隐藏列 | 无 | INVISIBLE | SELECT * 看不到，老 INSERT 报错 |

| 递归 CTE | 无 | WITH RECURSIVE | 忘终止条件死循环 |

| 原子 DDL | 崩溃留残表 | 事务性 DDL | 不用手动清残表 |

| 默认字符集 | latin1 | utf8mb4 | 老连接读老数据乱码 |

| 窗口函数 | 无 | 有 | 5.7 部署直接报错 |

| sql_mode | 宽松 | 严格 | 零日期/脏数据被拒 |

| AUTO_INCREMENT | 重启回退 | 持久化 | 5.7 才需担心回退 |

| GROUP BY | 隐含排序 | 不保证 | 报表顺序乱 |

注意事项：升级 8.0 前的 Checklist

  • [ ] 应用连接串显式指定了字符集吗？（别赌服务端默认）
  • 驱动版本支持 caching_sha2_password 吗？
  • 老脚本里有没有手写 utf8（应改 utf8mb4）？
  • 依赖 GROUP BY 隐式排序的报表补了 ORDER BY 吗？
  • 零日期/截断字符串等脏数据在 STRICT 模式下会不会报错？
  • 用到的窗口函数/CTE 在目标库版本真的支持吗？
  • 崩溃恢复流程按原子 DDL 更新文档了吗（不用手动清残表）？

一句话：8.0 的新特性不是白送的，升级前把"默认值变了"和"语法不兼容"两类坑过一遍，比上线后救火便宜得多。

自测：你了解 8.0 新坑吗

题 1：MySQL 里的 utf8 为什么存不了 emoji？该用什么？

<details>

<summary>参考答案</summary>

utf8 是阉割版，最多 3 字节；emoji 是 4 字节。要用 utf8mb4，排序规则用 8.0 默认的 utf8mb4_0900_ai_ci。

</details>

题 2：8.0 默认认证插件变了，老 JDBC 连不上报插件错误怎么办？

<details>

<summary>参考答案</summary>

8.0 默认 caching_sha2_password，老驱动不支持。升级驱动，或临时把账号改回 mysql_native_password。优先升级驱动。

</details>

题 3：8.0 的降序索引和 5.7 有什么本质区别？

<details>

<summary>参考答案</summary>

5.7 的 DESC 被忽略，索引永远正向存；8.0 真正按指定方向物理存储，ORDER BY a DESC, b ASC 混合方向也能走索引免 filesort。

</details>

题 4：隐藏列对 SELECT * 和不带列名的 INSERT 有什么影响？

<details>

<summary>参考答案</summary>

SELECT * 看不到它，但它真实存在。不带列名的 INSERT 按位置写值会因列数不匹配报错。永远显式列名即可避开。

</details>

题 5：递归 CTE 为什么会死循环？怎么防？

<details>

<summary>参考答案</summary>

数据有环或没层数上限时递归不收敛，直到 cte_max_recursion_depth 报错。加 lvl 层数列和 WHERE lvl < N 的上限。

</details>

题 6：8.0 上 ALTER TABLE 中途崩溃，会留残表吗？

<details>

<summary>参考答案</summary>

不会。8.0 是原子 DDL，DDL 写 redo/undo，崩溃后自动回滚，表要么改完要么没动，不用手动清残表。

</details>

题 7：升级 8.0 后老中文数据读出来乱码，最可能的原因？

<details>

<summary>参考答案</summary>

8.0 默认字符集从 latin1 变 utf8mb4，连接没指定字符集，和历史数据存储字符集对不上。连接串显式指定 utf8mb4，并核对库表列字符集。

</details>

题 8：8.0 默认 sql_mode 严格化，老脚本导入报 Invalid datetime format 为什么？

<details>

<summary>参考答案</summary>

老数据有 '0000-00-00' 零日期，8.0 STRICT_TRANS_TABLES 拒绝。迁移前清理零日期脏数据，别靠放宽 sql_mode 长期解决。

</details>

题 9：8.0 删除自增主键的最大 id，下次自增会回退吗？

<details>

<summary>参考答案</summary>

不会。8.0 把 AUTO_INCREMENT 持久化到 redo，重启不回退。这个回退问题是 5.7 的，8.0 已修复。

</details>

题 10：为什么升级 8.0 后有些 GROUP BY 报表顺序乱了？

<details>

<summary>参考答案</summary>

5.7 的 GROUP BY 隐含排序，老脚本依赖了这个副作用。8.0 可用哈希分组不排序，顺序不保证。必须显式 ORDER BY。

</details>

题 11：8.0 为什么不用再担心"删了最大 id，自增主键回退"？

<details>

<summary>参考答案</summary>

8.0 把 AUTO_INCREMENT 计数器持久化到 redo log，重启后按持久化值继续，不会回到 MAX(id)+1。这个回退问题是 5.7 内存计数导致的，8.0 已修复。

</details>

下一篇预告：第 53 章 事务使用踩坑

— 全文完 —回到顶部 ↑
下载推广海报

文章推广海报

《第 52 章 8.0新特性踩坑》完整推广海报
DISCUSSION

文章回复

0 条公开回复
未登录回复需要审核后公开
还没有回复，欢迎参与讨论。