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

ゝ一纸荒年。 2022-10-22 07:54 668阅读 0赞

## 一、如何设置 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时区，即美国的时区。

![在这里插入图片描述][20210405114642698.png_x-oss-process_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70]

**解决办法**：

*  数据库设置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

[20210405114642698.png_x-oss-process_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70]: /images/20221022/e2dd5d75d7044ab3b1d5bf4d6b4649f0.png