MySQL 的时区、字符集与 sql_mode：三个最容易在半夜出问题的设置

2026-03-1016 分钟读完0 次浏览

DATETIME 与 TIMESTAMP 的时区语义、驱动 loc 参数造成的差 8 小时、utf8mb4 与 emoji、表名大小写敏感的迁移事故、only_full_group_by 与严格模式。

2026 年 3 月的一个凌晨，维护脚本给我推了条消息：后台订单列表里，前一天晚上 22:40 之后下的单全被算进了第二天——按日期分组的日报里，这批订单整整晚了一天。白天看不出来，因为差 8 小时只在跨越零点的那两个小时里表现成"日期不对"，其余时间只是"时间显示得有点怪"，而没人会盯着一个看起来合理的数字。

同一周还撞上另外两件事：一个用户把昵称改成带 emoji 的，接口直接 500，日志里是 Incorrect string value；另一次是数据库从本地 Docker 换成云上实例后，一个跑了半年的统计 SQL 突然报 Expression #2 of SELECT list is not in GROUP BY clause。三件事看着毫无关系，根子都在同一类设置上：时区、字符集、sql_mode 这三样东西平时不声不响，出问题一定是在半夜或者上线那一刻。这篇把过程记下来，最后给一份新库初始化的清单。

一、先学会读事实：到底是谁在换算时区

排查时间问题，第一步不是看代码，是在出问题的那个连接上问数据库几句：

SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone,
       NOW(), UTC_TIMESTAMP(), CURRENT_TIMESTAMP;

四个变量各有分工：

@@global.time_zone = SYSTEM 表示"用系统时区"，此时真正的值来自 @@system_time_zone；
@@system_time_zone 是 mysqld 启动时读到的操作系统时区名。容器里它常常是 UTC，宿主机上可能是含糊的 CST（这个缩写既可能是中国标准时间也可能是美国中部时间，看到它千万别猜）；
NOW() 按 session 时区 换算，UTC_TIMESTAMP() 永远是 UTC。同一连接里把这两个并排打出来，差值就是"这个连接认为的时区偏移"，比看变量名快得多。

然后是那张必须记住语义差异的表：

	`DATETIME`	`TIMESTAMP`
存的是什么	字面墙钟时间，不带时区	内部存 UTC 秒数
读写的换算	不做任何换算，写什么读什么	写入按 session 时区转成 UTC，读出再转回 session 时区
范围	1000-01-01 ~ 9999-12-31	1970-01-01 ~ 2038-01-19
默认值行为	需要显式写 `DEFAULT CURRENT_TIMESTAMP`	老版本会自动给 `NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`
适合	业务时间（下单、支付、事件发生时刻）	纯粹的"记录写入时刻"，且能接受 2038 上限

一句话概括：DATETIME 的时区语义由"读写它的那个人"决定，TIMESTAMP 的时区语义由"session 时区"决定。前者把锅甩给应用，后者把锅甩给连接——两边的锅都得有人背。

二、差 8 小时是怎么来的：连接串里的 loc

我们的服务是 Go，DSN 长这样：

root:pwd@tcp(127.0.0.1:3306)/orders?parseTime=true&loc=Local&charset=utf8mb4

parseTime=true 让驱动把 DATETIME/TIMESTAMP 解析成 time.Time；而 loc 决定两件事：读出来的字面量按哪个时区理解，写入 time.Time 时按哪个时区格式化。go-sql-driver 的默认值是 loc=UTC，不是本地时区——这个默认值很多人不知道。

于是"差 8 小时"通常是下面三条路径之一：

一段代码用 loc=Local，另一段用默认的 UTC。同一个 DATETIME 字面量 2026-03-09 14:40:00，一边当成北京时间、一边当成 UTC，两个 time.Time 的绝对时刻差了 8 小时。单服务自查永远正常，只有跨服务传时间、或者写进同一张表之后才暴露。
一半写入来自应用，一半来自数据库。created_at DATETIME DEFAULT CURRENT_TIMESTAMP 和 SQL 里直接写的 NOW()，取的是 session 时区；应用传的 time.Time 取的是驱动 loc。两条路混在一张表里，同一列就有了两种时区。
TIMESTAMP 列 + 不同 session 时区。应用连接（session 时区可能是 SYSTEM=UTC）和 DBA 用客户端看（客户端设了 +08:00）看到的字面量不一样，于是"数据库里明明是 22:40，代码读出来是 14:40"。

