MySQL 的 timestamp 會存在時區問題?

語言: CN / TW / HK

簡介

眾所周知,MySQL 中有兩個時間型別,timestamp 與 datetime,但當在網上搜索 timestamp 與 datetime 區別時,會發現網上有不少與時區有關的完全相反的結論,主要兩種:

  • timestamp 沒有時區問題,而 datetime 有時區問題。原因是 timestamp 是以 UTC格式儲存的,而 datetime 儲存類似於時間字串的形式;

  • timestamp 也有時區問題。

兩種觀點讓人迷惑,那 timestamp 到底會不會有時區問題呢?

基本概念

時區

由於地域的限制,人們發明了時區的概念,用來適應人們在時間感受上的差異。比如中國的時區是東 8 區,表示為 +8:00,或 GMT+8。而日本的時區是東 9 區,表示為 +9:00,或 GMT+9,當中國是早上 8 點時,日本是早上 9 點,即東 8 區的 8 點與東 9 區的 9 點,這兩個時間是相等的。

另外時間還有如下兩個概念:

  • 絕對時間 :如 UNIX 時間戳,是 1970-01-01 00:00:00 開始到現在的秒數,如:1582416000,這種表示是絕對時間,不受時區影響,也叫紀元時 Epoch;

  • 本地時間 :相對於某一時區的時間,是本地時間。比如東 8 區的 2020-02-23 08:00:00,是中國人的本地時間。而在此時,日本人的本地時間是 2020-02-23 09:00:00。所以本地時間都是與某一時區相關的,脫離時區看本地時間,是沒有意義的,因為你並不知道這具體是指的什麼時間點。

在 Java 中,Date 物件是絕對時間,通過 SimpleDateForma t格式化出來的 yyyy-MM-dd HH:mm:ss 形式的時間字串,是本地時間。如果 SimpleDateFormat 沒有呼叫 setTimeZone() 顯示指定時區 ,那麼預設用的是 JVM 執行在的作業系統上的時區,我們開發機上的時區基本都是 GMT+8。

timestamp 與 datetime 區別

如下,我建立了一張表,裡面 time_stamp 是 timestamp 型別,date_time 是 datetime型別,create_timestamp、create_datetime是timestamp與datetime型別,但是它們可以由資料庫自動生成。

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

首先,將資料庫時區設定為 +8:00,即中國的東 8 區。

然後,如下圖手動插入一個固定時間的資料,以及用 now() 函式插入當前時間。

當插入完資料後,然後我們修改當前會話的時區為 +9:00,即日本的東 9 區,然後再次檢視資料。

如上,定義為 timestamp 型別的列 time_stamp、create_timestamp 不管是手動插入的,還是 now() 函式插入的,東9區都比東 8 區的時間大 1 個小時。

這是正確的,說明 timestamp 型別是時區相關 的。然而,定義為 datetime 型別的date_time、create_datetime 欄位,時間都沒有變化,這說明 datetime 型別是時區無關 的。

結論

t imestamp 在儲存上是包含時區的,而 datetime 是不包含時區。說明網上的第一種說法是對的。

再看個例子

我們將東 8 區的的 2020-02-23 08:00:00 轉換為 UNIX 時間戳 (絕對時間),再插入資料庫試試。

如下,使用 Linux 的 date 命令轉換時間串為 UNIX 時間戳

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

然後用 MySQL 的 from_unixtime() 函式,將 UNIX   時間戳 轉換為 MySQL 時間型別來插入資料。

如上,查詢出來的時間也是東 9 區的 9 點,時間也是正確的。

為什麼網上又說 timestamp 型別存在時區問題?

我發現網上說 timestamp 有時區問題,都是應用端插入資料,然後到資料庫中去看,結果發現時間不一樣。因此我打算在 Java 中寫個 Demo 試一下,看能不能重現這個問題。

首先,下面是 Java 中 Entity 的定義,與上面的 time_test 表對應。注意,這裡面時間屬性都是用 Date 型別定義的,如下:

然後,我寫了兩個介面 /insert /queryAll 來插入與查詢資料,如下:

我把資料庫的時區 設定為 +09:00 時區, 即日本的東 9 區,如下:

然後,呼叫 /insert 介面插入資料。注意,我介面傳入的時間是東 8 區的 8 點,如下:

插入完成後,去資料庫中查詢一把,如下:

