mysql 里 CST 时区的坑

梦里梦外; 2022-11-16 10:40 703阅读 0赞

# mysql 里 CST 时区的坑 #

### 一、问题简述 ###

mysql 里 CST 时区是个非常坑的概念，因为在 mysql 里CST既表示中国也表示美国的时区。但是在Jdk代码里，`CST` 这个字符串被理解为`Central Standard Time (USA)`（GMT-6），这就是造成坑的原因。

解决办法

mysql 的 time\_zone配置 不要用SYSTEM，因为用了就是跟随 system\_time\_zone 的值，而 system\_time\_zone 读取自数据库所在宿主机的操作系统，常常是 CST 的值。解决办法是将 time\_zone 改成例如 `+08:00` 以免造成误解。（肯定改mysql啦，你改得了jdk源码吗?）。**修改方法见后文**

### 二、查看、修改时区 ###

#### 1、怎么查看mysql是什么时区? ####

选一个即可

1.  方法1
    
    命令： 连上mysql后命令行执行 `show variables like '%time_zone%'` （Navicat连上后执行也行）
    
    结果：
    
        system_time_zone	CST
        time_zone	SYSTEM
    
    看time\_zone就行了，SYSTEM表示其值跟随system\_time\_zone。
    
    而system\_time\_zone的CST值，是表示中国呢，还是美国，是不清楚的，你直接再执行 `select now()` 跟你手机的时间对比一下就清楚了。
    
    **为什么会system\_time\_zone和time\_zone两个? 要看哪个? 不急后面会解释**
2.  方法2
    
    命令：连上mysql后命令行执行 （Navicat连上后执行也行）
    
        // 分两次执行下面2条命令
          select @@system_time_zone;
          select @@time_zone;
    
    结果：
    
        select @@system_time_zone;
          +--------------------+
          | @@system_time_zone |
          +--------------------+
          | CST                |
          +--------------------+
          1 row in set (4.81 sec)
           
          mysql> select @@time_zone;
          +-------------+
          | @@time_zone |
          +-------------+
          | SYSTEM      |
          +-------------+
          1 row in set (0.04 sec)
3.  方法3
    
        // 查出全局时区和会话时区
        select @@GLOBAL.time_zone,@@SESSION.time_zone;

> #### 为什么mysql有system\_time\_zone和time\_zone两个 ####
> 
> 见 https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
> 
> 简单说就是system\_time\_zone就是mysql服务（即mysqld）启动的时候读取数据库所在宿主机的时区，固定下来的值，这个值之后不再改变（除非重启mysql服务）。time\_zone的值未SYSTEM，意思是它的时区跟system\_time\_zone一样（注意system\_time\_zone的值固定下来后，数据库宿主机的时区再改变，time\_zone的值都是不变的，因为它是跟随system\_time\_zone变量的，不是实时跟随操作系统的）
> 
> 存在 “为什么linux时区是对的，但是mysql的时区是错的” 的疑惑，因为启动mysql服务时如果linux时区是错的，把mysql的时区也带错了，只是后来你发现linux时区错了于是改对了，但是你忘记启动mysql的时候linux时区是错的，而且你忘了是后来你才把linux的时区改正确的，所以你有这个的疑惑。**我好像也有过这样的疑惑**

#### 2、如何修改mysql的时区 ####

*  永久修改，改配置文件，重启mysql服务也有效

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

没有这个文件的话，见后文，让你的mysql启动的时候读取配置文件。

*  临时修改，重启mysql服务后丢失
    
    选择下面之一执行即可。
    
     *  修改会话级别定的，关闭会话后就会失效，也仅仅影响当前会话窗口：`set time_zone='+08:00';`
     *  全局修改，重启mysql服务后丢失，对所有会话生效：\`set global time\_zone=’+08:00’;

### 三、这个bug 是怎么产生的 ###

下面基于mysql-connector-java-8.0.19 进行分析，使用 `com.mysql.cj.jdbc.Driver`

在获取java.sql.Connection的过程会确定时区，调用如下方法

com.mysql.cj.protocol.a.NativeProtocol#configureTimezone
      
    /** * Configures the client's timezone if required. * * @throws CJException * if the timezone the server is configured to use can't be * mapped to a Java timezone. */
    public void configureTimezone() { 
    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
    
    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) { 
    configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
    }
    
    String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();
    
    if (configuredTimeZoneOnServer != null) { 
    // user can override this with driver properties, so don't detect if that's the case
    if (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) { 
        try { 
        canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());
        } catch (IllegalArgumentException iae) { 
        throw ExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor());
        }
    }
    }
    
    if (canonicalTimezone != null && canonicalTimezone.length() > 0) { 
    this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));
    
    //
    // The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this...
    //
    if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) { 
        throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] {  canonicalTimezone }),
                                                getExceptionInterceptor());
    }
    }
    
    this.serverSession.setDefaultTimeZone(this.serverSession.getServerTimeZone());
    }

大概逻辑如下：

1.  从mysql的time\_zone读取值
2.  如果值是SYSTEM则使用system\_time\_zone的值
3.  如果jdbc url配置了时区则使用url里的，如 `jdbc:mysql://localhost:3306/test?useSSL=true&serverTimezone=Asia/Shanghai`获取的是字符串配置定的