所以我的结论是一条硬约定：表里的时间列统一存 UTC 墙钟时间，连接串显式写 loc=UTC，只在展示层转成本地时区。不用 loc=Local，因为本地开发机是 +08:00、容器里是 UTC，同一份代码在两个环境算出两个值，这种 bug 最难查。判断线上到底是哪种情况，一个土办法很好用：让应用把"它认为的当前时间"和 SELECT NOW(), UTC_TIMESTAMP() 的结果打在同一个日志行里，偏移量立刻现形。

三、SET time_zone 的三种设法规格

改时区有三种写法，作用范围完全不同，我按场景排一下：

-- 1) 只影响当前连接：连接池里每个新连接都要执行一次
SET SESSION time_zone = '+08:00';

-- 2) 影响之后建立的新连接，重启即失效
SET GLOBAL time_zone = '+08:00';

-- 3) 持久化：写在 my.cnf 的 [mysqld] 段，重启生效
-- default-time-zone = '+08:00'

三个容易踩的点：

SET GLOBAL 需要 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限；更关键的是它不影响已经存在的连接。线上应用全是长连接，改完 GLOBAL 之后不重启/不断连接，问题照旧。
偏移写法 '+08:00' 不需要额外数据；写成 'Asia/Shanghai' 这类命名时区则要求 mysql 库里的 time_zone* 表被填充过，否则报 ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Shanghai'。填充命令是 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql。
偏移写法不带夏令时规则。中国没有夏令时，用 +08:00 没问题；给有夏令时的地区服务，老老实实用命名时区加偏移写法兜底。

四、字符集：`utf8` 只有三个字节

emoji 那个 500 的报错长这样：

Incorrect string value: '\xF0\x9F\x98\x80' for column 'nickname' at row 1

原因是 MySQL 里的 utf8 是 utf8mb3 的别名，最多 3 字节，而 emoji 在 UTF-8 里占 4 字节，需要一个 4 字节的字符集：utf8mb4。\xF0\x9F... 这种开头的字节序列，看到就该直接想到"四字节字符 + 三字节字符集"。

改的时候要注意字符集有四层，只改一层没用：

SHOW VARIABLES LIKE 'character_set%';   -- server / database / client / connection / results
SHOW FULL COLUMNS FROM user LIKE 'nickname';

ALTER DATABASE orders CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SET NAMES utf8mb4;                       -- 连接层；DSN 里的 charset=utf8mb4 就是在做这件事

库和表/列都要改，ALTER DATABASE 不会修改已有列的字符集；
连接层由连接串决定。Go 的 DSN 加 charset=utf8mb4&collation=utf8mb4_0900_ai_ci，或连接初始化时 SET NAMES utf8mb4。历史数据没问题、只有新写入报错，多半是连接层没改。

排序规则（collation）比字符集更容易出隐性事故：

排序规则	出现版本	特点
`utf8mb4_general_ci`	5.7 默认	排序粗糙，`ai_ci`：重音与大小写都不敏感
`utf8mb4_unicode_ci`	5.x / MariaDB	基于较早的 Unicode 规则，MariaDB 常用
`utf8mb4_0900_ai_ci`	MySQL 8.0 默认	基于 Unicode 9.0，排序更"正确"，MariaDB 里不存在

由此带来两个真实问题：一是从 5.7 升到 8.0 之后"列表顺序变了"、"SELECT DISTINCT 结果少了一条"是预期行为，不是 bug；二是 mysqldump 出来的建表语句里带着 COLLATE=utf8mb4_0900_ai_ci，灌进 MariaDB 会直接报 Unknown collation: 'utf8mb4_0900_ai_ci'——脚本化迁移时要在导出/导入环节统一替换。另外，两列 collation 不一致做 JOIN 会报 Illegal mix of collations，所以新库我只认"库级默认 + 列不单独指定"这一条。

五、lower_case_table_names：一次迁移到 Linux 就炸

Windows 默认 1（表名转小写后存储、也按小写比较）、macOS 默认 2（原样存储但比较时不区分大小写）——两者表现一致，SELECT * FROM UserOrder 和 userorder 都跑得通；
Linux 默认 0：表名原样存储，比较区分大小写。

