MYSQL因为时区协商误解导致时间差了 14 或 13 小时

女爷i 2023-02-11 12:30 23阅读 0赞

我一般使用MYSQL定义字段类型时，一般使用TIMESTAMP时间戳来定义创建时间与更新时间，并将其定义为默认值为CURRENT\_TIME,但是由于场景特殊，现在我需要将一个任务的开始时间与结束时间记录，并写入数据库，那么我的开始时间戳与结束时间戳则不应该是使用数据库自带的默认值的，而是应该使用我使用java代码里面传进去的LocalDateTime.now()方法。但是插入后数据我发现有问题，插入的时间比我当前的实际时间少13个小时。于是我开始百度找到答案。

名为 CST 的时区是一个很混乱的时区，在与 MySQL 协商会话时区时，Java 会误以为是 `CST -0500`，而非 `CST +0800`。

### 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

今天是“4月28日”。为什么提到日期？因为美国从“3月11日”至“11月7日”实行夏令时，美国中部时间改为 UTC-05:00，与 UTC+08:00 相差 13 小时。

### 排错过程 ###

在项目中，偶然发现数据库中存储的 Timestamp 字段的 unix\_timestamp() 值比真实值少了 13 个小时。通过调试追踪，发现了 `com.mysql.cj.jdbc` 里的时区协商有问题。

当 JDBC 与 MySQL 开始建立连接时，会调用 `com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer()` 获取服务器参数，其中我们看到调用 `this.session.configureTimezone()` 函数，它负责配置时区。

public void configureTimezone() {
        String configuredTimeZoneOnServer = getServerVariable("time_zone");
    
        if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
            configuredTimeZoneOnServer = getServerVariable("system_time_zone");
        }
    
        String canonicalTimezone = getPropertySet().getStringReadableProperty(PropertyDefinitions.PNAME_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.serverTimezoneTZ = 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.serverTimezoneTZ.getID().equals("GMT")) {
                throw ...
            }
        }
    
        this.defaultTimeZone = this.serverTimezoneTZ;
    }复制代码

追踪代码可知，当 MySQL 的 `time_zone` 值为 `SYSTEM` 时，会取 `system_time_zone` 值作为协调时区。

让我们登录到 MySQL 服务器验证这两个值：

**重点在这里！若 `String configuredTimeZoneOnServer` 得到的是 `CST` 那么 Java 会误以为这是 `CST -0500`，因此 `TimeZone.getTimeZone(canonicalTimezone)` 会给出错误的时区信息。**

![format_png][]

debug variables

如图所示，本机默认时区是 `Asia/Shanghai +0800`，误认为服务器时区为 `CST -0500`，实际上服务器是 `CST +0800`。

我们会想到，即便时区有误解，如果 Timestamp 是以 long 表示的时间戳传输，也不会出现问题，下面让我们追踪到 `com.mysql.cj.jdbc.PreparedStatement.setTimestamp()`。

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            setTimestampInternal(parameterIndex, x, this.session.getDefaultTimeZone());
        }
    }复制代码

注意到这里 `this.session.getDefaultTimeZone()` 得到的是刚才那个 `CST -0500`。

private void setTimestampInternal(int parameterIndex, Timestamp x, TimeZone tz) throws SQLException {
        if (x == null) {
            setNull(parameterIndex, MysqlType.TIMESTAMP);
        } else {
            if (!this.sendFractionalSeconds.getValue()) {
                x = TimeUtil.truncateFractionalSeconds(x);
            }
    
            this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = MysqlType.TIMESTAMP;
    
            if (this.tsdf == null) {
                this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
            }
    
            this.tsdf.setTimeZone(tz);
    
            StringBuffer buf = new StringBuffer();
            buf.append(this.tsdf.format(x));
            if (this.session.serverSupportsFracSecs()) {
                buf.append('.');
                buf.append(TimeUtil.formatNanos(x.getNanos(), true));
            }
            buf.append('\'');
    
            setInternal(parameterIndex, buf.toString());
        }
    }复制代码

原来 Timestamp 被转换为会话时区的时间字符串了。问题到此已然明晰：

1.  JDBC 误认为会话时区在 CST-5
2.  JBDC 把 Timestamp+0 转为 CST-5 的 String-5
3.  MySQL 认为会话时区在 CST+8，将 String-5 转为 Timestamp-13

最终结果相差 13 个小时！如果处在冬令时还会相差 14 个小时！

### 解决方案 ###

解决办法也很简单，明确指定 MySQL 数据库的时区，不使用引发误解的 `CST`：

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)复制代码

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

修改时区操作影响深远，需要重启 MySQL 服务器，建议在维护时间进行。

JSR-310相关规范在这个版本就已经支持了，所以大家只要不小于此版本的就放心用吧。举个例子：

public class User {
        private LocalDateTime createTime;
        // setter ... getter ... 省略了哈
    }
    
    public interface UserDao {
        @Insert("INSERT INTO user(create_time) values(#{createTime})")
        int insertUser(User user);
    }
    
    // 在set时间时，一般直接用now方法就好
    user.setCreateTime(LocalDateTime.now());

这样就OK了，如果你数据库表存的是datetime类型的话，MyBatis自动帮你解析转换，你不用做额外工作。  
注意：  
这里LocalDateTime默认是不包含时区信息的，会取当前机器时间的时区，其实一般情况下，是没有问题的，我用阿里云的服务器（在深圳），直接打印出来就是：

System.out.println(LocalDateTime.now());
    // 输出的是北京时间
    2019-03-15T16:51:37.121

当然这样可能你不是很放心，那么就指明时区：

System.out.println(LocalDateTime.now(ZoneId.of("+08:00")));

### MySQL时区有问题（相差13或14小时） ###

这个问题最开始让我非常头疼，明明我的Tomcat和MySQL在同一个服务器上，Java代码打印时间出来都是对的，结果一插入数据库时间就错了。  
然后进入数据库查看时间和时区：

mysql> select curtime();
    mysql> show variables like '%time_zone%';

发现时间也没问题，都是北京时间，那为什么通过JDBC一插就差那么十几个小时呢？  
问题的原因在这里：一次 JDBC 与 MySQL 因 “CST” 时区协商误解导致时间差了 14 或 13 小时的排错经历  
解决办法：  
手动修改MySQL的时区，明确指定：

mysql> set global time_zone='+08:00';
    mysql> set time_zone='+08:00';
    mysql> flush privileges;

或者修改my.cnf配置文件，一劳永逸，添加：

[mysqld]
    default-time-zone = '+08:00'

即可，最后记得重启MySQL服务，最好还能重启一下Tomcat。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zdGF0aWMub3NjaGluYS5uZXQvdXBsb2Fkcy9pbWcvMjAxNzA0LzI4MTM1OTQ0X2I5ZzgucG5n?x-oss-process=image/format,png