(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());@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()); } } }
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