前言

話說工作十多年，mysql 還真沒用幾年。起初是外企銀行，無法直接接觸到 DB；后來一直從事架構(gòu)方面，也多是解決問題為主。

這次搭建海外機(jī)房，圍繞時(shí)區(qū)大家做了一番討論。不說最終的結(jié)果是什么，期間有同事認(rèn)為 DB 返回的是 UTC 時(shí)間。

這里簡單做個(gè)驗(yàn)證，順便看下時(shí)區(qū)的問題到底是如何處理。

環(huán)境

openjdk version “1.8.0_242”mysql-connector-java “8.0.20”mysql “5.7” 時(shí)區(qū) TZ=Europe/London

本地時(shí)區(qū) GMT+8

創(chuàng)建個(gè)簡單的庫test及表user， 表結(jié)構(gòu)如下：

CREATE TABLE `user` ( `name` varchar(50) NOT NULL, `birth_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin1

插入一條測試數(shù)據(jù)：

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

執(zhí)行結(jié)果：

2020-05-14T15:00

分析

程序的執(zhí)行過程同時(shí)用 wireshark 抓了包。可以看到一次查詢，做了這么多次的交互（包含了會(huì)話初始化）。這里可以看到 #177 的交互返回查詢的結(jié)果：Tom 2020-05-14 08:00:00，與 DB 中的數(shù)據(jù)相符。可見，返回的并不是 UTC 時(shí)間。

在 TCP 抓包結(jié)果中 #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_packetAS max_allowed_packet, @@net_write_timeoutAS net_write_timeout, @@performance_schemaAS 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;

服務(wù)端返回的 time_zone 為 BST。與本地時(shí)區(qū)的轉(zhuǎn)換，由 mysql 的 connector 自動(dòng)完成。

進(jìn)階

時(shí)區(qū)自動(dòng)轉(zhuǎn)換

實(shí)現(xiàn)源碼：

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

如何確認(rèn)服務(wù)端時(shí)區(qū)？

使用會(huì)話中的服務(wù)端時(shí)區(qū)進(jìn)行服務(wù)端時(shí)區(qū)。會(huì)話初始化時(shí)會(huì)進(jìn)行時(shí)區(qū)的確認(rèn)，比如前面獲取的到BST。確認(rèn)時(shí)區(qū)的邏輯在NativeProtocol#configureTimezone()中：

public void configureTimezone() { #從mysql的響應(yīng)獲取 time_zone 和 system_time_zone 的設(shè)置 String configuredTimeZoneOnServer = this.serverSession.getServerVariable('time_zone'); if ('SYSTEM'.equalsIgnoreCase(configuredTimeZoneOnServer)) { configuredTimeZoneOnServer = this.serverSession.getServerVariable('system_time_zone'); } #從 jdbc url 參數(shù) serverTimezone 獲取時(shí)區(qū) String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue(); if (configuredTimeZoneOnServer != null) { //如果 jdbc url 中未通過 serverTimezone 指定時(shí)區(qū)。則從TimeZoneMapping.properties中獲取mysql 回傳的時(shí)區(qū)縮寫對(duì)應(yīng)的標(biāo)準(zhǔn)時(shí)區(qū)，比如此處的 BST => Europe/London //會(huì)出現(xiàn)無法映射的情況，不如 CEST 無法映射到 => Europe/Berlin，可以指定自定義的 Properties 文件進(jìn)行映射 // 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 指定了時(shí)區(qū)，則優(yōu)先使用該時(shí)區(qū) 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()); } }}

關(guān)于 serverTimezone 的官方說明

Override detection/mapping of time zone. Used when time zone from server doesn’t map to Java time zone

修改一下 jdbc url，通過serverTimezone指定時(shí)區(qū)為 GMT+8：jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false

再次執(zhí)行代碼：

2020-05-14T08:00

總結(jié)

到此這篇關(guān)于關(guān)于Java中mysql時(shí)區(qū)問題的文章就介紹到這了,更多相關(guān)Java中mysql時(shí)區(qū)問題內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！