JDBC与mysql同为CST时区导致数据库时间和客户端时间差13或者14小时

最新推荐文章于 2024-07-16 16:57:31 发布

Crystalqy

最新推荐文章于 2024-07-16 16:57:31 发布

阅读量1.5k

点赞数 1

分类专栏：数据库文章标签： mysql 时区

数据库专栏收录该内容

38 篇文章 1 订阅

订阅专栏

摘要
线上排查问题时候碰到一个奇怪的问题，代码中读取一天的记录。代码中设置时间是从零点到夜里二十四点。但是读取出来的记录的开始是既然是从13点开始的。然后看了JDBC的源码发现主要原因是Mysql的CST时间与Java中CST时间是不一样的，下面给出问题的排查过程。

情景再现

1、代码中用的java.util.Date类型、换成TimeStamp类型也没有解决问题
2、数据库中用的TimeStamp类型
3、mysql 版本5.6.x
4、jdk版本1.8
5、mysql-connector-java 8.0.13
我的数据库时区信息如下：

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)

CST时间

CST时间有四种解释，所以不同项目中可能代码的意义不一样，比如Mysql和Java。这也是这次错误的主要原因。Java和Mysql协商时区时把Mysql的CST时间当成了美国中部时间既UTC-5(美国从“3月11日”至“11月7日”实行夏令时,美国中部时间改为 UTC-05:00，其他时候是UTC-06:00)。我们国家是UTC+08:00 时区，所以差了13个小时（13小时还是14小时，取决于你传递给数据库的时间），

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

CST in Java

SimpleDateFormat f1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
f1.setTimeZone(TimeZone.getTimeZone("CST"));
System.out.println(f1.parse("2015-09-01 00:00:00"));

上面代码的输出如下：

Tue Sep 01 13:00:00 CST 2015

比实际的时间多了13个小时。所以你在Java中的时间被认为是UTC-5时间，而数据库任务时间是UTC-8时间。这就是我们上面错误的原因。

源码分析

关键的代码在ConnectionImpl类中，代码如下，已经去掉一些无关重要的代码

private void initializePropsFromServer() throws SQLException {
        String connectionInterceptorClasses = this.propertySet.getStringProperty(PropertyKey.connectionLifecycleInterceptors).getStringValue();
 
        this.session.setSessionVariables();
 
        this.session.loadServerVariables(this.getConnectionMutex(), this.dbmd.getDriverVersion()); //查询数据库一些重要的系统配置
 
        this.autoIncrementIncrement = this.session.getServerSession().getServerVariable("auto_increment_increment", 1);
 
        this.session.buildCollationMapping();
 
        this.session.getProtocol().initServerSession();// 初始化会话，协商时区代码就在里面
 
        checkTransactionIsolationLevel();
 
        this.session.checkForCharsetMismatch();
 
        this.session.configureClientCharacterSet(false);
 
        handleAutoCommitDefaults();

其中this.session.loadServerVariables(this.getConnectionMutex(), this.dbmd.getDriverVersion());查询Mysql重要配置。比如时区。具体查询的信息如下：

StringBuilder queryBuf = new StringBuilder(versionComment).append("SELECT");
                queryBuf.append("  @@session.auto_increment_increment AS auto_increment_increment");
                queryBuf.append(", @@character_set_client AS character_set_client");
                queryBuf.append(", @@character_set_connection AS character_set_connection");
                queryBuf.append(", @@character_set_results AS character_set_results");
                queryBuf.append(", @@character_set_server AS character_set_server");
                queryBuf.append(", @@collation_server AS collation_server");
                queryBuf.append(", @@collation_connection AS collation_connection");
                queryBuf.append(", @@init_connect AS init_connect");
                queryBuf.append(", @@interactive_timeout AS interactive_timeout");
                if (!versionMeetsMinimum(5, 5, 0)) {
                    queryBuf.append(", @@language AS language");
                }
                queryBuf.append(", @@license AS license");
                queryBuf.append(", @@lower_case_table_names AS lower_case_table_names");
                queryBuf.append(", @@max_allowed_packet AS max_allowed_packet");
                queryBuf.append(", @@net_write_timeout AS net_write_timeout");
                if (!versionMeetsMinimum(8, 0, 3)) {
                    queryBuf.append(", @@query_cache_size AS query_cache_size");
                    queryBuf.append(", @@query_cache_type AS query_cache_type");
                }
                queryBuf.append(", @@sql_mode AS sql_mode");
                queryBuf.append(", @@system_time_zone AS system_time_zone");
                queryBuf.append(", @@time_zone AS time_zone");
                if (versionMeetsMinimum(8, 0, 3) || (versionMeetsMinimum(5, 7, 20) && !versionMeetsMinimum(8, 0, 0))) {
                    queryBuf.append(", @@transaction_isolation AS transaction_isolation");
                } else {
                    queryBuf.append(", @@tx_isolation AS transaction_isolation");
                }
                queryBuf.append(", @@wait_timeout AS wait_timeout");

this.session.getProtocol().initServerSession();这就是协商时区的代码，也是我们重点需要关注的代码。如下

public void configureTimezone() {
        // 获取mysql时区配置，结果是SYSTEM
        String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
        //因为我的数据库time_zone是SYSTEM，所以就使用system_time_zone作为数据的时区，如一开始mysql查询结果，时区为CST，既configuredTimeZoneOnServer=CST
        if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
            configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
        }
      // 从配置中查找你对时区的配置，如果你没有这里为null。getPropertySet()就保存了你的数据库用户名、密码、字符编码啊等你在url链接中设置的属性
        String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();
      //因为我没有配置serverTimezone属性，所以canonicalTimezone==null
        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 {
//协商java中的时区，因为Mysql为CST，所以这里也是CST
                    canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());
                } catch (IllegalArgumentException iae) {
                    throw ExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor());
                }
            }
        }
 
        if (canonicalTimezone != null && canonicalTimezone.length() > 0) {
//将刚刚得到的Java的时区设置到会话中
            this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));
        }
        this.serverSession.setDefaultTimeZone(this.serverSession.getServerTimeZone());
    }

再来看一下，如果给sql语句的占位符中传递值的时候代码

  this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss", targetCalendar,
                    targetCalendar != null ? null : this.session.getServerSession().getDefaultTimeZone());
 
            StringBuffer buf = new StringBuffer();
            buf.append(this.tsdf.format(x));
            if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs()) {
                buf.append('.');
                buf.append(TimeUtil.formatNanos(x.getNanos(), 6));
            }
            buf.append('\'');
 
            setValue(parameterIndex, buf.toString(), MysqlType.TIMESTAMP);

代码中把Date或者TimeStamp转换为String，而且用了协商的时区。
分析到这里，问题基本上说清楚了。那么我们如何解决这个问题呢？

总结

1、数据库时区最好不要设置成CST，以免出现上面的错误
2、当数据库中的时间用的是时间类型时候，Java中可以用String，但是不适应做国际化
3、在数据库连接字符串中设置时区。如下(推荐的方式)：

jdbc:mysql://xxxx:3306/table_name?serverTimezone=Asia/Shanghai&useUnicode=true&charact