MySQL中datetime和timestamp的區別

海山了-發表於2024-07-10
MySql

# MySQL中datetime和timestamp的區別

相同點

  1. 兩個資料型別儲存時間的格式一致。均為 YYYY-MM-DD HH:MM:SS
  2. 兩個資料型別都包含「日期」和「時間」部分。
  3. 兩個資料型別都可以儲存微秒的小數秒（秒後6位小數秒）

自動更新和預設值

  • TIMESTAMP：
    • 支援預設值為當前時間，且在記錄更新時可以自動更新為當前時間。
    • 例如，DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP。
  • DATETIME：
    • 從MySQL 5.6.5開始，支援DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。
    • 在此之前不支援自動設定當前時間。

區別

1. 佔用空間

  • TIMESTAMP ：佔 4 個位元組（小數秒+3 個位元組）
  • DATETIME：在 MySQL 5.6.4 之前，佔 8 個位元組 ，之後版本，佔 5 個位元組。（小數秒+3 個位元組）
型別 佔據位元組 表示形式
timestamp 4 位元組 yyyy-mm-dd hh:mm:ss
datetime 8(5) 位元組 yyyy-mm-dd hh:mm:ss

2. 表示範圍

型別 表示範圍
datetime '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'
timestamp '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

timestamp翻譯為漢語即"時間戳"，它是當前時間到 Unix元年(1970 年 1 月 1 日 0 時 0 分 0 秒)的秒數。對於某些時間的計算，如果是以 datetime 的形式會比較困難，假如我是 1994-1-20 06:06:06 出生，現在的時間是 2016-10-1 20:04:50 ，那麼要計算我活了多少秒鐘用 datetime 還需要函式進行轉換，但是 timestamp 直接相減就行。

3. 時區(存入時間是否會自動轉換？)

timestamp 只佔 4 個位元組，而且是以utc的格式儲存， 它會自動檢索當前時區並進行轉換。

datetime以 8 個位元組儲存，不會進行時區的檢索.

也就是說，對於timestamp來說，如果儲存時的時區和檢索時的時區不一樣，那麼拿出來的資料也不一樣。對於datetime來說，存什麼拿到的就是什麼。

4.使用 now() 儲存當前時間時，儲存的實際值，是否與當前計算機時間一致？

  • TIMESTAMP：可能不一致。儲存值會被轉換成 UTC 時間值再存入資料庫。
  • DATETIME：與當前時間是一致的。

5.如果存入的是 NULL 時，兩個型別如何儲存？

  • TIMESTAMP：會自動儲存當前時間（ now() ）。
  • DATETIME：不會自動儲存當前時間，會直接存入 NULL 值。

時間複雜度

  • TIMESTAMP：
    • 插入和檢索時間複雜度為O(1)，因為儲存的是一個整數值（Unix時間戳）。
    • 需要進行時區轉換，可能會在某些操作上稍微增加時間。
  • DATETIME：
    • 插入和檢索時間複雜度也為O(1)，直接儲存和檢索“YYYY-MM-DD HH:MM”格式的字串。
    • 不涉及時區轉換，因此在某些操作上可能更快。

選擇

如果在時間上要超過Linux時間的，或者伺服器時區不一樣的就建議選擇 datetime。

如果只是想表示年、日期、時間的還可以使用 year、 date、 time，它們分別佔據 1、3、3 位元組，而datetime就是它們的集合。

如果資料庫中有timestamp型別的欄位，mysql資料庫不管是遷庫，還是叢集，都一定要保證時區的相同。如果mysql叢集中的資料庫時區不一致，timestamp的欄位將會造成資料不一致的情況發生。 在遷移庫或者搭建叢集時一定檢查時區，保證時區的相同。中國時區預設是+8,所以不管是單節點mysql，還是mysql叢集，我們第一件事就是應該將當前時區time_zone設定為+8:00。

參考文章

MySQL中datetime和timestamp的區別 - 掘金 (juejin.cn)

相關文章