MySQL 時間型別用 datetime, timestamp 還是 integer 更好

問題

今天我們來探討一個有意思的問題，先說場景：

• 這是一個做線上檔案產品的業務，需要給使用者展示檔案的編輯記錄，現在我們叫它【智慧檔案】。
• 智慧檔案會不定期給檔案資料打一個快照，儲存起來。使用者可以在歷史記錄中查閱快照。
• 快照之間會展示具體的變更記錄，比如“使用者A 複製了一段文字”，“使用者B刪除了一個圖片”。
• 快照本身是動態生成和回收的，即距離現在越遠的快照，留下來的越少（更稀疏的快照意味著相鄰快照之間的變更記錄會更多，本來是一天一個快照，展示這一天內的變更記錄即可，後來變成了一週一個快照，於是需要展示這一週內的變更記錄）

那如何實現查詢兩個快照之間的【變更記錄】有哪些呢？

快照 和 變更記錄 預期是兩張表。首先我們不能將【變更記錄】通過 id 掛在某個【快照】上，因為我們的快照是不斷被回收的，這樣的話當你回收快照時，也需要連帶著更新大量的【變更記錄】，出現寫擴散。

另一個想法是，能否通過時間戳進行比較？比如快照 A 的建立時間戳是 12345，快照 B 的建立時間戳是 23456。那麼我只要【變更記錄】這張表也有一個時間戳欄位，寫一個 SQL 查到兩個快照時間戳之間的變更記錄是不是就可以了？

寫出來 SQL 類似這樣：

select * from change_record where create_time > 12345 and create_time < 23456;

那麼，問題來了，這個 create_time，雖然這裡我們直接拿時間戳比較，但真的是效能最好的麼？建表的時候，我應該用 datetime, timestamp 還是 int ?

今天我們就來看看到底有什麼區別。

MySQL 支援的資料型別

任何一篇部落格，教學都比不上官方檔案，大家選型有疑慮時還是建議先來看看 MySQL Data Types 。

Integer

我們先來看 integer 有什麼型別。

SQL 標準中對於整數，提出了兩種型別：INTEGER(INT) 以及 SMALLINT。在此之外，MySQL 還額外提供了 TINYINT, MEDIUMINT, BIGINT 三種型別。

所以一共是五種:

可以看到，INT 其實和我們通常用的 int32 是一樣的，本質是 2 的 31 次方 - 1，大概21億4千7百萬。（正整數以二進位制儲存。負整數以二補數儲存。一個Int型別資料佔據空間4位元組。每個位元組8位元，共32位元。因此最大儲存2的31次方（從2的0次方開始）。但32位元的第一位是符號位。所以2的31次方減1.簡單說Int型別佔據4位元組，所以是這個取值範圍。）

這裡 BIGINT 就等價於 int64。

Datetime

datetime 其實是一個統稱，MySQL 提供了 DATE, DATETIME, TIMESTAMP 三種型別。

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

DATE 型別沒有具體的時間點，只能精確到【日期】，即 YYYY-MM-DD，比如 1994-06-09。

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

DATETIME 則同時支援【日期】和【時間】，格式為 YYYY-MM-DD hh:mm:ss。如 1995-04-29 17:11:12。

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

TIMESTAMP 同樣也支援【日期】和【時間】，但由於帶上了時間戳的語意，就不如 DATETIME 支援的範圍那麼寬了。UTC 時間，從'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07'

因為此前的系統設計都是基於 32 位實現的，我們上面提到過，最多無非是 2 的 31次方 - 1，每個數代表一秒的話，最多表示 68 年。所以 Unix 選取了 1970年1月1日作為UNIX TIME的紀元時間(開始時間)。

這裡我們主要還是關心 DATETIME 以及 TIMESTAMP，二者除了整秒之外，還可以支援小數點後的部分，最多到 microseconds （6位）精度。格式為 'YYYY-MM-DD hh:mm:ss[.fraction]'，比如 '2038-01-19 03:14:07.999999' （事實上這也是 TIMESTAMP 能支援的最大值）。

除此之外，二者也都支援 自動初始化（Automatic Initialization)。這裡要用到的兩個大殺器：

• DEFAULT CURRENT_TIMESTAMP
• ON UPDATE CURRENT_TIMESTAMP

二者可以同時出現，也可以單獨出現，分幾種情況：

• 同時出現

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

此時 ts 和 dt 的預設值就是當前時間，當這一行其他值發生變化時，也會自動把這兩個屬性更新為當前時間。

• 只有 DEFAULT

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

此時只有初始化的時候才會寫入當前時間，隨後更新時不會變動。（當然，我們也可以把 CURRENT_TIMESTAMP 換成一個常數，比如 0，語法上是支援的，只不過那樣就不是當前時間了）

• 只有 ON UPDATE

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
  );

此時沒有指定預設值，但發生更新時會改為當前時間，這時的預設值就是 type dependent，依賴型別了。 TIMESTAMP 的預設值為 0，如果定義了 NULL 則預設值為 NULL。

CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, 
  -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
  );

這次我們換成了 DATETIME，二者正好相反，不指定 DEFAULT 的話，預設值為 NULL，但如果我們宣告了 NOT NULL，則預設值變成 0。

• 我們可以使用 show variables like '%explicit_defaults_for_timestamp%'; 來檢視是否禁用了自動初始化和更新。

• 雖然在MySQL中可以對時間戳欄位賦值或更新，但建議僅在必要的情況下對時間戳列進行顯式插入和更新。

TIMESTAMP

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

