mysql在版本升级后的测试验证与应用场景

升级后必须立刻验证的 5 类 SQL 行为：GROUP BY 严格模式、ORDER BY 表达式限制、隐式类型转换报错变化、JSON 函数新语法兼容性、窗口函数执行权限；同时需检查字符集排序规则、认证插件、性能执行计划及存储过程兼容性。

升级后必须立刻验证的 5 类 SQL 行为

MySQL 版本升级（如从 5.7 升到 8.0）后，GROUP BY、ORDER BY、隐式类型转换、窗口函数支持、JSON 函数行为都可能变化。不验证这些，线上查询会突然报错或返回错结果。

重点验证场景：

• SELECT col1, COUNT(*) FROM t GROUP BY col2：8.0 默认启用 sql_mode=ONLY_FULL_GROUP_BY，若 col1 不在 GROUP BY 中且无聚合，直接报错
• ORDER BY 含表达式时（如 ORDER BY col1 + 1），8.0 要求该表达式必须出现在 SELECT 列表中，否则报错
• 使用 CAST('2023-01-01' AS DATETIME) 等隐式转换：8.0 对无效日期更严格，可能从警告变成错误
• 原用 JSON_EXTRACT(json_col, '$.name') 的地方，8.0 支持更短写法 json_col->'$.name'，但旧写法仍可用；需确认 ORM 或中间件是否兼容新语法
• 含 ROW_NUMBER()、RANK() 的查询：5.7 不支持，升级后首次执行可能因权限不足失败——需检查用户是否有 SELECT 权限外的 EXECUTE 权限（部分版本要求）

测试数据必须覆盖字符集与排序规则变更

MySQL 8.0 默认字符集从 latin1 变为 utf8mb4，默认排序规则从 latin1_swedish_ci 变为 utf8mb4_0900_ai_ci。这直接影响 WHERE 匹配、GROUP BY 分组、索引使用效率。

实操建议：

• 导出升级前库的 SHOW CREATE TABLE t，对比升级后建表语句，确认 CHARSET 和 COLLATE 是否被自动修改
• 对含中文、emoji、大小写混合字段的表，执行 SELECT * FROM t WHERE name = '张三' COLLATE utf8mb4_0900_as_cs 测试大小写敏感匹配是否符合预期
• 检查联合索引字段顺序是否因排序规则变更导致索引失效——例如原 (a VARCHAR(10), b INT) 索引，在 utf8mb4_0900_ai_ci 下 WHERE a = ? AND b > ? 仍能走索引；但若 a 改用 _as_cs 排序规则，可能触发全表扫描

权限系统变更带来的应用连接失败

MySQL 8.0 默认认证插件从 mysql_native_password 改为 caching_sha2_password。很多老客户端（如某些 Python MySQLdb 驱动、PHP 7.2 以下 mysqli、Node.js mysql2 低版本）不支持该插件，连接时直接报错：Plugin caching_sha2_password could not be loaded。

临时解决方式（仅限测试环境）：

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password123';

长期方案：

• Java 应用：升级 mysql-connector-java 至 8.0.15+
• Python 应用：改用 pymysql 或升级 mysqlclient 至 2.0+，并确保连接参数含 auth_plugin='mysql_native_password'
• Docker 环境：启动容器时加 --default-authentication-plugin=mysql_native_password 参数，避免影响现有 CI 流程

性能回归测试不能只看 QPS

升级后 QPS 没掉，不代表没问题。8.0 引入了直方图统计、哈希连接、降序索引等特性，但也会因优化器成本模型变化，让某些原本走索引的查询改走全表扫描。

必须做的三件事：

• 对核心慢查询，升级前后分别执行 EXPLAIN FORMAT=TREE（8.0 新格式），比对访问类型（access_type）、是否使用索引（key）、是否用到物化（materialized）
• 开启 performance_schema 并采集 1 小时内 top 20 查询的 digest_text 和 avg_timer_wait，对比升级前后延迟分布
• 特别关注 INSERT …… ON DUPLICATE KEY UPDATE 类语句：8.0 在高并发下锁行为更激进，容易出现 Lock wait timeout exceeded，需结合 INFORMATION_SCHEMA.INNODB_TRX 查看事务等待链

实际升级中最容易被跳过的，是存储过程和触发器里的 SQL 写法兼容性——它们往往藏在 DBA 不常看的角落，却会在某个定时任务里突然崩掉。