mysql升级后的字符集与时区设置问题处理

mysql 8.0升级后字符集与时区需手动配置：server端须在my.cnf中显式设utf8mb4和default-time-zone，客户端连接须指定charset=utf8mb4及时区，否则仍用旧值导致乱码或时间错误。

升级后 SHOW VARIABLES LIKE 'character%' 显示 utf8 而不是 utf8mb4

MySQL 5.7 升级到 8.0 后，character_set_server 和 collation_server 默认值仍可能是 utf8（即 utf8mb3），这不是你手动改的，是旧配置残留或启动时未显式指定导致的。MySQL 8.0 虽默认支持 utf8mb4，但不会自动覆盖已有配置文件中的旧值。

实操建议：

• 检查 my.cnf 或 my.ini 中是否显式写了 character-set-server = utf8 —— 必须改为 utf8mb4
• 确认 [mysqld] 和 [client] 两个段落都设置了：

  [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_0900_ai_ci  [client] default-character-set = utf8mb4

• 重启 MySQL 后，执行 select @@character_set_server, @@collation_server; 验证；注意：仅改配置不重启无效，且连接客户端（如 MySQL Shell、navicat）也需明确声明 charset=utf8mb4

SELECT NOW() 返回时间与系统时区不符，且 time_zone 变量显示 SYSTEM

MySQL 升级后，time_zone 默认仍为 SYSTEM，意味着它直接读取操作系统的时区设置。但很多 linux 发行版升级后会重置 /etc/localtime 链接，或容器环境里未挂载时区文件，导致 MySQL 实际用的是 UTC 而非预期时区（如 Asia/Shanghai）。

实操建议：

• 先查系统时区：timedatectl status | grep "Time zone"，再查 MySQL 是否同步：SELECT @@global.time_zone, @@session.time_zone;
• 不要依赖 SET time_zone = '+8:00' 临时修改——这仅对当前会话有效，且易被应用层覆盖
• 在 [mysqld] 段落中强制指定：default-time-zone = '+08:00' 或 default-time-zone = 'Asia/Shanghai'（后者需确保 mysql.time_zone* 表已填充，可通过 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql 初始化）
• 若用 docker，记得加 -v /etc/localtime:/etc/localtime:ro 并在配置中设 default-time-zone=SYSTEM 才真正生效

升级后原有表字段仍是 utf8 排序规则，ALTER table ... CONVERT TO 报错

即使服务器字符集已切为 utf8mb4，已有表的列、索引、存储过程等元数据不会自动升级。直接执行 ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 可能失败，常见原因包括：索引长度超限（VARCHAR(255) 在 utf8mb4 下实际占 1020 字节，InnoDB 单索引限制 767/3072 字节）、存在生成列或全文索引、或使用了不兼容的旧排序规则（如 utf8_general_ci）。

实操建议：

• 先检查表结构：SHOW CREATE TABLE tG，重点关注 COLLATE 和索引定义
• 对含长文本字段的表，分步操作：

  ALTER TABLE t MODIFY c1 VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

• 若报错 “Specified key was too long”，说明索引键超限，需提前缩减字段长度或启用 innodb_large_prefix=ON（MySQL 5.7+ 默认开启，8.0 已移除该参数，改由 innodb_file_format=Barracuda + ROW_FORMAT=DYNAMIC 控制）
• 避免在业务高峰期执行全表转换，尤其是大表；可考虑用 pt-online-schema-change 降低锁影响

应用连接后插入中文乱码，但 SET NAMES utf8mb4 临时有效

这说明客户端连接层未正确协商字符集。MySQL 8.0 默认要求握手阶段就声明 utf8mb4，而老版本驱动（如某些 java 的 mysql-connector-java 5.x、php 的 mysqli 未设 charset 参数）可能仍按 utf8 握手，导致后续 SET NAMES 无法逆转连接初始编码状态。

实操建议：

• Java 应用：URL 中必须显式加 ?characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai，且驱动版本 ≥ 8.0.23（修复了部分时区与编码组合 bug）
• python（PyMySQL）：创建连接时传参 charset='utf8mb4'；SQLAlchemy 则在 URL 后加 ?charset=utf8mb4
• node.js（mysql2）：配置项写 charset: 'utf8mb4'，不是 'UTF8' 或 'utf8'
• 验证连接实际编码：SHOW VARIABLES LIKE 'character_set_client'; SHOW VARIABLES LIKE 'character_set_connection'; —— 二者必须都是 utf8mb4，否则乱码不可避免

字符集和时区不是“设完就完”的静态配置，它们贯穿连接建立、查询解析、结果返回全过程。最容易被忽略的是客户端驱动的默认行为 —— 很多问题表面看是服务端没配好，实际是连接一建立，编码就已经错了。