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。

Codearts代码智能体

华为云Codearts平台推出的AI编码智能体

下载

• 确认用户认证方式：

  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)