如果获取到CST，则因为在java里 `TimeZone("CST")`表示美国中部时间，与mysql数据库用 CST 表示中国时区的含义就不同了，bug就是这么产生的。

### 四、其他问题 ###

#### 1、CST究竟是美国还是中国 ####

其实CST总共有4个含义

*  China Standard Time：中国标准时间
 *  Central Standard Time (USA) ：美国中部时间（中部就是在国土的中间的部分，例如芝加哥）
 *  Central Standard Time (Australia)：澳大利亚中部时间
 *  Cuba Standard Time：古巴标准时间

就是因为这个含义不清，所以会有坑，**体现在Java里就是**：

// 不建议这么创建 TimeZone，这里的CST指的是美国中部时间，不是中国，从
    TimeZone tz = TimeZone.getTimeZone("CST");
    System.out.println(tz);// 可以看到偏移量是offset=-21600000，-21600000微秒=-6小时，所以实锤这里的CST指美国
    
    // 建议创建 TimeZone 用 ZoneId，因为ZoneId 不允许 CST、JST 这种简称，能提前预防进坑，如下
    // ZoneId zoneId = ZoneId.of("CST");// 抛异常
    ZoneId zoneId = ZoneId.of("GMT+8");// 明确指定，是OK的，或者 "区域/城市" 的写法如 Asia/Shanghai
    TimeZone tz1 = TimeZone.getTimeZone(zoneId);

**体现在Mysql里**：  
你根本不知道mysql里的CST是中国还是美国，查出来都展示一样  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70]  
（关于system\_time\_zone和time\_zone后续有解释）

#### 2、你说CST除了表示中国，也表示美国定的时区，拿得出证据吗? ####

**是的!!! 动手做实验给你看 !!!**

使用`show variables like '%time_zone%';` 查看mysql的时区，目前是北京时间，查出

Variable_name	Value
    system_time_zone	CST
    time_zone	SYSTEM

当把操作系统（mysql的宿主机器的操作系统）的时区改成东京时区，并重启mysql服务后（**必须重启服务**），显示

Variable_name	Value
    system_time_zone	JST
    time_zone	SYSTEM

最后把操作系统时区改成美国中部时间（如芝加哥），并重启mysql服务后，显示

Variable_name	Value
    system_time_zone	CST
    time_zone	SYSTEM

可以看到非常坑，无论是中国还是美国，都显示CST，给人很大的误解

### 五、其他 ###

#### 1、mybatis/jdbc 是怎么将日期存入到数据库的，又是怎么查出来的 ####

（这个实在有点复杂，要考虑mysql所在操作系统的时区，也考虑跑你的mysql程序的web服务器的操作系统的时区。加又没时间继续研究了，缓一缓）

#### 2、再次啰嗦补充 ####

##### mysql里的 system\_time\_zone 和 time\_zone是什么关系 #####

system\_time\_zone 是mysql服务启动时读取宿主操作系统的时区，当时读取到什么值就固定什么，是那个时刻的快照。这个值只有在被重新指定或重启mysql服务的时候才可能变化。

time\_zone就是表示所有连接到此mysql的客户端（client）的时区，不管你是什么形式的客户端，是java通过jdbc连接的程序，还是Navicat这种图形化的，还是命令行的，都叫客户端。**这个时区是可以被覆盖的**，可以被jdbcUrl中的时区覆盖。

*  在jdbc的URL里配置了时区会怎么样?  
    会使用这个时区，在jdbcUrl里指定的时区优先级最高，会覆盖其他。例如 `jdbc:mysql://localhost:3306/test?useSSL=true&serverTimezone=Asia/Shanghai`
 *  time\_zone的SYSTEM是什么意思，是不是指 “操作系统” 的时区?  
    time\_zone的SYSTEM，是指值跟随 system\_time\_zone 的值，不是说跟随操作系统的值，操作系统的时区改了，如果 system\_time\_zone 不改，则 time\_zone 也是不会改的（**亲自实验过了!!**）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70]: /images/20221022/476d07cdf9f149adb7ce79409c59da47.png