這篇文章主要講解了“MySQL的timestamp存在的時區(qū)問題怎么解決”，文中的講解內(nèi)容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“mysql的timestamp存在的時區(qū)問題怎么解決”吧！

?？h網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、響應(yīng)式網(wǎng)站設(shè)計等網(wǎng)站項目制作，到程序開發(fā)，運營維護。創(chuàng)新互聯(lián)于2013年開始到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)。

簡介

眾所周知，mysql中有兩個時間類型，timestamp與datetime，但當在網(wǎng)上搜索timestamp與datetime區(qū)別時，會發(fā)現(xiàn)網(wǎng)上有不少與時區(qū)有關(guān)的完全相反的結(jié)論，主要兩種：

timestamp沒有時區(qū)問題，而datetime有時區(qū)問題，原因是timestamp是以UTC格式存儲的，而datetime存儲類似于時間字符串的形式

兩種觀點讓人迷惑，那timestamp到底會不會有時區(qū)問題呢？

答：因為mysql數(shù)據(jù)庫未指定所在時區(qū)默認為美國中部時間
(UTC-06:00)，美國從“3月11日”至“11月7日”實行夏令時，美國中部時間改為 UTC-05:00，與 UTC+08:00 相差 13 小時,冬令時則相差14個小時。所以存儲的時候時間就已經(jīng)有“誤差了”。
各位小伙伴使用timestamp類型的時候一定要注意指定時區(qū)，不管是在數(shù)據(jù)庫配置指定還是數(shù)據(jù)庫連接的參數(shù)設(shè)置，一定要指定時區(qū)。

serverTimezone=Asia/Shanghai

show variables like ‘%time_zone%';
set time_zone='+08:00';

select now();

基本概念

時區(qū)：
由于地域的限制，人們發(fā)明了時區(qū)的概念，用來適應(yīng)人們在時間感受上的差異，比如中國的時區(qū)是東8區(qū)，表示為+8:00，或GMT+8，而日本的時區(qū)是東9區(qū)，表示為+9:00，或GMT+9，當中國是早上8點時，日本是早上9點，即東8區(qū)的8點與東9區(qū)的9點，這兩個時間是相等的。
另外時間還有如下兩個概念：

絕對時間：

如unix時間綴，是1970-01-01 00:00:00開始到現(xiàn)在的秒數(shù)，如：1582416000，這種表示是絕對時間，不受時區(qū)影響，也叫紀元時epoch。

本地時間：

相對于某一時區(qū)的時間，是本地時間，比如東8區(qū)的2020-02-23 08:00:00，是中國人的本地時間，而在此時，日本人的本地時間是2020-02-23 09:00:00，所以本地時間都是與某一時區(qū)相關(guān)的，脫離時區(qū)看本地時間，是沒有意義的，因為你并不知道這具體是指的什么時間點。

比如在Java中，Date對象是絕對時間，通過SimpleDateFormat格式化出來的yyyy-MM-dd HH:mm:ss形式的時間字符串，是本地時間，如果SimpleDateFormat沒有調(diào)用setTimeZone()顯示指定時區(qū)，那么默認用的是jvm運行在的操作系統(tǒng)上的時區(qū)，我們開發(fā)機上的時區(qū)基本都是GMT+8。

timestamp與datetime區(qū)別

如下，我創(chuàng)建了一張表，里面time_stamp是timestamp類型，date_time是datetime類型，create_timestamp、create_datetime是timestamp與datetime類型，但是它們可以由數(shù)據(jù)庫自動生成。