TIMESTAMP 底層採用 4 個位元組儲存（2的31次方-1，還記得麼），能支援的時間範圍比 DATETIME 要小一倍，但它的特點在於，當我們寫入時，MySQL會根據當前 server 所在的時區進行轉換，將值變成 UTC 時區的時間，再儲存。同樣的，在查詢的時候，MySQL 也會幫助我們轉成當前時區再展示。這是 DATETIME 不具備的。

這樣的跨時區支援，在一些業務場景下是很有用的。畢竟儲存時間這件事情本身是很敏感的。海外使用者一開始請求到了新加坡機房，落了一個時間。隨後跑到歐洲玩耍，在法國重新存取，發現跟本地時間完全對不上，這就有問題了。

所以 TIMESTAMP 的思路就是，大家都以 UTC 時間為準，這是個基線，不管你是哪個時區的，我都要轉成統一的時間，查詢的時候給你轉回去就是了。

我們可以用 show variables like '%time_zone%'; 來檢視當前庫的時區：

需要注意，當MySQL引數time_zone=system時，查詢timestamp欄位會呼叫系統時區做時區轉換，而由於系統時區存在全域性鎖問題，在多並行巨量資料量存取時會導致執行緒上下文頻繁切換，CPU使用率暴漲，系統響應變慢設定假死。

The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

使用 SET TIME_ZONE = 'america/new_york"; 來設定時區。每個連線可以使用不同的時區

可以實驗一下，在一個時區寫入 TIMESTAMP 資料，切換時區後讀出來，顯示的時間是不一樣的，而 DATETIME 則是完全一致的。demo

DATETIME

DATETIME 底層採用 8 個位元組儲存，沒有跨時區的支援，結果直接展示。你存進去的是什麼時間，讀到的就是什麼時間。不過我們如果需要跨時區，也不是沒有辦法，可以在讀出來 DATETIME 後轉為時間戳，從業務程式碼層面來處理，想轉成什麼時區都 OK。

這裡不用擔心 2038 年的限制，雖然空間大了一倍，但通常情況下不會造成多大效能影響。

Integer

這裡在討論完 DATETIME, TIMESTAMP 之後，我們回過頭來看看 Integer。

為什麼我們能用一個整數來代表時間呢？這裡本質是我們給它賦予了【時間戳】的語意。

雖然整數的上下限更大（比如我們用 BIGINT，可以支援 2 的 63 次方 - 1 的資料），但是，但是，用法是關鍵。

如果你打算還用時間戳函數進行生成和轉換，那就需要關注 2038 年這個限制，本質上和 TIMESTAMP 是沒有區別的。

所以，通常我們認為，用整型時間戳的形式，取值範圍也是 1970 年 1 月 1日起，到 2038 年截止，這個區間。用 BIGINT 的意義不大，只要它的語意還是時間戳，就需要遵循這個規範。

BETWEEN 查詢

回到我們一開始提到的案例，我們需要篩選出兩個時間點之間，有哪些【變更記錄】。

如果是整型，我們其實經常使用 BETWEEN 來進行查詢：

SELECT *
FROM contacts
WHERE contact_id BETWEEN 100 AND 200;

它和下面直接用運運算元的形式是等價的，注意 BETWEEN 是個閉區間：

SELECT *
FROM contacts
WHERE contact_id >= 100
AND contact_id <= 200;

同樣的，查詢 datetime 依然可以用 BETWEEN:

SELECT *
FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

下面兩個查詢也是等價的：

SELECT count(*) FROM `table` 
where 
    created_at>='2011-03-17 06:42:10' and created_at<='2011-03-17 07:42:50';

SELECT count(*) FROM `table` 
where 
    created_at between '2011-03-17 06:42:10' and '2011-03-17 07:42:50';

當然，我們也可以用 now() 等函數作為輔助，注意 between 裡面一定要先寫小的時間，and 後面寫更大的時間點。

效能差異

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

其實 DATETIME 和 TIMESTAMP 底層也是整型儲存（否則就不會按照 2 的31 次方，63 次方來支援了），算是一層封裝，提供了一系列時間函數使用。

DATETIME 底層儲存實現是 BigInt，索引儲存上和 BigInt 的處理是幾乎一模一樣的，所以 BigInt 支援的索引查詢，datetime也支援。

加上索引後的速度如何，推薦大家閱讀這一篇 benchmark MYSQL 資料庫時間欄位 INT,TIMESTAMP,DATETIME 效能效率的比較介紹

這裡參照一下結論：

• 對於 MyISAM 引擎，不建立索引的情況下（推薦），效率從高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和時間比較）> timestamp（直接和時間比較）> UNIXTIMESTAMP(datetime) 。
• 對於 MyISAM 引擎，建立索引的情況下，效率從高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和時間比較）>timestamp（直接和時間比較）>UNIXTIMESTAMP(datetime) 。
• 對於 InnoDB 引擎，沒有索引的情況下(不建議)，效率從高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIXTIMESTAMP(datetime)。
• 對於 InnoDB 引擎，建立索引的情況下，效率從高到低：int > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。
• 一句話，對於 MyISAM 引擎，採用 UNIX_TIMESTAMP(timestamp) 比較；對於InnoDB 引擎，建立索引，採用 int 或 datetime直接時間比較。

大家可以嘗試一下，結合你的業務場景，跑一下 explain 看看。

到此這篇關於MySQL 時間型別用 datetime, timestamp 還是 integer 更好的文章就介紹到這了,更多相關MySQL 時間型別使用內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！