新項目可能存在國際化的問題，所以花了點時間了解了下 MySQL 和 JDBC 驅(qū)動相關(guān)的時間問題?？戳撕枚嗥┛桶l(fā)現(xiàn)，不少人理解的都是錯誤的，所以結(jié)合官方的文檔，重新梳理了一下。

大家都知道在 MySQL 中有兩個專門用來存取日期時間的類型，timestamp 和 datetime。大家總是說 datetime 不包含時區(qū)信息，timestamp 存的 utc 時間戳更適合國際化場景下的本地時間轉(zhuǎn)換。經(jīng)過了解，上述說法對，但不完全對。

版本信息：

• MySQL5.7
• mysql-connector-java 8.0.31

MySQL 用什么類型存日期時間？

目前可以存日期時間的數(shù)據(jù)類型主要分兩類：

• bigint：直接將 utc 時間戳存到 int 類型的字段中。后期根據(jù)用戶本地時區(qū)進行轉(zhuǎn)換。
• 日期時間類型：
  • timestamp：MySQL 官方定義的時間戳，內(nèi)部使用 utc 時間戳存儲，但查詢時返回的結(jié)果會隨著 session time_zone 的變化而變化。
  • datetime：只存日期時間的值，不包含時區(qū)信息。

那么我們應(yīng)該選擇哪個？先說結(jié)論在分析，根據(jù)需求選 bigint 或 datetime。

所以在我看來，如果是后臺管理類的系統(tǒng)，更適合使用 datetime，因為對性能要求沒那么高，并且查詢方便；其他的可以考慮 bigint。

其中最不建議使用的是 timestamp，因為這個會隨著 session time_zone 的變化而變化的，如果開發(fā)者對時區(qū)理解不深，當(dāng)服務(wù)器或、MySQL 或者 JVM 時區(qū)發(fā)生變化時，很容易出現(xiàn)問題。

JDBC 驅(qū)動和 MySQL 的時區(qū)問題

剛剛講了 MySQL 的 timestamp 類型的查詢結(jié)果會受到 session time_zone 的變化而變化，那么這節(jié)我們就來講講時區(qū)的問題。

那么從 Springboot Application 到 MySQL，哪些地方存在時區(qū)的配置呢？自然是通信的兩端，MySQL 服務(wù)端和 MySQL 客戶端。

MySQL Client

這里的 Client 是一個籠統(tǒng)的概念，涉及 client 所在機器 os 的時區(qū)、對應(yīng)應(yīng)用程序的時區(qū)（eg: Java 應(yīng)用就是 JVM 時區(qū)）、JDBC 驅(qū)動的時區(qū)。

• 機器 OS 時區(qū)

root@T630-03:/# cat /etc/timezone
Asia/Shanghai
root@T630-03:/# date
2023 年 06 月 21 日 星期三 11:20:28 CST

OS 時區(qū)影響的是 OS 自身的日期時間命令結(jié)果的返回，同時還是影響部分軟件，因為有些軟件會默認(rèn)使用 OS 時區(qū)作為軟件時區(qū)。

• 應(yīng)用程序時區(qū)

這里我們 Spring Boot 應(yīng)用程序為例，對應(yīng)的就是 JVM 時區(qū)，它默認(rèn)使用的系統(tǒng)時區(qū)，可通過輸出 ZoneId.*systemDefault*() 來查看?？赏ㄟ^ TimeZone.setDefault(TimeZone.getTimeZone("America/New_York")); 來配置。主要涉及在 Java 中操作日期和時間的相關(guān)類。比如 LocalDateTime.now() ，假設(shè)北京時間為 20:00，如果將 TimeZone 設(shè)置為 Asia/Tokyo，則會返回 21:00。

• JDBC 驅(qū)動時區(qū)

在 Springboot 應(yīng)用程序中，我們通過 JDBC 驅(qū)動來連接到數(shù)據(jù)庫，在連接的過程中，我們需要配置 jdbcUrl 來指定 JDBC 時區(qū)。

在 8.0 的驅(qū)動中，是通過配置以下字段指定的：

