MySQL 時間類型的選擇

MySQL 提供瞭 DATETIME 和 TIMESTAMP 兩種非常相似的類型處理日期和時間,大部分情況下兩種都是 OK 的,但是有些情況二者會互有優劣。

DATETIME

DATETIME 的時間跨度更大,可以從1001年到9999年,精度是秒。並且存儲的格式是將日期和時間打包使用 YYYYMMDDhhmmss格式的整數存儲,這個時間與時區無關,需要占用8個字節的存儲空間。默認,MySQL 顯示 的DATETIME是有序的,明確的格式,例如2021-06-02 18:35:23。這是 ANSI 的標準日期時間格式。

TIMESTAMP

TIMESTAMP即時間戳,存儲的是自格林威治時間(GMT)1970年1月1日零點以來的秒數。和 Unix 系統的時間戳一樣。TIMESTAMP 僅需要4個字節存儲,因此能夠表示的時間跨度更小,從1970年到2038年。MySQL 提供瞭 FROM_UNIXTIME 和 UNIX_TIMESTAMP 函數來完成時間戳和日期之間的轉換。

在 MySQL 4.1版本後,TIMESTAMP 顯示的格式和 DATETIME 類似,但是,TIMESTAMP 的顯示依賴於時區。MySQL 的服務端、操作系統以及客戶端連接都有時區的設置。因此,如果時間是從多個時區存儲的話,那 TIMESTAMP 和 DATETIME 的差別就會很大。TIMESTAMP 會保留使用時的時區信息,而 DATETIME 僅僅是使用文本表示時間。

TIMESTAMP 還有額外的特性。默認地,MySQL會在沒有指定值的情況下使用當前時間插入到 TIMESTAMP列,而更新的時候如果沒有指定值會使用當前時間更新該字段,以下面的測試表為例:

CREATE TABLE t_time_test (
    id INT PRIMARY KEY,
    t_stamp TIMESTAMP,
    t_datetime DATETIME
);

可以看到MySQL 給的默認值就是當前時間戳 CURRENT_TIMESTAMP,並且有個 ON UPDATE CURRENT_TIMESTAMP表示會隨之更新:

INSERT INTO t_time_test(id, t_datetime) VALUES
	(1, NULL), 
	(2, '2021-06-02 18:48:04'), 
	(3, NULL);

可以看到 t_stamp 列自動填充瞭當前時間。

這個特性使得我們可以物協程序維護數據更新時間字段,而交由 MySQL 完成。

如何選擇

從特性上看,可能會優先選擇使用 TIMESTAMP 來存儲時間,相比 DATETIME 來說更高效。也有些人使用整數存儲 Unix 時間戳,實際上這種方式並不能獲益,而且整數還需要額外進行處理,因此並不推薦這麼做。但是一些情況需要註意不要使用 TIMESTAMP 存儲時間:

  • 生日:生日肯定會有早於1970年的,會超出 TIMESTAMP 的范圍
  • 有效期截止時間:TIMESTAMP 的最大時間是2038年,如果用來存類似身份證的有效期截止時間,營業執照的截止時間等就不合適。
  • 業務生存時間:互聯網時代講究快,發展(死得)快。如果要成為長久存在的企業,那麼你的業務時間很可能在2038年還在繼續運營,畢竟現在都2021年瞭。如果你覺得公司業務挺不到2038年,那沒關系。當然,如果幸運地挺到2038年,請務必寫下一條待辦事項:到2038年1月1日前修改數據表時間戳字段類型。

如何存儲毫秒級時間

通常這個時候需要使用 BIGINT 來將時間轉換為整型存儲,或者是使用浮點數,用分數部分表示秒精度一下的時間,這兩種方式都可行。當然,這個時候需要應用支持做格式轉換。

結語

從安全穩妥的角度考慮,建議還是優先選擇 DATETIME 類型,雖然相比 TIMESTAMP 會犧牲一點性能,但是 TIMESTAMP 的時間范圍是硬傷,不要埋下一個隱患,等到真的2038年,你的公司可能是上市公司的時候,程序員可能會遭遇洪水般的 bug 沖擊而不明所以,結果公司的股價迎來閃崩!然後找出來這個程序員,發現是曾經公司的大神,目前的股東,已經實現財務自由的你!你說尷尬不尷尬?

以上就是MySQL 時間類型的選擇的詳細內容,更多關於MySQL 時間類型的資料請關註WalkonNet其它相關文章!