MySQL: datetime vs timestamp

區別

• datetime支援時間區間 1000-01-01 00:00:00 到9999-12-31 23:59:59，timestamp只支援'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC
• datetime直接儲存不需要轉換，timestamp會轉變為UTC時間儲存在資料庫，當select的時候再轉變為當前時區的時間
• datetime儲存需要更多的位元組，在mysql支援毫秒之前，datetime需要8位元組，而timestamp只需要4個位元組。細節可以參考TIMESTAMP vs. DATETIME, which should I be using?；支援毫秒之後，儲存空間有兩次變化，細節參見MySQL: DATETIME vs TIMESTAMP

誤解

網上有一些描述存在錯誤

• 錯誤: datetime不支援建立索引，其實是支援的。如下：

person | CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ctime` (`create_time`),
  KEY `mtime` (`modify_time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

最佳實踐

datetime和timestamp沒有明顯的優劣之分，如果選擇要看實際場景，這裡總結一些自己和別人的經驗

• 如果需要提供全球服務或者跨時區呼叫，優先選擇timestamp；不過如果希望不同時區也顯示同一個時間，則使用datetime（看具體應用吧）
• 時間跨度很大，timestamp滿足不了的，只能選擇datetime。比如歷史時間或者未來時間。

時區對datetime和timestamp的影響

mysql> show create table person;
+--------+---------------------------------------------------------------------------+
| Table  | Create Table |
+--------+---------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

表person的create_time欄位是datetime型別，modify_time是timestamp型別

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.06 sec)

mysql> select * from person;
+----+------+---------------------+---------------------+
| id | name | create_time         | modify_time         |
+----+------+---------------------+---------------------+
|  1 | tim  | 2018-07-14 17:42:40 | 2018-07-14 17:42:40 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

可以看出表中的create_time和modify_time的時間都是2018-07-14 17:42:40

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select * from person;
+----+------+---------------------+---------------------+
| id | name | create_time         | modify_time         |
+----+------+---------------------+---------------------+
|  1 | tim  | 2018-07-14 17:42:40 | 2018-07-14 09:42:40 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

如果設定時區為0時區，則可以看到create_time不變，但是modify_time發生了變化，這是因為timestamp的儲存是與時區無關的，當顯示的時候，則會自動轉變為當前時區的時間

注意事項

• CST：是一個可能代表多個時區的簡寫，這裡是China Standard Time，但是大部分情況下都是代表美國中部標準時間Central Standard Time

參考資料

• MySQL: DATETIME vs TIMESTAMP
• TIMESTAMP vs. DATETIME, which should I be using?
• MySQL Datetime vs Timestamp column types – which one I should use?
• Mybatis和Mysql的Datetime的一些問題

如果有一點幫助，麻煩點一個贊，如果沒有，也期待你的反饋