可以看到,time_stamp 欄位時間是 9  點。且我已將資料庫時區設定為東 9 區,東 9 區的 9 點與東 8 區的 8 點,這兩個時間實際是相等的,因此時間資料沒錯。

用 /queryAll 介面將資料查詢出來,如下:

timeStamp 屬性是 1582416000000,這是毫秒級的時間綴,秒級則是 1582416000,對應是東 8 區的 2020-02-23 08:00:00,時間資料也沒錯。

然後,我又將 MySQL 時區修改回 +8:00,並重啟我們的 Java 應用,如下:

再查詢一下資料,如下:

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

那為什麼網上會說 timestamp 存在時區問題?

經過一翻檢視,我發現他們都提到了 JDBC 的 serverTimezone,會不會是這個配置錯誤導致的呢?就先試試吧。

如圖,我把資料庫時區修改回 +9:00 時區,然後故意把 JDBC 的 URL 上的 serverTimezone 配置為與資料庫不一致的 GMT+8 時區,然後重啟 Java 應用,如下:

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

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

重新插入資料。注意,插入的時間還是東 8 區的 8 點,如下:

然後,我再到資料庫中查詢一把,如下:

time_stamp 時間竟然是 8 點!要知道我們雖然插入的是東 8 區的 8 點,但 當前會話可是東 9 區 的,東 8 區的 8 點等於東 9 區的 9 點,所以正確顯示應該為 9 點才對, 時間差了 1 小時

然後,我又呼叫 /queryAll 介面查詢了一把,想看看 MyBatis 查詢出來的時間資料對不對,如下:

可以看到 timeStamp 是 1582416000000,秒級是 1582416000,這個時間就是東 8 區的 8 點,東 9 區的 9 點啊!

查詢出來的時間竟然是正確的,為什麼?

serverTimezone 的本質

為了找出問題所在,我除錯了一下 MySQL 的 JDBC 驅動程式碼,終於弄明白了原因。

主要可以看看如下這幾點:

1.  MySQL  驅動建立連線後,會呼叫  com.mysql.jdbc.ConnectionImpl#configureTimezone() 來配置此連線的時區。如果 配置了 serverTimezone ,則會使用 serverTimezone 配置的時區。如果沒有配置,會去取 資料庫中的 time_zone 變數

這就是為什麼我們沒有配置 serverTimezone 變數時,結果也是正確的。

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

2. 呼叫 JDBC 的 setTimestamp() 方法時,實際呼叫的是 com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp()。

這裡面會根據 serverTimezone 指定的時區,將對應的 timestamp 物件轉換為 serverTimezone 指定時區的本地時間字串。

3. 執行 SQL 語句時,會執行 com.mysql.cj.jdbc.ClientPreparedStatement#execute()。

這裡面 sendPacket 變數儲存著真實會發送到 MySQL 的 SQL 語句。

注意:看的是 8.0.11 版本 mysql-connector-java 驅動原始碼,不同版本程式碼會稍有差異。比如 5.2.16 版本驅動,jdbc url 上需要同時配置這兩個配置 useTimezone=true&serverTimezone=GMT%2B8,且 setTimestamp() 對應的是 com.mysql.jdbc.PreparedStatement#setTimestampInternal方法。

原理總結

MySQL  驅動在傳送 SQL 前,會將 JDBC 中的 Date 物件引數根據 serverTimeZone 配置的時區轉化為日期字串後,再發送 SQL 請求給  MySQL server。同樣,在  MySQL Server 返回查詢結果後,結果中的日期值也是日期字串。 MySQL  驅動會根據 serverTimeZone 配置的時區,將日期字串轉化為 Date 物件。

因此,當 serverTimeZone 與資料庫實際時區不一致時,會發生時區轉換錯誤,導致時間偏差。

  1. 比如 SQL 引數是一個 Date 物件,時間值是東 8 區的 2020-02-23 08:00:00。 注意它裡面儲存的可不是 2020-02-23 08:00:00 這個字串,它是 Date 物件(絕對時間),只是我用文字表達出來是東8區的2020-02-23 08:00:00

  2. 然後,由於 serverTimeZone 配置的是東 8 區,MySQL 驅動會將這個 Date 物件轉為 2020-02-23 08:00:00。 注意,這時已經是字串了。 然後,再將 SQL 傳送給 MySQL。 注意,這裡的 SQL 裡面已經將 Date 引數替換為 2020-02-23 08:00:00 了,因為 Date 物件本身是無法走網路的

  3. 然後,MySQL 資料庫接收到這個時間字串 2020-02-23 08:00:00 後。由於資料庫時區配置是東 9 區,它會認為這個時間是東 9 區的,它會以東 9 區解析這個時間字串。這時,資料庫儲存的時間是東 9 區的 2020-02-23 08:00:00,也就是東 8 區的 2020-02-23 07:00:00,儲存的時間就偏差了 1 個小時。