CREATE TABLE `time_test` (
  `id` bigint unsigned,
  `time_stamp` timestamp,
  `date_time` datetime,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `create_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  PRIMARY KEY (`id`)
)

1、首先將數(shù)據(jù)庫時區(qū)設(shè)置為+8:00，即中國的東8區(qū)

2、然后如下手動插入一個固定時間的數(shù)據(jù)，以及用now()函數(shù)插入當前時間

3、當插入完數(shù)據(jù)后，然后我們修改當前會話的時區(qū)為+9:00，即日本的東9區(qū)，然后再次查看數(shù)據(jù)

4、如上，定義為timestamp類型的列time_stamp、create_timestamp不管是手動插入的，還是now()函數(shù)插入的，東9區(qū)都比東8區(qū)的時間大1個小時，這是正確的，說明timestamp類型是時區(qū)相關(guān)的，然而定義為datetime類型的date_time、create_datetime字段，時間都沒有變化，這說明datetime類型是時區(qū)無關(guān)的。

結(jié)論：
timestamp在存儲上是包含時區(qū)的，而datetime是不包含時區(qū)，說明網(wǎng)上的第一種說法是對的。

再看個例子
我們將東8區(qū)的的2020-02-23 08:00:00轉(zhuǎn)換為unix時間綴（絕對時間），再插入數(shù)據(jù)庫試試？
如下，使用linux的date命令轉(zhuǎn)換時間串為unix時間綴：

$ "date" --date="2020-02-23 08:00:00 +08:00" +%s
1582416000

然后用mysql的()函數(shù)，將unix時間綴轉(zhuǎn)換為mysql時間類型來插入數(shù)據(jù)。

如上，查詢出來的時間，也是東9區(qū)的9點，時間也是正確的。

為什么網(wǎng)上又說timestamp類型存在時區(qū)問題？

我發(fā)現(xiàn)網(wǎng)上說timestamp有時區(qū)問題，都是應(yīng)用端插入數(shù)據(jù)，然后到數(shù)據(jù)庫中去看，結(jié)果發(fā)現(xiàn)時間不一樣，因此我打算在Java中寫個Demo試一下，看能不能重現(xiàn)這個問題。

1、首先，下面是Java中Entity的定義，與上面的time_test表對應(yīng)，注意，這里面時間屬性都是用Date類型定義的，如下：

2、然后，我寫了兩個接口/insert與/queryAll來插入與查詢數(shù)據(jù)，如下：

3、然后我把數(shù)據(jù)庫的時區(qū)設(shè)置為+09:00時區(qū)，即日本的東9區(qū)，如下：

4、然后調(diào)用/insert接口插入數(shù)據(jù)，注意我接口傳入的時間是東8區(qū)的8點，如下：

5、插入完后，去數(shù)據(jù)庫中查詢一把，如下：

可以看到，time_stamp字段時間是9點，且我已將數(shù)據(jù)庫時區(qū)設(shè)置為東9區(qū)，東9區(qū)的9點與東8區(qū)的8點，這兩個時間實際是相等的，因此時間數(shù)據(jù)沒錯。

6、然后我使用/queryAll接口將數(shù)據(jù)查詢出來，如下：

timeStamp屬性是1582416000000，這是毫秒級的時間綴，秒級則是1582416000，對應(yīng)是東8區(qū)的2020-02-23 08:00:00，時間數(shù)據(jù)也沒錯！

7、然后我又將mysql時區(qū)修改回+8:00，并重啟我們的java應(yīng)用，如下：

8、再查詢一下數(shù)據(jù)，如下：

timeStamp屬性還是1582416000000，時間沒有變化，這也是正確的。

那為什么網(wǎng)上會說timestamp存在時區(qū)問題？

經(jīng)過一翻查看，我發(fā)現(xiàn)他們都提到了jdbc的serverTimezone，會不會是這個配置錯誤導致的呢？就先試試吧！
1、如圖，我把數(shù)據(jù)庫時區(qū)修改回+9:00時區(qū)，然后故意把jdbc的url上的serverTimezone配置為與數(shù)據(jù)庫不一致的GMT+8時區(qū)，然后重啟java應(yīng)用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

其中GMT%2B8就是GMT+8，因為在url上需要urlencode，所以就變成了GMT%2B8。

2、重新插入數(shù)據(jù)，注意插入的時間還是東8區(qū)的8點，如下：

3、然后，我再到數(shù)據(jù)庫中查詢一把，如下：

time_stamp中時間竟然是8點！要知道我們雖然插入的是東8區(qū)的8點，但當前會話可是東9區(qū)的，東8區(qū)的8點等于東9區(qū)的9點，所以正確顯示應(yīng)該為9點才對，時間差了1小時！

4、然后，我又調(diào)用/queryAll接口查詢了一把，想看看mybatis查詢出來的時間數(shù)據(jù)對不對，如下：

可以看到timeStamp是1582416000000，秒級是1582416000，這個時間就是東8區(qū)的8點，東9區(qū)的9點??！查詢出來的時間竟然是正確的，為什么？？？

serverTimezone的本質(zhì)

為了找出問題所在，我調(diào)試了一下mysql的jdbc驅(qū)動代碼，終于弄明白了原因，主要可以看看如下這幾點：

1.mysql驅(qū)動創(chuàng)建連接后，會調(diào)用com.mysql.jdbc.ConnectionImpl#configureTimezone()來配置此連接的時區(qū)，如果配置了serverTimezone，則會使用serverTimezone配置的時區(qū)，沒配置時會去取數(shù)據(jù)庫中的time_zone變量，這就是為什么我們沒有配置serverTimezone變量時，結(jié)果也是正確的。

//若使用普通驅(qū)動，使用此方法配置mysql連接的時區(qū)
com.mysql.jdbc.ConnectionImpl#configureTimezone()
//若使用cj驅(qū)動，使用此方法配置mysql連接的時區(qū)
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()

2.調(diào)用jdbc的setTimestamp()方法時，實際調(diào)用的是com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp()，這里面會根據(jù)serverTimezone指定的時區(qū)，將對應(yīng)的Timestamp對象轉(zhuǎn)換為serverTimezone指定時區(qū)的本地時間字符串。

3.執(zhí)行sql語句時，會執(zhí)行com.mysql.cj.jdbc.ClientPreparedStatement#execute()，這里面sendPacket變量保存著真實會發(fā)送到mysql的sql語句。

注：看的是8.0.11版本mysql-connector-java驅(qū)動源碼，不同版本代碼會稍有差異，比如5.2.16版本驅(qū)動，jdbc url上需要同時配置這兩個配置：useTimezone=true&serverTimezone=GMT%2B8，且setTimestamp()對應(yīng)的是com.mysql.jdbc.PreparedStatement#setTimestampInternal方法。

原理總結(jié)如下：
mysql驅(qū)動在發(fā)送sql前，會將jdbc中的Date對象參數(shù)，根據(jù)serverTimeZone配置的時區(qū)轉(zhuǎn)化為日期字符串后，再發(fā)送sql請求給mysql server，同樣在mysql server返回查詢結(jié)果后，結(jié)果中的日期值也是日期字符串，mysql驅(qū)動會根據(jù)serverTimeZone配置的時區(qū)，將日期字符串轉(zhuǎn)化為Date對象。

因此，當serverTimeZone與數(shù)據(jù)庫實際時區(qū)不一致時，會發(fā)生時區(qū)轉(zhuǎn)換錯誤，導致時間偏差，如下：
a、比如sql參數(shù)是一個Date對象，時間值是東8區(qū)的2020-02-23 08:00:00，注意它里面存儲的可不是2020-02-23 08:00:00這個字符串，它是Date對象(絕對時間)，只是我用文字表達出來是東8區(qū)的2020-02-23 08:00:00。
b、然后，由于serverTimeZone配置的是東8區(qū)，mysql驅(qū)動會將這個Date對象轉(zhuǎn)為2020-02-23 08:00:00，注意這時已經(jīng)是字符串了，然后再將sql發(fā)送給mysql，注意這里的sql里面已經(jīng)將Date參數(shù)替換為2020-02-23 08:00:00了，因為Date對象本身是無法走網(wǎng)絡(luò)的。
c、然后mysql數(shù)據(jù)庫接收到這個時間字符串2020-02-23 08:00:00后，由于數(shù)據(jù)庫時區(qū)配置是東9區(qū)，它會認為這個時間是東9區(qū)的，它會以東9區(qū)解析這個時間字符串，這時數(shù)據(jù)庫保存的時間是東9區(qū)的2020-02-23 08:00:00，也就是東8區(qū)的2020-02-23 07:00:00，保存的時間就偏差了1個小時。
d、查詢結(jié)果里時間為什么又對了呢，因為查詢結(jié)果返回了東9區(qū)的時間字符串，而java應(yīng)用又將其理解為是東8區(qū)的時間，負負得正了！

將serverTimezone與mysql時區(qū)保持一致

so，那么如果我們將serverTimezone配置改正確，即與數(shù)據(jù)庫保持一致時，應(yīng)該查詢到的時間就會是錯的，會少1個小時。

1、jdbc url中使用與數(shù)據(jù)庫一樣的東9區(qū)GMT+9，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

其中的GMT%2B9，即是GMT+9。

2、然后重啟Java應(yīng)用，再查詢一把看看，如下：

返回的是毫秒級時間綴1582412400000，秒級就是1582412400，使用linux的date命令轉(zhuǎn)換為時間字符串形式：

$ "date" --date="@1582412400" +"%F %T %z"
2020-02-23 07:00:00 +0800

看到?jīng)]，它是東8區(qū)的7點，剛好差了1個小時。

3、所以，使用mysql的timestamp類型時，對于java應(yīng)用來說，一定要保證jdbc url中的serverTimezone與數(shù)據(jù)庫中的時區(qū)配置是一致的。
另外一點是，當沒有配置serverTimezone時，mysql驅(qū)動會自動讀取mysql server中配置的時區(qū)，這里面也有坑！如下：

mysql驅(qū)動自動讀取數(shù)據(jù)庫時區(qū)的坑

3.1 mysql安裝好后，默認時區(qū)是SYSTEM，而SYSTEM指的是system_time_zone變量的時區(qū)，如下：

3.2 當mysql驅(qū)動讀到time_zone變量是SYSTEM時，會再去讀取system_time_zone變量，而system_time_zone對于國內(nèi)來說，默認是CST，這是一個混亂的時區(qū)，是4個不同時區(qū)的縮寫，如下：

對于Linux或MySQL，會認為CST是中國標準時間(+8:00)，但Java卻認為CST是美國標準時間(-6:00)(注：可能和Java運行在Windows中有關(guān))：
如下，linux中CST等于+0800，即中國時區(qū)：

$ "date" +"%F %T %Z %z"
2021-09-12 18:35:49 CST +0800

如下，java中CST等于-06:00，美國時區(qū)：

3.3 因此mysql驅(qū)動取到CST這個時區(qū)值時，它會以為這是-6:00時區(qū)，但MySQL卻理解為+8:00時區(qū)，因此MySQL時區(qū)一定不要配置為CST，而要配置為具體的時區(qū)，如+8:00，但如果MySQL時區(qū)為CST且不可修改的情況下，一定要配置jdbc的serverTimezone為清晰的時區(qū)(如：GMT+8)。

Entity中日期屬性是String呢？

1、我們將Entity對象中的時間屬性改為String（不推薦），如下：

2、然后也寫兩個接口，/insert2與/queryAll2，如下：

3、然后插入數(shù)據(jù)，注意這時我是直接將無時區(qū)的8點，作為參數(shù)給到sql的，如下：

4、然后再查詢一把，如下：

如上所示，time_stamp字段值是8點，但此時數(shù)據(jù)庫時區(qū)是東9區(qū)，所以這是東9區(qū)的8點。
5、然后我將數(shù)據(jù)庫與jdbc中serverTimezone都改為東8區(qū)呢，改完后重啟Java應(yīng)用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

6、再次插入數(shù)據(jù)，參數(shù)還是無時區(qū)的8點，如下：

7、再查詢一把，如下：

如上所示，time_stamp字段值是8點，但現(xiàn)在數(shù)據(jù)庫時間是東8區(qū)，所以這是東8區(qū)的8點。
8、然后我再將jdbc url上的serverTimezone調(diào)整為東9區(qū)，然后重啟Java應(yīng)用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

現(xiàn)在serverTimezone與數(shù)據(jù)庫中不一致，數(shù)據(jù)庫是東8區(qū)，serverTimezone是東9區(qū)。
9、我們再次插入無時區(qū)的8點，如下：

10、然后再查詢一把，如下：

time_stamp字段值還是8點，數(shù)據(jù)庫是東8區(qū)，所以這是東8區(qū)的8點，但我們serverTimezone與數(shù)據(jù)庫的時區(qū)不一致啊，沒看到時間有偏差，為什么？

解釋一下
前面說過了，對于jdbc中的Date對象，在發(fā)送給mysql前，會先根據(jù)serverTimezone轉(zhuǎn)換為相應(yīng)時區(qū)的時間字符串，但現(xiàn)在Entity中時間屬性是String類型，mysql驅(qū)動不會進行轉(zhuǎn)換，所以不管serverTimezone怎么配置，對String類型的時間串都沒影響。

這樣的話，似乎java中日期類型用時間字符串來存還好些，不容易出錯，但請再認真考慮一下，調(diào)用方傳了一個無時區(qū)的8點，數(shù)據(jù)庫自作主張，就將其認為是東9區(qū)的8點，但如果這個時間字符串實際是東8區(qū)的8點呢？這時如果保存到數(shù)據(jù)庫中為東9區(qū)的8點，那數(shù)據(jù)就存錯了！

那如果目前api接口就傳的無時區(qū)的時間串，Entity中就定義的String，怎么解決呢？
1、詢問接口定義人員，這個接口的時間串指的是哪個時區(qū)的，比如是東8區(qū)的2020-02-23 08:00:00。
2、然后接口接收到時間后，要以東8區(qū)將時間字符串轉(zhuǎn)換為Date對象，如下：

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
Date date = sdf.parse("2020-02-23 08:00:00");

3、然后如果Entity中時間屬性定義的是String，那么我們要再將Date對象以數(shù)據(jù)庫的時區(qū)格式化為對應(yīng)的時間字符串，比如數(shù)據(jù)庫時區(qū)是東9區(qū)，那么格式化后就是2020-02-23 09:00:00，如下：

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+9"));
String dateStr = sdf.format(date);
entity.setTimeStamp(dateStr);

4、然后將Entity保存到mysql中的，就也會是東9區(qū)的2020-02-23 09:00:00，結(jié)果正確。

所以，使用String類型來存儲時間數(shù)據(jù)，要想將時間值保存正確，超級麻煩，不建議在實際開發(fā)中這種使用。

最佳實踐

1、大多數(shù)團隊會規(guī)定api中傳遞時間要用unix時間綴，因為如果你傳一個2020-02-23 08:00:00時間值，它到底是哪個時區(qū)的8點呢？對于unix時間綴，就不會有此問題，因為它是絕對時間。而如果某些特殊原因，一定要使用時間字符串，最好使用ISO8601規(guī)范那種帶時區(qū)的時間串，比如：2020-02-23T08:00:00+08:00。

2、Mybatis中Entity定義要與數(shù)據(jù)庫定義一致，數(shù)據(jù)庫中是timestamp，那么Entity中要定義為Date對象，因為mysql驅(qū)動在執(zhí)行sql時，會自動根據(jù)serverTimezone配置幫你轉(zhuǎn)換為數(shù)據(jù)庫時區(qū)的時間串，如果你自己來轉(zhuǎn)換，你極有可能因為忘記調(diào)用setTimeZone()方法，而使用當前java應(yīng)用所在機器的默認時區(qū)，一旦java應(yīng)用所在機器的時區(qū)與數(shù)據(jù)庫的時區(qū)不一致，就會出現(xiàn)時區(qū)問題。

3、jdbc的serverTimezone參數(shù)，要配置正確，當不配置時，mysql驅(qū)動會自動讀取mysql server的時區(qū)，此時一定要將mysql server的時區(qū)指定為清晰的時區(qū)(如：+08:00)，切勿使用CST。

4、如果數(shù)據(jù)庫時區(qū)修改后，jdbc的serverTimezone也要跟著修改，并重啟Java應(yīng)用，就算沒有配置serverTimezone，也需要重啟，因為mysql驅(qū)動初始化連接時，會將當前數(shù)據(jù)庫時區(qū)緩存到一個java變量中，不重啟Java應(yīng)用它不會變。

數(shù)據(jù)庫中用timestamp還是int來存儲時間？

如果用int型時間綴存儲，不管數(shù)據(jù)庫時區(qū)是啥，都不影響，因為存儲的是絕對時間，看起來完美解決了時區(qū)問題。
但從某些角度看，這種方案只是把時區(qū)問題從數(shù)據(jù)庫端推到應(yīng)用端去了，時區(qū)問題將出現(xiàn)在將時間字符串轉(zhuǎn)換為時間綴的過程中，比如某程序員從api接口中拿到時間字符串后，沒考慮時區(qū)，直接轉(zhuǎn)為unix時間綴，就可能出現(xiàn)時區(qū)問題。
因此，對于不帶時區(qū)的時間串解析，一定要問清楚這是哪個時區(qū)的時間，并在代碼中顯式指定！

另外，用int存儲時間還有如下3個不好的點：

開發(fā)人員看到這個字段后，無法一目了然的了解到這個時間綴大概是個什么時間，需要去轉(zhuǎn)換一下，會很繁瑣。像update_time這樣的字段，數(shù)據(jù)庫提供了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP的機制，這樣在更新任何字段時，update_time會自動更新，而如果使用int存儲，就需要程序員每次更新表時，重新set這個字段，容易遺忘。由于int只有4個字節(jié)，用它來存儲時間，會在2038年后溢出，而對于timestamp來說，MySQL將其底層存儲統(tǒng)一修改為8個字節(jié)，相對來說還是比較容易的。當然，也并不是建議不用int，這是見仁見智的，不管用timestamp還是int，都沒有致命性問題的。

感謝各位的閱讀，以上就是“mysql的timestamp存在的時區(qū)問題怎么解決”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對mysql的timestamp存在的時區(qū)問題怎么解決這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！