MySQL 在 Y2K38 問題上的修正
最近在看Year 2038 problem 維基百科條目,有列出一些作業系統和軟體在該問題上實作的解決方案,有提到在MySQL 8.0.28 版有修正FROM_UNIXTIME()、 UNIX_TIMESTAMP()、CONVERT_TZ() 三個 function 處理 64 位元數值的問題。
最近在看Year 2038 problem 維基百科條目,有列出一些作業系統和軟體在該問題上實作的解決方案,有提到在MySQL 8.0.28 版有修正FROM_UNIXTIME()
、 UNIX_TIMESTAMP()
、CONVERT_TZ()
三個 function 處理 64 位元數值的問題。
先回頭了解一下「Y2K38問題」是什麼?早年 CPU 架構和作業系統還停留在 32 位元時代,處理 32 位元長度的資料型態速度較 64 位元資料型態快上許多,就連儲存 UNIX Timestamp 的 time_t 都是使用 32 位元長度,UNIX 時間的設計是以 1970-01-01 00:00:00 UTC 作為相對時間的起點,用一個整數型態的變數儲存相對起點經過的秒數當作當下時間,比如說 86,400 就是相對原點過了 86,400 秒,也就是 1970-01-02 00:00:00 UTC
,1,700,000,000 是 2023-11-14 22:13:20 UTC
。這個整數型態變數的長度就很重要,會影響所能儲存的時間範圍,如果是用有號的 32 位元,記錄範圍會從\(-(2^{31})=-2,147,483,648\) 到 \(2^{31}-1=2,147,483,647\),當數值超過這一範圍就會發生整數溢位問題,變化如下圖所示:
可以看到在超過 2,147,483,647
後十進位值變成 -2,147,483,648
,對應的時間也由 2038-01-19 03:14:07 UTC
跳到 1901-12-13 20:45:52 UTC
,解法就是把儲存相對秒數的變數改用 64 位元來儲存,如果是繼續用秒當作最小時間單位的話,\(2^{63}-1=9,223,372,036,854,775,807\),大概可用到 2,920 億年後,這已經超過目前已知的宇宙年齡長度,不是很實用也很浪費空間,所以有些程式語言在處理時間上把時間單位精度提高到千分之一秒、百萬分之一秒甚至是十億分之一秒,這是另外一個討論課題這邊就不多說了。現在 CPU 和作業系統都已經進入 64 位元的時代來說,處理 64 位元長度變數的速度已經不是問題,除了多用些空間外沒什麼影響。
回頭來看 MySQL 的問題,在 2005 年 MySQL 還在 4.1 的時代就有使用者回報 MySQL Bugs: #12654: "64-bit unix timestamp is not supported in MySQL functions" 問題,指出 UNIX_TIMESTAMP()
在給入一個大於 '2038-01-01'
的字串會回應 0
,FROM_UNIXTIME()
給入一個大於 2,147,483,647
的整數值會回應 NULL
。在 MySQL 中由於本身的 DATETIME 資料型態設計問題,實務上直接用整數型態欄位儲存 UNIX Timestamp 已經是最佳做法,原先以為只要把紀錄時間欄位的資料型態,由 4 Byte 長度的 INT
alter 成 8 Byte 的 BIGINT
就可以解決,但實際上的轉換是透過 UNIX_TIMESTAMP()
和 FROM_UNIXTIME()
兩個函式處理的話,還得將 MySQL 升級到 2022-01-18 釋出的 8.0.28 版本才能解決,這問題從回報到修正版本上線也經過超過 16 年的時間,而 2023 年初的現在到 2038 年問題發生的時間點也只剩下不到 15 年了。