MySQL 字符集与时区，是生产库里最典型的两类「隐性坑」：前者让带 emoji 的昵称直接写入失败、或者写进去读出来是问号；后者让报表时间整体偏移 8 小时。它们不会让服务立刻崩溃，却会持续污染数据，等业务发现时往往已经写坏了几十万行。本文复盘一次真实排查，按「现象 → 分层定位 → 修复 → 上线 Checklist」给出可直接执行的 SQL 与配置。
故障现场：两个看似无关的报障
同一个下午收到两条工单。一条来自客户端：用户改昵称时带了一个 emoji，接口返回 500，服务端日志是 java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80'。另一条来自运营：昨天导出的订单报表里，凌晨 2 点的单子全变成了前一天上午 10 点，整整差 8 小时。
两条工单指向同一个根因家族——字符集和时区都不是「一个配置项」，而是一条从操作系统到 JDBC 连接的多层继承链。任何一层没对齐，都会在最末端表现为乱码或时间偏移。排查的第一步不是改配置，而是把每一层的实际值都打出来。
字符集的四层继承：server / database / table / column
MySQL 的字符集是逐层继承、就近覆盖的：建库不指定就继承 server，建表不指定就继承库，建列不指定就继承表。所以「my.cnf 里已经写了 utf8mb4」完全不代表老表就是 utf8mb4。定位必须查到列这一级。
-- 1. 服务端全局与连接层实际生效值（重点看 character_set_client/connection/results）
SHOW VARIABLES LIKE 'character\_set\_%';
SHOW VARIABLES LIKE 'collation\_%';
-- 2. 库级别
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'order_db';
-- 3. 表级别：一次列出所有非 utf8mb4 的表
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'order_db' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';
-- 4. 列级别：真正存数据的那一层
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'order_db' AND CHARACTER_SET_NAME IS NOT NULL
AND CHARACTER_SET_NAME <> 'utf8mb4';本次排查的结果是：server 和新建的库都是 utf8mb4，但 user_profile 是 2021 年建的老表，表和 nickname 列都还是 utf8（也就是 utf8mb3）。
坑一：MySQL 的 utf8 是「假 utf8」
MySQL 历史上的 utf8 实际是 utf8mb3，每个字符最多 3 字节，装不下需要 4 字节的 emoji、部分生僻汉字和一些少数民族文字。写入时 MySQL 不会静默截断，而是直接抛 Incorrect string value——这就是昵称接口 500 的原因。
| 对比项 | utf8 (utf8mb3) | utf8mb4 |
|---|---|---|
| 单字符最大字节 | 3 | 4 |
| emoji / 4 字节汉字 | 写入报错 | 正常 |
| 默认排序规则 | utf8_general_ci | MySQL 8 起 utf8mb4_0900_ai_ci |
| VARCHAR 索引最大字符数 （单列索引 3072 字节） | 1024 | 768 |
| 建议 | 不要再用于新表 | 新库唯一选择 |
改造：CONVERT TO 而不是只改默认字符集
# 只改表默认字符集：新增列生效，已有列不动 —— 这是最常见的误操作
ALTER TABLE user_profile DEFAULT CHARACTER SET utf8mb4;
# 正确做法：连已有列一起转换
ALTER TABLE user_profile
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
# 转换前先估算索引长度风险：utf8mb4 下 varchar(1000) 会超 3072 字节
SELECT TABLE_NAME, INDEX_NAME, SUM(SUB_PART_OR_LEN) AS bytes_est
FROM (
SELECT s.TABLE_NAME, s.INDEX_NAME,
COALESCE(s.SUB_PART, c.CHARACTER_MAXIMUM_LENGTH) * 4 AS SUB_PART_OR_LEN
FROM information_schema.STATISTICS s
JOIN information_schema.COLUMNS c
ON c.TABLE_SCHEMA = s.TABLE_SCHEMA AND c.TABLE_NAME = s.TABLE_NAME
AND c.COLUMN_NAME = s.COLUMN_NAME
WHERE s.TABLE_SCHEMA = 'order_db' AND c.CHARACTER_SET_NAME IS NOT NULL
) t
GROUP BY TABLE_NAME, INDEX_NAME HAVING bytes_est > 3072;CONVERT TO 会重建整张表，千万级大表在业务高峰执行等于自杀。生产上一律走在线 DDL 工具，参考在线改大表实战：pt-osc 与 gh-ost 零停机；改造语句本身要进版本化迁移，别在客户端手敲，做法见数据库迁移实战：Flyway 与 Liquibase 版本化管理。
坑二：连接层不一致，写进去是好的、读出来是乱的
如果表已经是 utf8mb4，数据却还是乱码，问题几乎一定在连接层。MySQL 在客户端与服务端之间会按 character_set_client → character_set_connection → 列字符集 做两次转码，读取时再按 character_set_results 转回去。中间任何一环被设成 latin1，都会造成不可逆的字节损坏。
# 服务端：my.cnf 一次性钉死，避免依赖客户端自觉
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
skip_character_set_client_handshake = ON # 忽略客户端声明，强制用服务端字符集
[client]
default-character-set = utf8mb4
# 会话内临时确认（三个变量应同时为 utf8mb4）
SET NAMES utf8mb4;
SHOW VARIABLES LIKE 'character_set_client';
# JDBC：MySQL Connector/J 8.x 默认已是 utf8mb4，但被显式写坏的情况很多，检查这一行
jdbc:mysql://db:3306/order_db?characterEncoding=utf8&useUnicode=true&connectionCollation=utf8mb4_0900_ai_ci判断「数据坏没坏」有一个可靠办法：用 SELECT HEX(nickname) 看原始字节。如果字节本身是合法 UTF-8 序列，只是显示乱，那属于读取端转码问题，改连接即可；如果字节已经是二次编码的产物，就只能靠 binlog 或备份回滚，恢复手法见MySQL 备份恢复实战：全量备份与 binlog 恢复。
坑三：排序规则混用，JOIN 直接不走索引
字符集对齐了，排序规则（collation）也可能不齐——尤其是一半表从 MySQL 5.7 迁上来带着 utf8mb4_general_ci，新表是 utf8mb4_0900_ai_ci。两者 JOIN 时会报 Illegal mix of collations，或者更阴险：MySQL 隐式转换后放弃索引，全表扫描。
-- 症状：type=ALL、key=NULL，明明两边都有索引
EXPLAIN SELECT o.id FROM orders o
JOIN user_profile u ON o.user_no = u.user_no
WHERE u.status = 1;
-- 临时兜底（能跑通，但仍然不走索引，只当救火）
... ON o.user_no = u.user_no COLLATE utf8mb4_0900_ai_ci
-- 根治：把落后的一方统一过来
ALTER TABLE user_profile
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;这类「索引突然失效」的排查思路，和慢 SQL 打满 CPU 是一套方法论，可对照慢 SQL 拖垮生产库：一次 CPU 100% 排查实录与MySQL 死锁排查实录：从日志定位到事务优化一起看。
时区的三层：OS → MySQL → 会话 → 驱动
时间差 8 小时的排查同理，先把每一层的实际值打出来，不要凭印象。注意 SYSTEM 这个值本身不说明任何东西，它只是「跟随操作系统」。
-- MySQL 侧：global 与 session 时区可能不同
SELECT @@global.time_zone, @@session.time_zone,
NOW() AS now_session, UTC_TIMESTAMP() AS now_utc,
TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS offset_check;
-- 期望：offset_check = 08:00:00（East 8）；若为 00:00:00 则库在跑 UTC
-- 操作系统侧
$ date +"%Z %z" # 期望 CST +0800
$ timedatectl | head -3 # 容器里常见 UTC
-- 应用侧（Java）：JVM 默认时区也要对齐，否则同一时刻两边算出不同字符串
System.out.println(java.time.ZoneId.systemDefault());本次的实际情况是：ECS 主机是 CST，MySQL 容器镜像默认 UTC，而应用容器加了 TZ=Asia/Shanghai。应用把「2026-09-03 02:00」按东八区理解写入 DATETIME 列，读取时又用 UTC 解释，一来一回就成了差 8 小时。
DATETIME 与 TIMESTAMP：行为完全不同
| 维度 | DATETIME | TIMESTAMP |
|---|---|---|
| 存储本质 | 字面量，不带时区 | UTC 秒数 |
| 读写是否按时区换算 | 不换算，存啥读啥 | 按会话时区自动换算 |
| 改时区后旧数据表现 | 不变（但含义变了） | 显示值随之平移 |
| 范围 | 1000–9999 年 | 1970–2038 年 |
| 多时区业务建议 | 存 UTC，展示层换算 | 可用，但注意 2038 |
坑四：差 8 小时的四种成因与定位顺序
把上面的输出对照下表，基本一查就能定位到具体哪一层。定位顺序建议自下而上：先 OS，再 MySQL global/session，最后驱动与 JVM。
| 现象 | 判定依据 | 处置 |
|---|---|---|
| 库整体慢 8 小时 | TIMEDIFF(NOW(),UTC_TIMESTAMP()) 为 00:00:00 | my.cnf 设 default-time-zone='+08:00' |
| 只有某个应用偏移 | 该应用连接的 @@session.time_zone 异常 | 检查驱动 URL 与连接池初始化 SQL |
| 写入正确、读出偏移 | 列类型是 TIMESTAMP | 统一读写两侧会话时区 |
| 容器重建后突然出现 | 镜像内 date 为 UTC | 镜像里装 tzdata 并设 TZ |
坑五：容器里没有时区数据库
把 time_zone 设成 Asia/Shanghai 而不是 +08:00 时，MySQL 需要查 mysql.time_zone_name 表。官方精简镜像默认没导入这些数据，设置会直接报 Unknown or incorrect time zone。两条路：要么用固定偏移，要么把系统时区库导进去。
# 方案 A：固定偏移，最省事，不受 tzdata 影响（推荐用于单一时区业务）
[mysqld]
default-time-zone = '+08:00'
# 方案 B：导入命名时区（需要夏令时/多时区换算时才必要）
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql
$ mysql -uroot -p -e "SET GLOBAL time_zone='Asia/Shanghai';"
# 容器镜像：时区数据 + TZ 环境变量一起给，缺一不可
FROM mysql:8.4
ENV TZ=Asia/Shanghai
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone顺带提醒：连接池的 connectionInitSql 也是常被忽略的一层，Spring Boot 升级过程中这类配置迁移遗漏很常见，可参考Spring Boot 3 升级踩坑实录：Jakarta、Security 6 与 7 大雷区。
新库标准配置模板
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
skip_character_set_client_handshake = ON
default-time-zone = '+08:00'
explicit_defaults_for_timestamp = ON
[client]
default-character-set = utf8mb4
-- 建库建表一律显式声明，别赖继承
CREATE DATABASE order_db
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE user_profile (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_no VARCHAR(32) NOT NULL,
nickname VARCHAR(64) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE KEY uk_user_no (user_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;跨数据库迁移时这两类问题会成倍放大，异构库的语义差异可对照PostgreSQL vs SQL Server：语法差异与迁移避坑指南；PostgreSQL 侧的 timestamptz 与执行计划细节见PostgreSQL 慢查询优化：执行计划解读与索引调优实战。
上线前 8 条 Checklist
- 用 information_schema 扫一遍，确认没有列级别残留 utf8mb3。
- 全库 collation 统一，不允许 general_ci 与 0900_ai_ci 混用。
- my.cnf 开启
skip_character_set_client_handshake，不信任客户端声明。 - 连接串、连接池初始化 SQL、ORM 配置三处的字符集口径一致。
TIMEDIFF(NOW(), UTC_TIMESTAMP())作为巡检项，纳入监控告警。- 应用容器与数据库容器都显式设置
TZ，禁止依赖镜像默认。 - DATETIME 与 TIMESTAMP 在一个项目里只选一种口径并写进规范。
- 大表 CONVERT 一律走在线 DDL，且先在预发全量演练一遍。
小结
字符集和时区的坑，本质都不是「配错了一个值」，而是「多层配置各自为政，没有人做过一致性校验」。排查的核心动作只有一个：把 OS、服务端、库、表、列、会话、驱动这几层的实际值全部打出来对齐，而不是相信文档或印象。修完之后，把 TIMEDIFF 巡检和字符集扫描做成定期检查，才能保证下一次建表、下一次换镜像时不会重新掉进同一个坑。
