mysql的时区处理

前言
解决方案
- 保持应用服务器时间和数据库时间一致
  - 临时设置
  - 配置文件设置
- 客户端设置连接参数
补充
- 关于CST时区

前言

最近在做一个开放数据服务的时候，发现返回的数据格式化后由于时区问题导致客户端在解析时间时出现问题，比如如下数据：

数据库存储时间：

+----------+---------------------+
| username |updated_at           |
+---------+----------------------+
|  penghj  | 2020-02-21 12:05:29 |
+----------+---------------------+

由于数据库时区是GTM+8，但是程序服务器的时间是UTC，因此将数据库的2020-02-21 12:05:29当成UTC时区的时间了，最终格式化后得到如下json结果返回：

{
  "username": "penghj",
  "updatedAt": "2020-02-21T12:05:29.000Z"
}

这个时间格式是yyyy-MM-dd'T'HH:mm:ss.SSS'Z'，这是swagger标准定义的返回时间格式。这里的Z表示的是UTC时区。

客户端按照这个格式在解析时间时，转换为GMT+8时区的时间，最终客户端得到的时间如下：

2020-02-21 20:05:29.

我们发现，客户端得到的时间比实际时间早了8小时，这导致了客户端出现了一个未来的时间。

这个问题就是由于服务端时区和数据库时区不一致导致的。

解决方案

解决这个问题的方式有很多种，这里列举几种。

保持应用服务器时间和数据库时间一致

这种方式最简单也最暴力，几乎没有后遗症，但是需要在一开始部署数据库服务和应用的时候就把时区全部配置好。

在配置mysql服务器时间前，可以先通过如下命令查看mysql服务时区：

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

这里可以看到mysql目前使用的时区是系统时区，系统的时区是CST。

mysql服务配置时区的方法有两种：

临时设置

在mysql客户端执行如下命令：

mysql> set global time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

这种方式虽然方便，但是mysql一旦重启就无效了。

配置文件设置

修改配置文件my.cnf，在[mysqld]节下增加default-time-zone = '+08:00'

这种方式修改需要重启mysql服务，建议在维护时间进行。

客户端设置连接参数

本人只在mysql 驱动5.1.48版本上测试了以下内容，其他版本请自行测试或者查看官方文档

以JDBC连接为例，可以在jdbc url中设置连接参数，使程序读到的时间是按照mysql服务的时区转换为应用服务器的时区的结果。

比如数据库服务的时区为GMT+8，应用服务器的时区为UTC时，存储时间2020-02-21 12:05:29在应用中读到的时间就变成2020-02-21 04:05:29了。

一般情况下只需要在jdbc连接中加上useTimezone=true和serverTimezone={serverTimezone}即可，如：

jdbc:mysql://mysqlserver:3306/database?useTimezone=true&serverTimezone=UTC

这里假设MySQL服务端的时区是UTC时区，如果是东八区的话，可以设置为GMT+8，在jdbcUrl中得用Url编码：GMT%2B8

在mysql中，还有其他jdbc连接跟时区相关的几个参数：

属性说明
useTimezone 是否在客户端和服务端之间转换时间/日期类型的数据，`true`表示转换，`false`表示不转换，这是旧版本的遗留代码，因此这个属性只在`useLegacyDatetimeCode=true`的时候生效。默认值： `false`
serverTimezone 指定mysql服务的时区，当mysql服务的时区和应用时区不一致时指定，覆盖驱动自动检查的时区。
cacheDefaultTimezone 是否缓存客户端默认时区，这个缓存主要用于mysql数据的时区和客户端时区的转换，缓存可以提高性能，但是无法感知客户端时区的动态变化，客户端时区变化后得重新连接才能效。默认值： `true`
noTimezoneConversionForDateType 当`useTimezone'='true'`或者`useLegacyDatetimeCode'='false'`时，不使用服务器的时区来转换date类型的数据。默认值： `true`
noTimezoneConversionForTimeType 当`useTimezone'='true'`时，不使用服务器的时区来转换date类型的数据。默认值: `false`
useJDBCCompliantTimezoneShift 当JDBC连接包含`java.util.Calendar`参数类型时，是否使用JDBC兼容规则转换`TIME/TIMESTAMP/DATETIME`值的时区信息。这是旧版驱动代码的功能，因此只有当`useLegacyDatetimeCode=true`的时候才生效。默认值: `false`
useLegacyDatetimeCode `true`表示在服务端处理result set和statement中的`DATE/TIME/DATETIME/TIMESTAMP`这些类型的数据，从服务端时区转换为客户端时区后再返回给客户端。`false`表示不在服务端处理，直接返回结果给客户端，这是为了保持向后兼容旧版本的驱动。当设置为`false`时，`useTimezone`,`useJDBCCompliantTimezoneShift`,`useGmtMillisForDatetimes`和`useFastDateParsing`的设置都会无效。默认值： `true`
useSSPSCompatibleTimezoneShift `true`表示启用兼容模式，`false`表示不启用兼容模式。这个兼容模式是指：在发送`TIMESTAMP`类型的值到mysql服务端时，从一个使用服务器端预处理语句，并且配置属性`useJDBCCompliantTimeZoneShift`设置为`true`的MySQL环境中，迁移到不使用服务端预处理语句的MySQL环境中的兼容模式。默认值： `false`

补充

关于CST时区

名为CST的时区是一个很混乱的时区，有四种含义：

美国中部时间 Central Standard Time (USA) UTC-06:00
澳大利亚中部时间 Central Standard Time (Australia) UTC+09:30
中国标准时 China Standard Time UTC+08:00
古巴标准时 Cuba Standard Time UTC-04:00

因此强烈建议不要在任何环境中使用这个时区，以免引起歧义。