mysql数据库从低版本迁移到高版本的注意事项

mysql 5.7 升级到 8.0 需重点处理 sql_mode 严格模式、权限系统重构（caching_sha2_password）、字符集 utf8mb4 默认变更、jsON 函数行为差异四大兼容性问题。

MySQL 5.7 升级到 8.0 时 sql_mode 不兼容导致插入失败

MySQL 8.0 默认启用严格模式（STRICT_TRANS_TABLES），而 5.7 很多部署会关闭或仅启用部分模式。升级后常见报错：Error 1364 (HY000): Field doesn't have a default value 或 ERROR 1292 (22007): Incorrect datetime value。

• 检查当前模式：select @@sql_mode;
• 临时兼容：启动时加参数 --sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"，但不推荐长期使用
• 更稳妥做法：在升级前导出数据时用 mysqldump --compatible=mysql40（仅部分兼容），或升级后执行 SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; 并逐表修复默认值和 NOT NULL 字段
• 注意：修改 sql_mode 后需重启连接才生效，已有连接不受影响

用户权限表结构变更引发连接拒绝

MySQL 8.0 彻底重构了权限系统，mysql.user 表字段大幅调整（如移除 Password 列，改用 authentication_string；plugin 默认从 mysql_native_password 变为 caching_sha2_password）。旧客户端（如 MySQL 5.7 客户端、某些 JDBC 驱动）可能无法认证。

• 升级后立即执行：ALTER USER 'your_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
• 确认插件类型：SELECT User, Host, plugin FROM mysql.user WHERE User = 'your_user';
• 若应用使用 JDBC，需升级驱动至 mysql-connector-java:8.0.28+，并在连接串中显式指定：?serverTimezone=UTC&allowPublicKeyRetrieval=true&usessl=false
• 不要直接复制旧版 mysql 系统库文件覆盖——8.0 的 mysql 库必须由 mysqld --initialize 或 mysql_upgrade（已废弃）生成

mysqldump 导出再导入时字符集乱码

5.7 常用 utf8（实际是 utf8mb3），而 8.0 默认字符集改为 utf8mb4，且排序规则（collation）默认从 utf8mb4_general_ci 升级为 utf8mb4_0900_ai_ci。直接导入可能触发警告甚至截断中文、emoji。

• 导出时强制指定：mysqldump --default-character-set=utf8mb4 --skip-set-charset -u root -p database_name > dump.sql
• 导入前在 SQL 文件头部手动添加：

  SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

• 检查表定义是否含 CHARACTER SET utf8 或 COLLATE utf8_general_ci，批量替换为 utf8mb4 和 utf8mb4_0900_ai_ci
• 升级后运行：SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'your_db'; 确认库级默认值

json 字段函数语法差异与性能退化

MySQL 8.0 对 JSON 函数做了大量增强（如支持 JSON_TABLE、路径通配符 $**），但也引入了不兼容行为：例如 JSON_EXTRACT 在 5.7 返回字符串带引号，8.0 默认返回去引号值；JSON_CONTAINS 对数字比较更严格。

• 测试关键查询：把含 JSON_EXTRACT(col, '$.field') 的语句改成 JSON_UNQUOTE(JSON_EXTRACT(col, '$.field')) 确保字符串一致性
• 避免在 WHERE 中对 JSON 字段用函数索引（如 WHERE JSON_EXTRACT(data, '$.id') = 123），应改用生成列 + 普通索引：

  ALTER TABLE t ADD COLUMN data_id INT AS (JSON_EXTRACT(data, '$.id')) STOred;

• 检查慢日志中是否有新增的 JSON_CONTAINS_PATH 或 JSON_OVERLAPS 调用——这些函数在大数据量下可能比等值查询慢数倍

升级不是“dump + restore”就完事。最常被跳过的一步是：在目标 8.0 实例上用生产流量回放（mysqlslap 或 pt-query-digest + tcpdump）验证所有业务 SQL 的执行计划和响应时间。尤其注意视图、存储过程里隐式类型转换和 GROUP BY 行为变化。