我们的翻车路径是这样的：本地用 Windows 开发，表被存成了 userorder，代码里慢慢出现了 UserOrder、user_order 各种写法（在本地全都能跑）。迁移时用 mysqldump 备份、在 Linux 上恢复，物理表名就是小写，而代码里仍写着 UserOrder——上线第一分钟就报了 Table 'orders.userorder' doesn't exist。

更麻烦的是它不能事后改：MySQL 8.0 之后 lower_case_table_names 只允许在初始化数据目录时设定，运行期改动会直接启动失败，报 Different lower_case_table_names settings for server ('0') and data dictionary ('1')；想改只能重建实例并重新导入。所以它和字符集、sql_mode 一样，属于必须在新库初始化时就定下来的项。

我的应对很简单，也不依赖这个参数：数据库对象名一律小写加下划线，代码里的表名不做任何大小写"创作"。跨平台场景就两端设同一个值——容器里在初始化命令上加 --lower-case-table-names=1，注意必须配合全新的数据目录。

六、only_full_group_by 与严格模式：以前能跑的 SQL 为什么报错

第一步还是先问事实：SELECT @@sql_mode;。MySQL 8.0 的默认值是 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION；而 MariaDB 10.x 的默认值里没有 ONLY_FULL_GROUP_BY。这一句就解释了那个经典现象：同一份代码，本地 MariaDB 跑得通、云上 MySQL 8 报错——不是云上"环境有问题"，是两个数据库的默认严格度不同。

报错	真正在说什么	我现在的处理
`Expression #2 of SELECT list is not in GROUP BY clause ...`	SELECT 里有既不聚合、也不函数依赖于分组列的字段，结果本来就不确定	明确写 `ANY_VALUE(col)`，或改成子查询 / 窗口函数。不要为了让 SQL 跑通就删掉 `ONLY_FULL_GROUP_BY`，它拦下的是"结果随机"
`Data too long for column 'x' at row 1`	严格模式下超长不再静默截断	加长字段长度或改截断逻辑，这是它在帮你找 bug
`Incorrect integer value: '' for column`	空串塞进数字列，非严格模式会悄悄写成 0	应用层显式做空值转换，别依赖数据库兜底
`Incorrect datetime value: '0000-00-00'`	`NO_ZERO_DATE` 生效	用 `NULL` 表示"没有时间"，别用零值日期

改 sql_mode 同样分 session / global / 配置文件三层，SET GLOBAL 重启失效、且不影响已有连接——和 time_zone 一个道理。

七、新库初始化清单

这份配置我现在是照着抄的，MySQL 8 与 MariaDB 的差异在注释里：

[mysqld]
character-set-server            = utf8mb4
collation-server                = utf8mb4_0900_ai_ci   # MariaDB 改 utf8mb4_unicode_ci
default-time-zone               = '+08:00'
lower_case_table_names          = 1                     # 只能初始化时定，之后改要重建实例
explicit_defaults_for_timestamp = ON
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

配套的六条动作：

建库时显式声明字符集与排序规则：CREATE DATABASE orders DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;；
连接串写全：...?parseTime=true&loc=UTC&charset=utf8mb4&collation=utf8mb4_0900_ai_ci&timeout=5s&readTimeout=5s&writeTimeout=5s；
时间列统一 DATETIME(3) 存 UTC，写入用应用生成的时间（time.Now().UTC()），只有纯日志表才用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP；
对象名全小写，代码和 SQL 里保持一种写法；
初始化完成后跑一次自检：上面第一节那段 SELECT，把 @@sql_mode、字符集、时区一起存进部署记录；
把这六条写进项目 README 的"环境约定"里——最容易出事的不是老同事，是照着本地 MySQL 默认值写代码的新同事。

如果重来一次，我会把这三样东西当成"建库第一天就必须签字确认"的参数，而不是等出事再对齐：数据库的默认值是为通用场景挑的，不是为你的部署形态挑的。真正让我半夜起来的那 8 小时，本质上不是 MySQL 的时区算错了，而是我自己从没明确说过数据里存的是哪个时区的时间——一旦这句话写不清楚，任何一层都会替你做决定，而且每一层决定得都不一样。

标签：#MySQL #时区 #字符集