一前言
昨天在給開發同學做資料庫設計規範分享的時候，講到時間欄位常用的有三個選擇datetime、timestamp、int，應該使用什麼型別的合適？本文透過三種型別的各個維度來分析，宣告:本文沒有具體的結論，但是會給一個推薦使用方式，需要使用者結合自己的業務場景來具體選擇。

二分析
int型：
儲存長度: 4位元組
表示範圍: date('Y-m-d H:i:s', 4294967295) 最大到 2106-02-07 14:28:15 ，如果一個企業活過這麼久，就需要資料庫考慮 bigint 或者datetime型別了。
是否為空: 可以為空,但是業務邏輯設計建議設定非空
儲存格式: 數值型別儲存，節省空間
時區相關: 與時區無關
預設值 : 可以根據業務邏輯設定預設值為某個時間。
優點
1 型別簡單,cpu處理該欄位的運算會比較快，佔用位元組小，節省空間。
2 查詢速度快。

datetime：
儲存長度: 8位元組
表示範圍:'1000-01-01 00:00:00'-'9999-12-31 23:59:59'
是否為空: 允許為空值，可以自定義值，且insert和update操作不會自動修改其值。
儲存格式: 以實際格式儲存(Just stores what you have stored and retrieves the same thing which you have stored.)
時區相關: 與時區無關
預設值 : 不指定預設值的時候 MySQL會初始化為'0000-00-00 00:00:00'
mysql> CREATE TABLE `tm` (
-> `d1` int(10) unsigned NOT NULL default '0',
-> `d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
-> `d3` datetime NOT NULL,
-> `d4` timestamp NOT NULL default CURRENT_TIMESTAMP on update current_timestamp
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tm(d1,d4) values(1458612980,now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from tm;
+------------+---------------------+---------------------+---------------------+
| d1 | d2 | d3 | d4 |
+------------+---------------------+---------------------+---------------------+
| 1458612980 | 2016-03-22 10:16:20 | 2016-03-22 15:21:21 | 2016-03-22 10:16:20 |
| 1458612980 | 2016-03-22 15:22:17 | 0000-00-00 00:00:00 | 2016-03-22 15:22:17 |
+------------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
優點 顯示直觀，不需使用函式做轉換

timestamp：
儲存長度: 4位元組
是否為空: 允許為空值，但是不可以自定義值，所以為空值時沒有任何意義。
表示範圍:'1970-01-01 00:00:01'-'2038-01-19 03:14:07
是否為空: 允許為空值，可以自定義值，且insert和update操作不會自動修改其值。
儲存格式: 值以UTC格式儲存,即以毫秒為單位的數字儲存（ it stores the number of milliseconds）
時區相關: 和時間相關，時區轉化，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區。
預設值 : 可以設定為CURRENT_TIMESTAMP()，當前的系統時間。
gmt_modified timestamp not null default '0000-00-00 00:00:00' on update current_timestamp
欄位屬性加上 "on update current_timestamp",
1 在更新記錄時不指定update timestamp欄位的值，資料庫會自動修改gmt_modified的值為當前系統的時間，
2 在插入記錄時不指定timestamp欄位和timestamp欄位的值，插入後該欄位的值會自動變為當前系統時間。
相比於 init 型別的可以自動更新為系統當前時間，其他並無優勢。
三總結
對於如何選型，有如下三種層面效能，儲存空間，時間範圍的考慮。這裡我從時間範圍和儲存空間層面推薦使用 int 或者bigint ，datetime 型別。bigint和datetime 佔用的空間一樣，唯一的差異是在效能上可能存在差異。當然如果你服務的企業有存在 102年的夢想，那我建議直接使用 datetime型別。2038年的時候，雖然我們(80後)估計已經在家養老或者身居高層，為了避免給後來的運維人員留坑，建議不要使用 timestamp 欄位。
四推薦閱讀
1 datatime和timstmap初始化
2 datetime官方文件
3 說說time_zone 帶來的效能問題

【MySQL】時間型別儲存格式選擇

相關文章