這兩個參數(shù)指定了，通過 JDBC 驅(qū)動連接到 MySQL 的 session time_zone 為 Asia/Shanghai。那么通過 JDBC 驅(qū)動去調(diào)用 SQL 中日期時間相關(guān)的方法都會以此時區(qū)為準(zhǔn)。

• connectionTimeZone=Asia/Shanghai
• forceConnectionTimeZoneToSession=true

MySQL Server

這里的 Server 指的是數(shù)據(jù)庫，涉及數(shù)據(jù)庫所在機器 os 的時區(qū)、MySQL 的時區(qū)。

• 機器 OS 時區(qū)

同上，但 MySQL 默認(rèn)使用的是 OS 時區(qū)

• MySQL 會話時區(qū)

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

MySQL 有一個默認(rèn)的時區(qū)配置，如上，是使用 OS 時區(qū)作為 MySQL 默認(rèn)時區(qū)的。MySQL 其實本身是沒有所謂的時區(qū)的，我們所說的都是它的 session time_zone，具體的：

• MySQL 存在一個默認(rèn)時區(qū)，一般是 SYSTEM。可通過配置文件或 SQL 進行修改。
• Client 連接到 MySQL 時若不指定 session time_zone，默認(rèn)使用 MySQL Server 的時區(qū)。也可通過 JdbcUrl 在連接的時候指定或通過 SET time_zone ='UTC'; 指定。
• Client 在指定了 session time_zone 后，所有的日期時間查詢均會按照所在時區(qū)返回結(jié)果。

timestamp 類型與時區(qū)的影響

timestamp 在 MySQL 中其實保存就是 UTC 時間戳，當(dāng) Client 配置不同的會話時區(qū)后，會進行轉(zhuǎn)換顯示。

下面舉幾個最直觀的例子。

表 time_test，有一個 timestamp 類型的字段 client_time。

直接在 MySQL 控制臺操作

SET time_zone = 'Asia/Shanghai';
# 2023-06-20 08:00:00
insert into time_test(client_time) values (current_timestamp());
select * from time_test ;
SET time_zone = 'UTC';
# 2023-06-20 00:00:00
select * from time_test ;
# 2023-06-20 00:01:00
insert into time_test(client_time) values (current_timestamp());
select * from time_test ;
SET time_zone = 'Asia/Shanghai';
# 2023-06-20 08:01:00
select * from time_test ;

? 結(jié)果應(yīng)該很好理解。按照當(dāng)前時區(qū)插入數(shù)據(jù)后，查詢結(jié)果是當(dāng)前時區(qū)的日期，更換時區(qū)后，會進行相應(yīng)的偏移，MySQL 會自動進行轉(zhuǎn)換。

• 通過 JDBC 驅(qū)動操作

? 通過 SpringBoot 調(diào)用 JDBC 驅(qū)動查詢，雖然中間涉及到了多個時區(qū)概念，但其實轉(zhuǎn)換過程也很簡單。

說白了，只與 JDBC 驅(qū)動的時區(qū)有關(guān)。

當(dāng)我們做國際化項目時，只需要保持 JVM 時區(qū)和 JDBC 驅(qū)動時區(qū)一致，均為 Asia/Shanghai。其他用戶，只需要根據(jù)設(shè)置的本地時區(qū)進行轉(zhuǎn)換即可。

總結(jié)

一番了解下來，最易用的其實還是bigint和datetime這兩個時區(qū)無關(guān)的類型，時區(qū)相關(guān)的操作直接由我們自己控制最理想。并且也沒有timestamp的時間限制?？偨Y(jié)下：

• 優(yōu)先使用bigint和datetime，國際化在代碼層面做。
• 要使用timestamp，需要了解清楚各種時區(qū)的信息并做好配置。防止因為機器時區(qū)改變、數(shù)據(jù)庫時區(qū)改變而影響查詢的結(jié)果。

參考文獻

• 6.3.11 Datetime types processing
• 6.6.1 Preserving Time Instants
• Support for Date-Time Types in Connector/J 8.0

到此這篇關(guān)于MySQL timestamp與時區(qū)問題的解決的文章就介紹到這了,更多相關(guān)MySQL timestamp與時區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論