那麼問題來了:查詢結果裡的時間為什麼又對了呢?

因為查詢結果返回了東 9 區的時間字串,而 Java 應用又將其理解為是東 8 區的時間,負負得正了!

將 serverTi mezone 與 MySQL 時區保持一致

那麼,如果我們將 serverTimezone 配置改正確,即與資料庫保持一致時,應該查詢到的時間就會是錯的,會少 1 個小時。

JDBC URL 中使用與資料庫一樣的東 9 區 GMT+9,如下:

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

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

然後,重啟 Java 應用再查詢一把看看,結果如下:

返回的是毫秒級時間戳 1582412400000,秒級就是 1582412400。使用 Linux 的 date命令轉換為時間字串形式:

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

看到沒,它是東 8 區的 7 點,剛好差了 1 個小時。

所以,使用 MySQL  timestamp 型別時,對於 Java 應用一定要 保證 J DBC   URL  中的 serverTimezone 與資料庫中的時區配置是一 致的

另外一點是,當沒有配置 serverTimezone 時, MySQL  驅動會自動讀取  MySQL Server中配置的時區,這裡面也有坑。

MySQL 驅動自動讀取資料庫時區的坑

MySQL  安裝好後預設時區是 SYSTEM。而 SYSTEM 指的是 system_time_zone 變數的時區,如下:

當  MySQL  驅動讀到 time_zone 變數是 SYSTEM 時,會再去讀取 system_time_zone 變數。

而 system_time_zone 對於國內來說,預設是 CST。 這是一個 混亂的時區 ,是 4 個不同時區的縮寫,如下:

對於 Linux 或  MySQL ,會認為 CST 是中國標準時間 (+8:00)。但 Java 卻認為 CST 是美國標準時間 (-6:00) 注:可能和 Java 執行在 Windows 中有關

如下,Linux 中 CST 等於 +0800,即中國時區:

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

如下,Java 中 CST 等於 -06:00,美國時區:

因此, MySQL  驅動取到 CST 這個時區值時,它會以為這是 -6:00 時區,但 MySQL 卻理解為 +8:00 時區。

因此 MySQL 時區一定不要配置為 CST ,而要配置為具體的時區,如 +8:00。但如果MySQL 時區為 CST 且不可修改的情況下,一定要配置 JDBC 的 serverTimezone 為清晰的時區(如 GMT+8)。

Entity 中日期屬性是 String 呢?

我們將 Entity 物件中的時間屬性改為 String( 不推薦 ),如下:

然後也寫兩個介面, /insert2 /queryAll2 ,如下:

然後插入資料。注意,這時我是直接將無時區的 8 點作為引數給到 SQL 的,如下:

然後再查詢一把,如下:

如上所示,time_stamp 欄位值是 8 點,但此時資料庫時區是東 9 區,所以這是東 9 區的 8 點。

然後,將資料庫與 JDBC 中 serverTimezone 都改為東 8 區,改完後重啟Java應用。如下:

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

再次插入資料,引數還是無時區的 8 點,如下:

再查詢一把,如下:

如上所示,time_stamp 欄位值是 8 點,但現在資料庫時間是東 8 區,所以這是東 8 區的 8 點。

然後,再將 JDBC URL 上的 serverTimezone 調整為東 9 區,然後重啟 Java 應用。如下:

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

現在 serverTimezone 與資料庫中不一致,資料庫是東 8 區,serverTimezone 是東 9 區。


再次插入無時區的 8 點,如下:

然後再查詢一把,結果如下:

time_stamp 欄位值還是 8 點,資料庫是東 8 區,所以這是東 8 區的 8 點。我們 serverTimezone 與資料庫的時區不一致啊,但卻沒看到時間有偏差,這又是為什麼?

解釋一下

前面說過了,對於 JDBC 中的 Date 物件,在傳送給 MySQL  前,會先根據 serverTimezone 轉換為相應時區的時間字串,但現在 Entity 中時間屬性是 String 型別, MySQL  驅動不會進行轉換,所以不管 serverTimezone 怎麼配置,對 String 型別的時間串都沒影響。

