問題

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

• 這是一個做在線文檔產(chǎn)品的業(yè)務(wù)，需要給用戶展示文檔的編輯記錄，現(xiàn)在我們叫它【智能文檔】。
• 智能文檔會不定期給文檔數(shù)據(jù)打一個快照，保存起來。用戶可以在歷史記錄中查閱快照。
• 快照之間會展示具體的變更記錄，比如“用戶A 復(fù)制了一段文字”，“用戶B刪除了一個圖片”。
• 快照本身是動態(tài)生成和回收的，即距離現(xiàn)在越遠(yuǎn)的快照，留下來的越少（更稀疏的快照意味著相鄰快照之間的變更記錄會更多，本來是一天一個快照，展示這一天內(nèi)的變更記錄即可，后來變成了一周一個快照，于是需要展示這一周內(nèi)的變更記錄）

那如何實(shí)現(xiàn)查找兩個快照之間的【變更記錄】有哪些呢？

快照 和 變更記錄 預(yù)期是兩張表。首先我們不能將【變更記錄】通過 id 掛在某個【快照】上，因?yàn)槲覀兊目煺帐遣粩啾换厥盏?，這樣的話當(dāng)你回收快照時，也需要連帶著更新大量的【變更記錄】，出現(xiàn)寫擴(kuò)散。

另一個想法是，能否通過時間戳進(jìn)行比較？比如快照 A 的創(chuàng)建時間戳是 12345，快照 B 的創(chuàng)建時間戳是 23456。那么我只要【變更記錄】這張表也有一個時間戳字段，寫一個 SQL 查到兩個快照時間戳之間的變更記錄是不是就可以了？

寫出來 SQL 類似這樣：

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

那么，問題來了，這個 create_time，雖然這里我們直接拿時間戳比較，但真的是性能最好的么？建表的時候，我應(yīng)該用 datetime, timestamp 還是 int ?

今天我們就來看看到底有什么區(qū)別。

MySQL 支持的數(shù)據(jù)類型

任何一篇博客，教程都比不上官方文檔，大家選型有疑慮時還是建議先來看看 MySQL Data Types 。

Integer

我們先來看 integer 有什么類型。

SQL 標(biāo)準(zhǔn)中對于整數(shù)，提出了兩種類型：INTEGER(INT) 以及 SMALLINT。在此之外，MySQL 還額外提供了 TINYINT, MEDIUMINT, BIGINT 三種類型。

所以一共是五種:

可以看到，INT 其實(shí)和我們通常用的 int32 是一樣的，本質(zhì)是 2 的 31 次方 - 1，大概21億4千7百萬。（正整數(shù)以二進(jìn)制存儲。負(fù)整數(shù)以補(bǔ)碼存儲。一個Int類型數(shù)據(jù)占據(jù)空間4字節(jié)。每個字節(jié)8位，共32位。因此最大存儲2的31次方（從2的0次方開始）。但32位的第一位是符號位。所以2的31次方減1.簡單說Int類型占據(jù)4字節(jié)，所以是這個取值范圍。）

這里 BIGINT 就等價于 int64。

Datetime

datetime 其實(shí)是一個統(tǒng)稱，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 類型沒有具體的時間點(diǎn)，只能精確到【日期】，即 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'

因?yàn)榇饲暗南到y(tǒng)設(shè)計都是基于 32 位實(shí)現(xiàn)的，我們上面提到過，最多無非是 2 的 31次方 - 1，每個數(shù)代表一秒的話，最多表示 68 年。所以 Unix 選取了 1970年1月1日作為UNIX TIME的紀(jì)元時間(開始時間)。

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

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

• DEFAULT CURRENT_TIMESTAMP
• ON UPDATE CURRENT_TIMESTAMP

二者可以同時出現(xiàn)，也可以單獨(dú)出現(xiàn)，分幾種情況：

• 同時出現(xiàn)

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

此時 ts 和 dt 的默認(rèn)值就是當(dāng)前時間，當(dāng)這一行其他值發(fā)生變化時，也會自動把這兩個屬性更新為當(dāng)前時間。

• 只有 DEFAULT

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

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

• 只有 ON UPDATE

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

此時沒有指定默認(rèn)值，但發(fā)生更新時會改為當(dāng)前時間，這時的默認(rèn)值就是 type dependent，依賴類型了。 TIMESTAMP 的默認(rèn)值為 0，如果定義了 NULL 則默認(rèn)值為 NULL。

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

