MySQL DATETIME 时区问题防坑指南

太长不太长不看直接上结论

MySQL DATETIME 字段在存储的时候只存储了时间的数字信息（比如 2021-12-04 14:15:38），而没有存储时区；

由于没有存储时区，所以就依赖了 MySQL server、client 运行环境的时区配置；

MySQL sever 本身运行环境有时区配置，now() 之类的内置函数，或者 dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 之类自动设置的时间依赖的是 MySQL server 本身的时区配置；

MySQL client 一般是应用程序，当通过对应语言的 mysql-driver 连接 MySQL 时，在写入、读取、范围查询时对于 DATETIME 字段实际发送过去的值是字符串类型的，比如 2021-12-04 14:20:12，也是没有时区信息的，应用程序往往会根据配置来解析成对应时区的时间；

所以使用 DATETIME 字段几乎就必须保证所有相关的 client 和 server 始终使用一个固定时区配置，只要有不同就可能会出现问题。

比如：

client 使用的时区和 server 使用的时区不一致，那么 sever 的 NOW() 或者 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 得到的值被 client 读到之后发现差了几个小时
client1 和 client2 同时使用一个 server，但是 client1、client2 的时区配置不一样，或者 client1 原本配置了时区 Asia/Shanghai 后来变成了 UTC，那么读写老数据的时候可能发现范围查询有问题、更新不成功（按时间大小做限定条件更新）、时间范围查询丢数据……等等的诡异问题

避坑方法：

继续用 DATETIME 字段，但加以注意
- 毕竟有些特性是无法取代的（DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 或者一些时间函数的操作）或者需要使用一些 DATETIME 相关的函数操作
- 那么这时候尽量保证 MySQL 服务器的时区配置和 client 的时区配置相同
- 如果机房仅部署在国内，未来也不会再其他国家、地区部署，业务也仅限于国内，那么使用固定时区问题不大
- 如果机房可能部署在其他国家、地区，那么部署的时候要么写死全都用 +8 时区，比如 Asia/Shanghai，或者固定 UTC 时区，或者使用各地时区，但处理好不同时区之间机器间的数据交互（总体还是比较容易出问题的，某个环节漏掉就可能导致问题，比如文后的“DATETIME 时区踩坑”）
改用 TIMESTAMP 存储时间戳
- 类似 DATETIME 字段，且支持一些 MySQL 自带的时间更新、函数操作
- 可能会在更新行记录的时候自动更新行内 TIMESTAMP 类型字段的值，需要注意一下
- 有效范围比 DATETIME 要小，从 '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC，目前 2021 年，有效时间不长了
改用 int 存储时间戳
- 如果程序里面的时间字段全都来自于程序设置，且只需要一些简单操作，那么可以用 int 来表示
- 时间戳不受时区影响，在不同时区的同一时刻获取的时间戳是相同的，所以不会存在时区问题
- 需要注意一下使用 INT 还是 BIGINT，以及使用的时间戳的精度是 s、ms、us 还是 ns

如果想继续了解一些细节，欢迎阅读后面的部分 👇🏻

MySQL 的 time 类型存储

详见：

MySQL :: MySQL Internals Manual :: 10.9 Date and Time Data Type Representation

划重点：

5.6.4 前和后实现有所不同，这里只说 5.6.4 及以后
DATETIME 有可选的小数部分，根据小数位数不同，占用不同的存储空间
TIMESTAMP 也有可选的小数部分以满足不同进度的需要

DATETIME 整数部分：
 1 bit  sign           (1= non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12) (2^17 = 131072)
 5 bits day            (0-31) (2^5 = 32)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59) (2^6 = 64)
---------------------------
40 bits = 5 bytes

TIMESTAMP 整数部分：
4 bytes 2^32 = 4294967296 2106-02-07 06:28:16 UTC
2^31 = 2147483648 2038-01-19 03:14:08 UTC
// 可能第一个 bit 用来做一些其他工作，文档中没有详细描写

小数部分：
0     0 bytes
1,2   1 byte  (2^8 = 256)
3,4   2 bytes (2^16 = 65536)
4,5   3 bytes (2^24 = 16777216)

根据存储实现可以很清楚的看出 DATETIME 是没有存储时区信息的。

golang mysql-driver 中 time.Time 到 DATETIME 的转换

项目地址：go-sql-driver/mysql

// packets.go:909
writeExecutePacket
  // 是实际的把语句传输给 MySQL 的函数
  // 对于 time 类型的 arg 会做一次转换
  appendDateTime(b, v.In(mc.cfg.Loc)) // appendDateTime 基本是一个 timeToString 的操作，关键是这里有一次转换时区

// 或者如何设置了 InterpolateParams（提前把参数放入 SQL）
// 那么在 connection.go:297 Exec 函数中会调用 interpolateParams
// connection.go:198
interpolateParams
  appendDateTime(buf, v.In(mc.cfg.Loc)) // 同样会有 appendDateTime 的 time.Time 转 string 的操作

// 至于 mc.cfg.Loc 的配置，来自最开始的 MySQL 设置，可以是专门的 Config，或者是从 DSN 字符串中解析
// dsn.go:369
parseDSNParams
  // 解析 DSN
  // 对于 loc
  cfg.Loc, err = time.LoadLocation(value) // 实际就是执行了一次 golang 的 LoadLocation 操作
                                          // 一般情况是 =Local，那么就依赖 golang 运行的环境了

DATETIME 时区踩坑

工作里面有个项目，代码同时在国内和海外的服务器上运行，数据是隔离的，已经平稳地运行了一段时间。

实际上服务的启动脚本里面强制设置了 TZ=Asia/Shanghai（golang 会依靠这个环境变量作为 Local 的时区），在海外的服务器上原本也是这么运行的，所以海外的数据库里面放的时间比如 2021-12-04 16:00:00 其实是指北京时间（+8 时区）的下午 16 点。

由于这个设置比较久远，当时添加的同事早就不负责这个项目了。在迁移代码仓库的时候漏掉了这个环境变量配置，导致新的项目部署之后传输给数据库的是 UTC 时间，这样就导致出现问题了。

更新数据的时候有 where update_time < ? 的判断，因为这次传了 UTC（UTC 是 +0 时区，+8 时区的 2021-12-04 16:00:00 对应了 +0 时区的 2021-12-04 08:00:00），这样去数据库更新的时候如果前面不久用 +8 时区时间更新过，数据库里的 update_time 比传入的还大，所以就更新不上，用户的数据就处理错误了。

类似的，查询数据的时候原本想查询 now 之前的（where update_time < '2021-12-04 16:00:00'），就变成了 now-8h 之前的（where update_time < '2021-12-04 08:00:00'），导致漏查了最近 8 小时的数据。