关于Java中的mysql时区问题详解

关于java中的mysql时区问题详解

前言

话说工作十多年，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,

@KdYFVmg@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());@Overridepublic 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&ampKdYFVmg;useSSL=false

再次执行代码：

2020-05-14T08:00

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。