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

摘要
线上排查问题时候碰到一个奇怪的问题,代码中读取一天的记录。代码中设置时间是从零点到夜里二十四点。但是读取出来的记录的开始是既然是从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&characterEncoding=utf8
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 141,390评论 1 297
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 60,682评论 1 254
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 93,205评论 0 211
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 40,718评论 0 171
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 48,406评论 1 250
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 38,590评论 1 170
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 30,271评论 2 267
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,052评论 0 163
  • 想象着我的养父在大火中拼命挣扎,窒息,最后皮肤化为焦炭。我心中就已经是抑制不住地欢快,这就叫做以其人之道,还治其人...
    爱写小说的胖达阅读 28,801评论 6 227
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 32,404评论 0 212
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,176评论 2 212
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 30,489评论 1 222
  • 白月光回国,霸总把我这个替身辞退。还一脸阴沉的警告我。[不要出现在思思面前, 不然我有一百种方法让你生不如死。]我...
    爱写小说的胖达阅读 24,156评论 0 31
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,031评论 2 210
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 31,402评论 3 201
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,583评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 25,927评论 0 163
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 33,352评论 2 228
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 33,478评论 2 229

推荐阅读更多精彩内容