关于mysql的时区（下）：如何设置mysql的时区

一、如何设置 mysql 时区

1、命令

1）查时区：show variables like '%time_zone%'

返回有2行记录，要看time_zone变量的值，不需要看system_time_zone。

若值为SYSTEM表示取值跟system_time_zone保持一致。

system_time_zone的值是启动mysql服务的时候读取了操作系统的值，除非重新启动mysql服务重读否则这个值不变

还有一种查时区的方法，select @@GLOBAL.time_zone,@@SESSION.time_zone 可以查出全局的时区以及会话时区。

2）设置会话时区：set time_zone='+8:00'

仅对当前会话有效，在当前窗口立即生效，关闭会话窗口后设置失效。无需重新登录会话生效，也无需关闭窗口再开窗口

• 执行后如果不确定是否设置成功，可以用上面提到过的语句查查看

• 允许取值：'+08:00'，兼容了多0

  As a string indicating an offset from UTC of the form [*H*]*H*:*MM*, prefixed with a + or -, such as '+10:00', '-6:00', or '+05:30'. A leading zero can optionally be used for hours values less than 10; MySQL prepends a leading zero when storing and retriving the value in such cases.

• 允许取值SYSTEM：set time_zone='SYSTEM'

3）设置全局时区：set global time_zone='+8:00

全局会话有效。必须重新连接才生效（比如exit后重新mysql -uroot -p进行连接）。无需重启mysql服务，重启 mysql 服务后丢失。

网上贴的是两个语句，需要 flush privileges，但是实际测试即使flush了也还是需要重新连接会话才会生效，而且看了下官网，没flush的语句。而且实测不需要flush只需要重连

4）修改 mysql 的配置文件永久设置时区

需要重启 mysql 服务后才能生效，这个对比上面的全局设置，即使服务重启也是能保持配置。配置后跟数据库所在的操作系统的时区就独立开了。

// 下面是5.7的mysql配置，我看了一下8.x版本的mysql也是同样的配置，不过mysql 8.x 默认就是 utf8mb4了，所以字符设置的那行就不需要了
// 配置的位置，无论5.7还是8.x版本，都必须配在 [mysqld] 下面
[mysqld]
default-time-zone=+08:00
character-set-server=utf8mb4

2、如何查看并读懂这些命令

1）、解读查时区的命令返回的结果

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+

• 要知道mysql当前在什么时区，看哪个变量?

  看 time_zone。不看 system_time_zone。如要修改时区，直接修改 time_zone，无视 system_time_zone

• time_zone 的值如果是 SYSTEM 表示什么?

  表示跟 system_time_zone 取值一样。安装MySQL后默认就是SYSTEM。

  有些地方会表述成 SYSTEM 的含义是 "时区跟随操作系统"，"跟随操作系统和跟随system_time_zone" 其实是一样的意思，因为 system_time_zone 就是启动mysql服务的时候读取了操作系统的时区的值。
  

• 建议time_zone不要设置成 SYSTEM

  因为如果 system_time_zone 的值是CST，CST被Java认为是美国的时间，造成混乱。参考后面由此引起的bug

• system_time_zone 的值是怎么来的?

  它的值来自mysql服务启动时读取操作系统时区，读取后即使修改操作系统的时区，它的值也不会再改变了，除非重启mysql 服务变量重新读取

• system_time_zone 的值能改变吗?

  不能通过命令改变

  mysql> set system_time_zone='JST';
  ERROR 1238 (HY000): Variable 'system_time_zone' is a read only variable
  

• 如何确定CST代表什么时区?

  由于中国和美国的时区同名，要知道CST究竟代表什么时区，最简单的方法是 select now() 跟你手机的时间对比一下

3、探讨一个问题

如果OS是东八区，mysql服务起来了，time_zone值是SYSTEM，system_time_zone值是CST（东八区），此时连上mysql获取的时间是东八区的，接着修改OS的时区为东九区，断开mysql连接的会话并重新连接会话，问此时获取的时间是什么时区的? (实测还是东八区)

这个问题的本质就是：time_zone的SYSTEM的值的含义，究竟是跟随启动mysql服务就确定下来的system_time_zone的值呢? 还是跟随操作系统的变化而变化。

实际测试，是跟随system_time_zone变量的变化而变化，而非系统，也就是说time_zone是SYSTEM值，只跟system_time_zone变量有关。而system_time_zone仅仅是启动mysql服务的时候操作系统的一个时区的快照值而已（那一瞬间的值）

4、恶心的CST（修改time_zone改成非SYSTEM!）

CST同名的有4个时区

• Central Standard Time (USA) UT-6:00 美国标准时间
• Central Standard Time (Australia) UT+9:30 澳大利亚标准时间
• China Standard Time UT+8:00 中国标准时间
• Cuba Standard Time UT-4:00 古巴标准时间

这个不仅仅是重名的问题，而且在某些情况下会造成bug，详细看另一篇博文。这里简单说一下

CST 时区是个非常坑的概念，因为在 mysql 里被理解为 China Standard Time（GMT+8），但是在Java里被理解为Central Standard Time (USA)（GMT-6），这就是造成坑的原因。解决办法是mysql就别用CST时区，改成 +08:00 以免造成误解。（肯定改mysql啦，你改得了jdk源码吗?）

如果mysql的time_zone变量是SYSTEM，而system_time_zone是CST的值，system_time_zone的CST这个字符串会造成bug。

mysql的jdbc驱动的代码里会设置时区，这个时区是通过 TimeZone.getTimeZone(canonicalTimezone) 读取，其中 canonicalTimezone 是字符串， TimeZone.getTimeZone("CST") 返回-6时区，即美国的时区。

解决办法：

• 数据库设置time_zone的值为非SYSTEM，比如+08:00
• spring/springboot等程序连接的时候，jdbcUrl带上时区，比如jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai

参考资料

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

https://dev.mysql.com/doc/refman/5.7/en/datetime.html