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

一  前言

  昨天在給開發同學做資料庫設計規範分享的時候，講到時間字段常用的有三個選擇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

存儲格式: 值以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 字段。

四 推薦閱讀