MySQL 的 timestamp 和 datetime 型別比較

matteao發表於2019-09-17
MySql型別

前言

最近做專案的時候，發現對mysql的timestamp 和datetime 的區別認識不清,所以建了一個測試表，測試了一下區別。另外，我在社群搜尋相關文章時，沒有發現針對性的文章，所以寫出來。

測試環境

mysql 5.7 + phpmyadmin

測試步驟

  • 插入資料時，timestamp 和 datetime的表現
  • 更新資料時，timestamp 和 datetime的表現
  • 更改mysql 的time_zone 值，timestamp 和 datetime的表現

測試資料表

MySQL 的 timestamp 和 datetime 型別比較

其中欄位datetime1 是datetime型別, 欄位timestamp1, timestamp2, timestamp3 是timestamp型別。

  • 有以下區別：
    1. datetime型別 可以設定預設值，也可以不設定
    2. 第一個timestamp型別的預設值自動變為 CURRENT_TIMESTAMP， 約束為 ON UPDATE CURRENT_TIMESTAMP 。CURRENT_TIMESTAMP 是mysql 的變數，值是當前時間。
    3. 其他timestamp型別的預設值自動變為0000-00-00 00:00:00

欄位預設值調整

為了有差異，我把欄位timestamp3 的預設值更改為 CURRENT_TIMESTAMP。這樣， 欄位timestamp1和timestamp3的區別變成了ON UPDATE CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP的區別

MySQL 的 timestamp 和 datetime 型別比較

插入資料

timestamp 型別

INSERT INTO `datetest`(`id`, `datetime1`, `timestamp1`, `timestamp2`, `timestamp3`) VALUES (1, now(), null, null, null);
INSERT INTO `datetest`(`id`, `datetime1`) VALUES (2, now());

結果：

MySQL 的 timestamp 和 datetime 型別比較

  • 小結：

    插入資料， timestamp型別的傳值為null時， 會自動變為當前時間

更新資料

UPDATE `datetest` SET `datetime1`=now(),`timestamp1`= null ,`timestamp2`= null,`timestamp3`= null WHERE id = 1;
UPDATE `datetest` SET `datetime1`=now() WHERE id = 2;

結果：
MySQL 的 timestamp 和 datetime 型別比較

  • 小結：

    更新資料, timestamp型別沒傳值時，預設值為ON UPDATE CURRENT_TIMESTAMP的timestamp, 會更新為當前時間。以 CURRENT_TIMESTAMP 或者0000-00-00 00:00:00 為預設值的timestamp不會更新。

更改time_zone 值

set time_zone = "+9:00";
INSERT INTO `datetest`(`id`, `datetime1`, `timestamp1`, `timestamp2`, `timestamp3`) VALUES (4, now(), null, null, null);
show VARIABLES like "time_zone";

mysql 的timestamp和datetime 型別比較

MySQL 的 timestamp 和 datetime 型別比較

  • 小結：

    更改時區後， 改為+9:00時區後， datetime型別的小時 數值+1， timestamp型別的值不變。說明：timestamp儲存的是UTC時間，然後顯示的是根據當前時區的轉換時間；datetime型別 儲存的是根據運算元據當時的時區的時間。

結論

  • datetime 和 timestamp 型別的表現形式都為：yyyy-mm-dd hh:mm:ss ，其中datetime 佔據8位元組，timestamp佔據4位元組
  • 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型別的傳值為null， 會自動變為當前時間
  • 更新資料時，timestamp型別沒傳值時，預設值為ON UPDATE CURRENT_TIMESTAMP的timestamp, 會更新為當前時間。以 CURRENT_TIMESTAMP 或者0000-00-00 00:00:00 為預設值的timestamp不會更新。
  • 更改時區後， 改為+9:00時區後， datetime型別的小時 數值+1， timestamp型別的值不變。說明：timestamp儲存的是UTC時間，然後顯示的是根據當前時區的轉換時間；datetime型別 儲存的是根據運算元據當時的時區的時間。

相關文章