這次我們換成了 DATETIME，二者正好相反，不指定 DEFAULT 的話，默認(rèn)值為 NULL，但如果我們聲明了 NOT NULL，則默認(rèn)值變成 0。

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

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

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 個字節(jié)存儲（2的31次方-1，還記得么），能支持的時間范圍比 DATETIME 要小一倍，但它的特點(diǎn)在于，當(dāng)我們寫入時，MySQL會根據(jù)當(dāng)前 server 所在的時區(qū)進(jìn)行轉(zhuǎn)換，將值變成 UTC 時區(qū)的時間，再存儲。同樣的，在查詢的時候，MySQL 也會幫助我們轉(zhuǎn)成當(dāng)前時區(qū)再展示。這是 DATETIME 不具備的。

這樣的跨時區(qū)支持，在一些業(yè)務(wù)場景下是很有用的。畢竟存儲時間這件事情本身是很敏感的。海外用戶一開始請求到了新加坡機(jī)房，落了一個時間。隨后跑到歐洲玩耍，在法國重新訪問，發(fā)現(xiàn)跟本地時間完全對不上，這就有問題了。

所以 TIMESTAMP 的思路就是，大家都以 UTC 時間為準(zhǔn)，這是個基線，不管你是哪個時區(qū)的，我都要轉(zhuǎn)成統(tǒng)一的時間，查詢的時候給你轉(zhuǎn)回去就是了。

我們可以用 show variables like '%time_zone%'; 來查看當(dāng)前庫的時區(qū)：

需要注意，當(dāng)MySQL參數(shù)time_zone=system時，查詢timestamp字段會調(diào)用系統(tǒng)時區(qū)做時區(qū)轉(zhuǎn)換，而由于系統(tǒng)時區(qū)存在全局鎖問題，在多并發(fā)大數(shù)據(jù)量訪問時會導(dǎo)致線程上下文頻繁切換，CPU使用率暴漲，系統(tǒng)響應(yīng)變慢設(shè)置假死。

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"; 來設(shè)置時區(qū)。每個連接可以使用不同的時區(qū)

可以實(shí)驗(yàn)一下，在一個時區(qū)寫入 TIMESTAMP 數(shù)據(jù)，切換時區(qū)后讀出來，顯示的時間是不一樣的，而 DATETIME 則是完全一致的。demo

DATETIME

DATETIME 底層采用 8 個字節(jié)存儲，沒有跨時區(qū)的支持，結(jié)果直接展示。你存進(jìn)去的是什么時間，讀到的就是什么時間。不過我們?nèi)绻枰鐣r區(qū)，也不是沒有辦法，可以在讀出來 DATETIME 后轉(zhuǎn)為時間戳，從業(yè)務(wù)代碼層面來處理，想轉(zhuǎn)成什么時區(qū)都 OK。

這里不用擔(dān)心 2038 年的限制，雖然空間大了一倍，但通常情況下不會造成多大性能影響。

Integer

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

為什么我們能用一個整數(shù)來代表時間呢？這里本質(zhì)是我們給它賦予了【時間戳】的語義。

雖然整數(shù)的上下限更大（比如我們用 BIGINT，可以支持 2 的 63 次方 - 1 的數(shù)據(jù)），但是，但是，用法是關(guān)鍵。

如果你打算還用時間戳函數(shù)進(jìn)行生成和轉(zhuǎn)換，那就需要關(guān)注 2038 年這個限制，本質(zhì)上和 TIMESTAMP 是沒有區(qū)別的。

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

BETWEEN 查詢

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

如果是整型，我們其實(shí)經(jīng)常使用 BETWEEN 來進(jìn)行查詢：

SELECT *
FROM contacts
WHERE contact_id BETWEEN 100 AND 200;

它和下面直接用運(yùn)算符的形式是等價的，注意 BETWEEN 是個閉區(qū)間：

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';

當(dāng)然，我們也可以用 now() 等函數(shù)作為輔助，注意 between 里面一定要先寫小的時間，and 后面寫更大的時間點(diǎn)。

性能差異

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.

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

DATETIME 底層存儲實(shí)現(xiàn)是 BigInt，索引存儲上和 BigInt 的處理是幾乎一模一樣的，所以 BigInt 支持的索引查詢，datetime也支持。

加上索引后的速度如何，推薦大家閱讀這一篇 benchmark MYSQL 數(shù)據(jù)庫時間字段 INT,TIMESTAMP,DATETIME 性能效率的比較介紹

這里引用一下結(jié)論：

• 對于 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直接時間比較。

大家可以嘗試一下，結(jié)合你的業(yè)務(wù)場景，跑一下 explain 看看。

到此這篇關(guān)于MySQL 時間類型用 datetime, timestamp 還是 integer 更好的文章就介紹到這了,更多相關(guān)MySQL 時間類型使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！