Java 中的 Mysql 时区问题

 关注
发布于: 2020 年 05 月 15 日
(Photo by Andrea Piacquadio from Pexels)
话说工作十多年，mysql 还真没用几年。起初是外企银行，无法直接接触到 DB；后来一直从事架构方面，也多是解决问题为主。
这次搭建海外机房，围绕时区大家做了一番讨论。不说最终的结果是什么，期间有同事认为 DB 返回的是 UTC 时间。
这里简单做个验证，顺便看下时区的问题到底是如何处理。
环境openjdk version “1.8.0_242” mysql-connector-java “8.0.20” mysql “5.7” 时区 TZ=Europe/London 本地时区 GMT+8
创建个简单的库test及表user， 表结构如下：
CREATE TABLE `user` (
  `name` varchar(50) NOT NULL,
  `birth_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
插入一条测试数据：
mysql> insert into `user`
    -> values ('Tom', time('2020-05-15 08:00:00'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+------+---------------------+
| name | birth_date          |
+------+---------------------+
| Tom  | 2020-05-14 08:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
测试代码：
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "root");
Statement stmt = conn.createStatement();
stmt.execute("select * from user where name = 'Tom'");
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
    Timestamp timestamp = rs.getTimestamp("birth_date");
    System.out.println(timestamp.toLocalDateTime().toString());
}
执行结果：
2020-05-14T15:00
﻿
分析程序的执行过程同时用 wireshark 抓了包。可以看到一次查询，做了这么多次的交互（包含了会话初始化）。这里可以看到 #177 的交互返回查询的结果：Tom 2020-05-14 08:00:00，与 DB 中的数据相符。可见，返回的并不是 UTC 时间。
﻿
﻿
在 TCP 抓包结果中 #155 的查询语句：
/* mysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b) */
SELECT @@session.auto_increment_increment AS auto_increment_increment,
       @@character_set_client             AS character_set_client,
       @@character_set_connection         AS character_set_connection,
       @@character_set_results            AS character_set_results,
       @@character_set_server             AS character_set_server,
       @@collation_server                 AS collation_server,
       @@collation_connection             AS collation_connection,
       @@init_connect                     AS init_connect,
       @@interactive_timeout              AS interactive_timeout,
       @@license                          AS license,
       @@lower_case_table_names           AS lower_case_table_names,
       @@max_allowed_packet               AS max_allowed_packet,
       @@net_write_timeout                AS net_write_timeout,
       @@performance_schema               AS performance_schema,
       @@query_cache_size                 AS query_cache_size,
       @@query_cache_type                 AS query_cache_type,
       @@sql_mode                         AS sql_mode,
       @@system_time_zone                 AS system_time_zone,
       @@time_zone                        AS time_zone,
       @@transaction_isolation            AS transaction_isolation,
       @@wait_timeout                     AS wait_timeout;
﻿
﻿
服务端返回的 time_zone 为 BST。与本地时区的转换，由 mysql 的 connector 自动完成。
进阶时区自动转换实现源码：
ResultSetImpl源码
this.defaultTimestampValueFactory = new SqlTimestampValueFactory(pset, null, this.session.getServerSession().getServerTimeZone());
@Override
public Timestamp getTimestamp(int columnIndex) throws SQLException {
    checkRowPos();
    checkColumnBounds(columnIndex);
    return this.thisRow.getValue(columnIndex - 1, this.defaultTimestampValueFactory);
}
如何确认服务端时区？使用会话中的服务端时区进行服务端时区。会话初始化时会进行时区的确认，比如前面获取的到BST。确认时区的逻辑在NativeProtocol#configureTimezone()中：
public void configureTimezone() {
    #从mysql的响应获取 time_zone 和 system_time_zone 的设置
    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
        configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
    }
    #从 jdbc url 参数 serverTimezone 获取时区
    String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();
    if (configuredTimeZoneOnServer != null) {
        //如果 jdbc url 中未通过 serverTimezone 指定时区。则从TimeZoneMapping.properties中获取mysql 回传的时区缩写对应的标准时区，比如此处的 BST => Europe/London
        //会出现无法映射的情况，不如 CEST 无法映射到 => Europe/Berlin，可以指定自定义的 Properties 文件进行映射
        // 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());
            }
        }
    }
    
    //如果 jdbc url 中通过 serverTimezone 指定了时区，则优先使用该时区
    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());
        }
    }
}
关于 serverTimezone 的官方说明Override detection/mapping of time zone. Used when time zone from server doesn’t map to Java time zone
修改一下 jdbc url，通过serverTimezone指定时区为 GMT+8：jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false
再次执行代码：
2020-05-14T08:00
﻿
原文链接
发布于: 2020 年 05 月 15 日阅读数: 32
张晓辉

 关注
大胆尝试，小心求证 2018.04.09 加入
胡说八道
发布
暂无评论