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

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

1. 同時出現

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
複制代碼           

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

1. 隻有 DEFAULT

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
複制代碼           

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

1. 隻有 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 性能效率比較

這裡引用一下結論：

對于 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 看看。