這樣的話,似乎 Java 中日期型別用時間字串來存還好些,不容易出錯。但請再認真考慮一下,呼叫方傳了一個無時區的 8 點, 資料庫自作主張 ,就將其認為是東 9 區的 8 點,但如果這個時間字串實際是東 8 區的 8 點呢?這時如果儲存到資料庫中為東9區的8點,那資料就存錯了。

如果目前 API 介面就傳的是無時區的時間串,Entity 中就定義的 String,怎麼解決呢?

  1. 詢問介面定義人員,這個介面的時間字串指的是哪個時區的?比如是東 8 區的 2020-02-23 08:00:00;

  2. 然後介面接收到時間後,要以東 8 區將時間字串轉換為 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 物件以資料庫的時區格式化為對應的時間字串。比如,資料庫時區是東 9 區,那麼格式化後就是 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 區的 2020-02-23 09:00:00,結果正確。

所以,使用 String 型別來儲存時間資料,要想將時間值儲存正確超級麻煩,不建議在實際開發中這樣使用。

最佳實踐

1. 大多數團隊會規定 API 中傳遞時間要用 UNIX 時間綴。

因為如果你傳一個 2020-02-23 08:00:00 時間值,它到底是哪個時區的 8 點呢?對於 UNIX 時間戳,就不會有此問題,因為它是絕對時間。而如果某些特殊原因,一定要使用時間字串,最好使用 ISO8601 規範那種帶時區的時間串,比如 2020-02-23T08:00:00+08:00。

2. Mybatis 中 Entity 定義要與資料庫定義一致。

資料庫中是 timestamp,那麼 Entity 中要定義為 Date 物件,因為  MySQL  驅動在執行 SQL 時,會自動根據 serverTimezone 配置幫你轉換為資料庫時區的時間串。如果你自己來轉換,你極有可能因為忘記呼叫 setTimeZone() 方法,而使用當前 Java 應用所在機器的預設時區,一旦 Java 應用所在機器的時區與資料庫的時區不一致,就會出現時區問題。

3. JDBC 的 serverTimezone 引數要配置正確。

當不配置時, MySQL  驅動會自動讀取 MySQL Server 的時區,此時一定要將 MySQL   S erver 的時區指定為清晰的時區(如 +08:00), 切勿使用 CST。

4. 如果資料庫時區修改後,JDBC 的 serverTimezone 也要跟著修改,並重啟 Java 應用。

就算沒有配置 serverTimezone,也需要重啟。因為  MySQL  驅動初始化連線時,會將當前資料庫時區快取到一個 Java 變數中,不重啟 Java 應用它不會變。

資料庫中用 timestamp 還是 int 來儲存時間?

如果用 int 型時間戳儲存,不管資料庫時區是啥都不影響,因為儲存的是絕對時間。看起來完美解決了時區問題。

但從某些角度看,這種方案只是把時區問題 從資料庫端推到應用端去了 ,時區問題將出現在將時間字串轉換為時間綴的過程中。比如某程式設計師從  API 介面中拿到時間字串後,沒考慮時區,直接轉為 UNIX 時間綴,就可能出現時區問題。

因此,對於不帶時區的時間串解析,一定要問清楚這是哪個時區的時間,並在程式碼中顯式指定。

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

  • 開發人員看到這個欄位後,無法一目瞭然的瞭解到這個時間綴大概是個什麼時間,需要去轉換一下,會很繁瑣;

  • 像 update_time 這樣的欄位,資料庫提供了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 的機制,這樣在更新任何欄位時,update_time 會自動更新。而如果使用 int儲存,就需要程式設計師每次更新表時,重新 set 這個欄位,容易遺忘;

  • 由於 int 只有 4 個位元組,用它來儲存時間,會在 2038 年後溢位,而對於 timestamp來說, MySQL 將其底層儲存統一修改為 8 個位元組,相對來說還是比較容易的。

當然,也並不是建議不用 int,這是見仁見智的。不管用 timestamp 還是 int,都沒有致命性問題。

總結

timestamp 本身是沒有時區問題的,時區問題是由於 serverTimezone 配置錯誤、 MySQL  使用 CST 這種混亂時區或 Entity 中將日期定義 String 型別導致的。

- EOF -

看完本文有收穫?請轉發分享給更多人

關注「ImportNew」,提升Java技能

點贊和在看就是最大的支援:heart: