MySQL 時間類型的選擇

Posted on by

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其它相關文章！

推薦閱讀：