技术分享 | MySQL:一文弄懂时区&time_zone

你还在被以下问题困扰吗：

MySQL 的安装规范中应该设置什么时区？

JAVA 应用读取到的时间和北京时间差了14个小时，为什么？怎么解决？

已经运行一段时间的业务，修改 MySQL 的时区会影响已经存储的时间类型数据吗？

迁移数据时会有导致时间类型数据时区错误的可能吗？

...

看完这篇文章，你能解决上面所有的疑惑。首先出场的是和时区相关的启动参数和系统变量。

如果要在 MySQL 启动时就指定时区，则应该使用启动参数： default-time-zone ，示例：

启动后我们可以看到控制时区的系统变量，其中 time_zone 变量控制时区，在MySQL运行时可以通过 set 命令修改（注意：不可以写在 my.cnf 中）：

启动参数和系统变量的可用值遵循相同的格式：

system_time_zone 变量只有全局值没有会话值，不能动态修改，MySQL 启动时，将尝试自动确定服务器的时区，并使用它来设置 system_time_zone 系统变量， 此后该值不变。当 time_zone='system' 时，就是使用的这个时区，示例中 time_zone 就是 CST，而 CST 在 RedHat 上就是东八区：

概括一下就两点：

1. NOW() 和 CURTIME() 系统函数的返回值受当前 session 的时区影响

不仅是select now()，包括insert .. values(now())、以及字段的 DEFAULT CURRENT_TIMESTAMP 属性也受此影响：

2. timestamp 数据类型字段存储的数据受时区影响

timestamp 数据类型会存储当时session的时区信息，读取时会根据当前 session 的时区进行转换；而 datetime 数据类型插入的是什么值，再读取就是什么值，不受时区影响。也可以理解为已经存储的数据是不会变的，只是 timestamp 类型数据在读取时会根据时区转换：

关于时区所有明面上的东西都在上面了，我们前面提到的困扰就是在暗处的经验。

1. MySQL的安装规范中应该设置什么时区？

对于国内的业务了，在 my.cnf 写入 default-time-zone='+08:00' `，其他地区和开发确认取对应时区即可。

为什么不设置为 system 呢？使用系统时间看起来也是个不错的选择，比较省事。不建议的原因有两点：

2. JAVA应用读取到的时间和北京时间差了14个小时，为什么？怎么解决？

这通常是 JDBC 参数中没有为连接设置时区属性（用 serverTimezone 参数指定），并且MySQL中没有设置全局时区，这样MySQL默认使用的是系统时区，即 CST。这样一来应用与MySQL 建立的连接的 session time_zone 为 CST ，前面我们提到 CST 在 RedHat 上是 +08:00 时区，但其实它一共能代表4个时区：

JDBC在解析CST时使用了美国标准时间，这就会导致时区错误。要解决也简单：一是遵守上面刚说到的规范，对MySQL显示的设置'+08:00'时区；二是JDBC设置正确的 serverTimezone。

3. 已经运行一段时间的业务，修改MySQL的时区会影响已经存储的时间类型数据吗？

完全不会，只会影响对 timestamp 数据类型的读取。这里不得不提一句，为啥要用 timestamp？用 datetime 不香吗，范围更大，存储空间其实差别很小，赶紧加到开发规范中吧。

4. 迁移数据时会有导致时间类型数据时区错误的可能吗？

这个还真有，还是针对 timestamp 数据类型，比如使用 mysqldump 导出 csv 格式的数据，默认这种导出方式会使用 UTC 时区读取 timestamp 类型数据，这意味导入时必须手工设置 session.time_zone='+00:00'才能保证时间准确：

如何避免？mysqldump 也提供了一个参数 --skip-tz-utc ，意思就是导出数据的那个连接不设置 UTC 时区，使用 MySQL 的 gloobal time_zone 系统变量值。

其实 mysqldump 导出 sql 文件时默认也是使用 UTC 时区，并且会在导出的 sql 文件头部带有 session time_zone 信息，这样可以保证导 SQL 文件导入和导出时使用相同的时区，从而保证数据的时区正确（而导出的 csv 文件显然不可以携带此信息）。需要注意的是 --compact 参数会去掉 sql 文件的所有头信息，所以一定要记得： --compact 参数得和 --skip-tz-utc 一起使用。

视图功能，只是把多个表，按照自已的需求，东一块西一块，逻辑拼在一起，形成一个逻辑表。

调用的时候直接 *** 作这个逻辑表视图就可以了，其它分析解释的 *** 作就交给mysql引擎去处理，最终查询还是要经原来的物理表的。

用视图是不会节省sql执行时间的，反而会增加解析时间，减少效率的。

如果只是解析出来查看，可以加 --base64-output=decode-rows 不显示行格式的内容： mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

用来分析某个事务做了什么： mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037' mysql-bin.000199

a. 时间范围

--start-datetime、--stop-datetime 解析出指定时间范围内的 binlog，这个只适合粗略的解析，不精准，因此不要用来回放 binlog。有个小技巧：如果只能确定大概的时间范围，而且不确定在哪个 binlog 中，可以直接解析多个 binlog。比如大概在 11:20-12:00 内做了个表删除 *** 作，但这个时间内有多个 binlog，可以这样：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205

b. 偏移量范围

--start-position、--stop-position 解析 binlog 指定偏移量范围内的 binlog。如果同时指定了 --start-position 和 --stop-position，并且是解析多个 binlog，则 --start-position 只对第一个 binlog 生效，--stop-position 只对最后一个 binlog 生效。

这个常用场景是：已经解析过一次 binlog 并取得目标事务的 起始 position 后，精确的解析这一段 binlog：

c. GTID 范围

--include-gtids、--exclude-gtids 详细看参数解释。

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -proot

1. --no-defaults

可以避免 my.cnf 里配了 [client] 某些 mysqlbinlog 没有的参数导致 mysqlbinlog 失败

2. -v

不加，只显示行格式（即那一串字符串），无法得到伪 SQL ：

加 -v，从行格式中重建伪SQL(带注释)，不显示 binlog_rows_query_log_events 参数效果：

加 -vv，从行格式中重建伪SQL并添加字段数据类型的注释，可以显示 binlog_rows_query_log_events 参数效果：

3. 加 --base64-output=decode-rows

不显示行格式，如果同时加 -v 参数，可以从行格式中解码为带注释的伪SQL：

4. --skip-gtids

不保留 GTID 事件信息，这样回放 binlog 时会跟执行新事务一样，生成新的 GTID。对比如下：

5. --include-gtids

只解析出指定的 GTID 的事务：

不解析指定的 GTID 的事务

---