MySQL timestamp與時(shí)區(qū)問(wèn)題的解決
新項(xiàng)目可能存在國(guó)際化的問(wèn)題，所以花了點(diǎn)時(shí)間了解了下 MySQL 和 JDBC 驅(qū)動(dòng)相關(guān)的時(shí)間問(wèn)題。看了好多篇博客發(fā)現(xiàn)，不少人理解的都是錯(cuò)誤的，所以結(jié)合官方的文檔，重新梳理了一下。
大家都知道在 MySQL 中有兩個(gè)專(zhuān)門(mén)用來(lái)存取日期時(shí)間的類(lèi)型，timestamp 和 datetime。大家總是說(shuō) datetime 不包含時(shí)區(qū)信息，timestamp 存的 utc 時(shí)間戳更適合國(guó)際化場(chǎng)景下的本地時(shí)間轉(zhuǎn)換。經(jīng)過(guò)了解，上述說(shuō)法對(duì)，但不完全對(duì)。
版本信息：
MySQL5.7mysql-connector-java 8.0.31MySQL 用什么類(lèi)型存日期時(shí)間？目前可以存日期時(shí)間的數(shù)據(jù)類(lèi)型主要分兩類(lèi)：
bigint：直接將 utc 時(shí)間戳存到 int 類(lèi)型的字段中。后期根據(jù)用戶(hù)本地時(shí)區(qū)進(jìn)行轉(zhuǎn)換。日期時(shí)間類(lèi)型：timestamp：MySQL 官方定義的時(shí)間戳，內(nèi)部使用 utc 時(shí)間戳存儲(chǔ)，但查詢(xún)時(shí)返回的結(jié)果會(huì)隨著 session time_zone 的變化而變化。datetime：只存日期時(shí)間的值，不包含時(shí)區(qū)信息。那么我們應(yīng)該選擇哪個(gè)？先說(shuō)結(jié)論在分析，根據(jù)需求選 bigint 或 datetime。
類(lèi)型優(yōu)點(diǎn)缺點(diǎn)bigint1. 直觀，不需要考慮時(shí)區(qū)2. 國(guó)際化場(chǎng)景直接根據(jù)本地時(shí)區(qū)轉(zhuǎn)化3. 存儲(chǔ)和查詢(xún)效率高1. 數(shù)據(jù)庫(kù)直接查看時(shí)可讀性差，需要轉(zhuǎn)化timestamp無(wú)1. 查詢(xún)結(jié)果收到 session time_zone 的影響，容易出錯(cuò)2. 最大時(shí)間為 2038 年datetime1. 可讀性好2. MySQL 自帶各種操作函數(shù)，便于查詢(xún)3. 存儲(chǔ)日期范圍大，到 9999 年1. 存儲(chǔ)空間略大2. 查詢(xún)效率較前兩者低所以在我看來(lái)，如果是后臺(tái)管理類(lèi)的系統(tǒng)，更適合使用 datetime，因?yàn)閷?duì)性能要求沒(méi)那么高，并且查詢(xún)方便；其他的可以考慮 bigint。
其中最不建議使用的是 timestamp，因?yàn)檫@個(gè)會(huì)隨著 session time_zone 的變化而變化的，如果開(kāi)發(fā)者對(duì)時(shí)區(qū)理解不深，當(dāng)服務(wù)器或、MySQL 或者 JVM 時(shí)區(qū)發(fā)生變化時(shí)，很容易出現(xiàn)問(wèn)題。
JDBC 驅(qū)動(dòng)和 MySQL 的時(shí)區(qū)問(wèn)題剛剛講了 MySQL 的 timestamp 類(lèi)型的查詢(xún)結(jié)果會(huì)受到 session time_zone 的變化而變化，那么這節(jié)我們就來(lái)講講時(shí)區(qū)的問(wèn)題。
那么從 Springboot Application 到 MySQL，哪些地方存在時(shí)區(qū)的配置呢？自然是通信的兩端，MySQL 服務(wù)端和 MySQL 客戶(hù)端。
MySQL Client這里的 Client 是一個(gè)籠統(tǒng)的概念，涉及 client 所在機(jī)器 os 的時(shí)區(qū)、對(duì)應(yīng)應(yīng)用程序的時(shí)區(qū)（eg: Java 應(yīng)用就是 JVM 時(shí)區(qū)）、JDBC 驅(qū)動(dòng)的時(shí)區(qū)。
機(jī)器 OS 時(shí)區(qū)root@T630-03:/# cat /etc/timezoneAsia/Shanghairoot@T630-03:/# date2023 年 06 月 21 日 星期三 11:20:28 CSTOS 時(shí)區(qū)影響的是 OS 自身的日期時(shí)間命令結(jié)果的返回，同時(shí)還是影響部分軟件，因?yàn)橛行┸浖?huì)默認(rèn)使用 OS 時(shí)區(qū)作為軟件時(shí)區(qū)。
應(yīng)用程序時(shí)區(qū)這里我們 Spring Boot 應(yīng)用程序?yàn)槔瑢?duì)應(yīng)的就是 JVM 時(shí)區(qū)，它默認(rèn)使用的系統(tǒng)時(shí)區(qū)，可通過(guò)輸出 ZoneId.*systemDefault*() 來(lái)查看。可通過(guò) TimeZone.setDefault(TimeZone.getTimeZone("America/New_York")); 來(lái)配置。主要涉及在 Java 中操作日期和時(shí)間的相關(guān)類(lèi)。比如 LocalDateTime.now() ，假設(shè)北京時(shí)間為 20:00，如果將 TimeZone 設(shè)置為 Asia/Tokyo，則會(huì)返回 21:00。
JDBC 驅(qū)動(dòng)時(shí)區(qū)在 Springboot 應(yīng)用程序中，我們通過(guò) JDBC 驅(qū)動(dòng)來(lái)連接到數(shù)據(jù)庫(kù)，在連接的過(guò)程中，我們需要配置 jdbcUrl 來(lái)指定 JDBC 時(shí)區(qū)。
在 8.0 的驅(qū)動(dòng)中，是通過(guò)配置以下字段指定的：
這兩個(gè)參數(shù)指定了，通過(guò) JDBC 驅(qū)動(dòng)連接到 MySQL 的 session time_zone 為 Asia/Shanghai。那么通過(guò) JDBC 驅(qū)動(dòng)去調(diào)用 SQL 中日期時(shí)間相關(guān)的方法都會(huì)以此時(shí)區(qū)為準(zhǔn)。
connectionTimeZone=Asia/ShanghaiforceConnectionTimeZoneToSession=trueMySQL Server這里的 Server 指的是數(shù)據(jù)庫(kù)，涉及數(shù)據(jù)庫(kù)所在機(jī)器 os 的時(shí)區(qū)、MySQL 的時(shí)區(qū)。
機(jī)器 OS 時(shí)區(qū)同上，但 MySQL 默認(rèn)使用的是 OS 時(shí)區(qū)
MySQL 會(huì)話(huà)時(shí)區(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 有一個(gè)默認(rèn)的時(shí)區(qū)配置，如上，是使用 OS 時(shí)區(qū)作為 MySQL 默認(rèn)時(shí)區(qū)的。MySQL 其實(shí)本身是沒(méi)有所謂的時(shí)區(qū)的，我們所說(shuō)的都是它的 session time_zone，具體的：
MySQL 存在一個(gè)默認(rèn)時(shí)區(qū)，一般是 SYSTEM。可通過(guò)配置文件或 SQL 進(jìn)行修改。Client 連接到 MySQL 時(shí)若不指定 session time_zone，默認(rèn)使用 MySQL Server 的時(shí)區(qū)。也可通過(guò) JdbcUrl 在連接的時(shí)候指定或通過(guò) SET time_zone ='UTC'; 指定。Client 在指定了 session time_zone 后，所有的日期時(shí)間查詢(xún)均會(huì)按照所在時(shí)區(qū)返回結(jié)果。timestamp 類(lèi)型與時(shí)區(qū)的影響timestamp 在 MySQL 中其實(shí)保存就是 UTC 時(shí)間戳，當(dāng) Client 配置不同的會(huì)話(huà)時(shí)區(qū)后，會(huì)進(jìn)行轉(zhuǎn)換顯示。
下面舉幾個(gè)最直觀的例子。
表 time_test，有一個(gè) timestamp 類(lèi)型的字段 client_time。
直接在 MySQL 控制臺(tái)操作
SET time_zone = 'Asia/Shanghai';# 2023-06-20 08:00:00insert into time_test(client_time) values (current_timestamp());select * from time_test ;SET time_zone = 'UTC';# 2023-06-20 00:00:00select * from time_test ;# 2023-06-20 00:01:00insert into time_test(client_time) values (current_timestamp());select * from time_test ;SET time_zone = 'Asia/Shanghai';# 2023-06-20 08:01:00select * from time_test ;? 結(jié)果應(yīng)該很好理解。按照當(dāng)前時(shí)區(qū)插入數(shù)據(jù)后，查詢(xún)結(jié)果是當(dāng)前時(shí)區(qū)的日期，更換時(shí)區(qū)后，會(huì)進(jìn)行相應(yīng)的偏移，MySQL 會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換。
通過(guò) JDBC 驅(qū)動(dòng)操作? 通過(guò) SpringBoot 調(diào)用 JDBC 驅(qū)動(dòng)查詢(xún)，雖然中間涉及到了多個(gè)時(shí)區(qū)概念，但其實(shí)轉(zhuǎn)換過(guò)程也很簡(jiǎn)單。
JVM 時(shí)區(qū)JVM 日期（假設(shè)是 LocalDateTime.now() 返回）JDBC 驅(qū)動(dòng)時(shí)區(qū)插入后返回的查詢(xún)結(jié)果Asia/Shanghai2023-06-20 08:00:00Asia/Shanghai2023-06-20 08:00:00Asia/Shanghai2023-06-20 08:00:00UTC2023-06-20 00:00:00Asia/Tokyo2023-06-20 09:00:00Asia/Shanghai2023-06-20 08:00:00Asia/Tokyo2023-06-20 09:00:00UTC2023-06-20 01:00:00說(shuō)白了，只與 JDBC 驅(qū)動(dòng)的時(shí)區(qū)有關(guān)。
當(dāng)我們做國(guó)際化項(xiàng)目時(shí)，只需要保持 JVM 時(shí)區(qū)和 JDBC 驅(qū)動(dòng)時(shí)區(qū)一致，均為 Asia/Shanghai。其他用戶(hù)，只需要根據(jù)設(shè)置的本地時(shí)區(qū)進(jìn)行轉(zhuǎn)換即可。
總結(jié)一番了解下來(lái)，最易用的其實(shí)還是bigint和datetime這兩個(gè)時(shí)區(qū)無(wú)關(guān)的類(lèi)型，時(shí)區(qū)相關(guān)的操作直接由我們自己控制最理想。并且也沒(méi)有timestamp的時(shí)間限制。總結(jié)下：
優(yōu)先使用bigint和datetime，國(guó)際化在代碼層面做。要使用timestamp，需要了解清楚各種時(shí)區(qū)的信息并做好配置。防止因?yàn)闄C(jī)器時(shí)區(qū)改變、數(shù)據(jù)庫(kù)時(shí)區(qū)改變而影響查詢(xún)的結(jié)果。參考文獻(xiàn)6.3.11 Datetime types processing6.6.1 Preserving Time InstantsSupport for Date-Time Types in Connector/J 8.0到此這篇關(guān)于MySQL timestamp與時(shí)區(qū)問(wèn)題的解決的文章就介紹到這了,更多相關(guān)MySQL timestamp與時(shí)區(qū)內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！
相關(guān)文章：
網(wǎng)公網(wǎng)安備