mysql升级过程中处理SQL兼容性与语法变动问题_技术教程

MySQL 8.0升级后GROUP BY报错源于默认启用ONLY_FULL_GROUP_BY模式，需重写SQL使SELECT列全在GROUP BY中或用聚合函数包裹，而非简单关闭该模式。

MySQL 8.0 升级后 `GROUP BY` 报错：Expression #1 of SELECT list is not in GROUP BY clause

这是升级到 MySQL 8.0 后最常遇到的报错，根源是默认启用了 sql_mode=ONLY_FULL_GROUP_BY。旧版本（如 5.7）即使关闭该模式也可能因配置遗漏而“侥幸通过”，但 8.0 默认严格校验。

检查当前模式：
```
SELECT @@sql_mode;
```
确认是否含 ONLY_FULL_GROUP_BY

临时绕过（仅测试环境）：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

长期方案不是关掉它，而是重写 SQL：所有 SELECT 列必须在 GROUP BY 中显式出现，或用聚合函数包裹，例如：
```
SELECT user_id, MAX(name), COUNT(*) FROM orders GROUP BY user_id;
```
而非 SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id
注意 ORM 框架（如 Django、MyBatis）自动生成的 SQL 可能隐含非确定性字段，需配合框架配置或手动重写查询

`mysql_native_password` 认证插件被弃用导致连接失败

MySQL 8.0 默认使用 caching_sha2_password 插件，老客户端（尤其是 MySQL 5.7 客户端、某些 JDBC 驱动旧版、PHP mysqli 扩展）不支持，会报错 Client does not support authentication protocol requested by server。

确认用户认证方式：

SELECT user, host, plugin FROM mysql.user WHERE user = 'your_user';

降级插件（兼容性优先）：

ALTER USER 'your_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; FLUSH PRIVILEGES;

更推荐升级客户端驱动：JDBC 用 mysql-connector-java:8.0.28+，Python 用 PyMySQL >= 1.0.2 或 mysqlclient >= 2.1.0，并确保连接字符串中指定 ?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false（视环境而定）
不要在生产环境全局修改 default_authentication_plugin，只针对具体用户调整

系统表、保留字与函数行为变更引发隐性错误

MySQL 8.0 新增大量保留字（如 ROLE、RESOURCE_GROUP），且部分内置函数语义收紧，容易在无报错情况下返回错误结果。

检查建表语句中是否误用新保留字作列名或别名，例如：SELECT role FROM users 在 8.0 中需写成 SELECT `role` FROM users
JSON_EXTRACT 返回值类型更严格：以前可能自动转为字符串，现在返回 JSON 类型，WHERE JSON_EXTRACT(data, '$.status') = 'active' 应改为 WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.status')) = 'active' 或用 ->> 操作符：WHERE data->>'$.status' = 'active'
INFORMATION_SCHEMA 表结构有调整（如 TABLES 表新增 CREATE_OPTIONS 字段），依赖硬编码字段顺序的脚本会出错，务必用字段名而非序号访问
执行 mysqldump --compatible=mysql40 不再能解决语法兼容问题，应改用 --skip-triggers --no-create-info
分离逻辑，人工校验触发器和 DDL

升级前必须做的 SQL 兼容性扫描

不能靠“升级后再修”，必须前置识别风险点。官方 mysqlsh 的 util.checkForServerUpgrade() 是基础，但覆盖有限；需补充人工规则。

用正则批量扫描 SQL 文件（含应用代码、存储过程、迁移脚本）：

grep -n -E "(GROUP\s+BY.*[^,]\s+[^[:space:]]+[^,]|ORDER\s+BY.*\s+NULLS\s+(FIRST|LAST)|CREATE\s+USER.*IDENTIFIED\s+WITH)" *.sql *.py *.java

重点关注：含 NULLS FIRST/LAST（8.0 支持但旧版不识别）、CREATE USER ... IDENTIFIED WITH（语法存在但插件名不兼容）、未加反引号的保留字、USING BTREE 在分区表中的位置变化
在测试库启用 log_error_verbosity = 3 并开启 general_log，回放业务流量，捕获所有 warning 级别提示——很多兼容性问题只报 warning 不报 error
特别注意时间函数：NOW(3) 在 5.7 是毫秒精度，在 8.0 是微秒精度，若业务依赖固定小数位，需显式截断或改用 SYSDATE(3)