MySQL 中你應該使用什麼資料類型表示時間？

當你需要儲存日期時間資料時，一個問題來了：你應該使用 mysql 中的什麼類型？使用 mysql 原生的 date 類型還是使用 int 字段把日期和時間儲存為一個純數字呢？

在這篇文章中，我将解釋 mysql 原生的方案，并給出一個最常用資料類型的對比表。我們也将對一些典型的查詢做基準測試，然後得出在給定場景下應該使用什麼資料類型的結論。

如果你想直接看結論，請翻到文章最下方。

<a target="_blank"></a>

datetime 資料表示一個時間點。這可以用作日志記錄、物聯網時間戳、月曆事件資料，等等。mysql 有兩種原生的類型可以将這種資訊儲存在單個字段中：datetime 和 timestamp。mysql 文檔中是這麼介紹這些資料類型的：

datetime 類型用于儲存同時包含日期和時間兩部分的值。mysql 以 'yyyy-mm-dd hh:mm:ss' 形式接收和顯示 datetime 類型的值。 timestamp 類型用于儲存同時包含日期和時間兩部分的值。 datetime 或 timestamp 類型的值可以在尾部包含一個毫秒部分，精确度最高到微秒（6 位數）。 timestamp 和 datetime 資料類型提供自動初始化和更新到目前的日期和時間的功能，隻需在列的定義中設定 default currenttimestamp 和 on update currenttimestamp。

作為一個例子：

<code>create table `datetime_example` (</code>

<code>`id` int(11) not null auto_increment,</code>

<code>`measured_on` datetime not null default current_timestamp on update current_timestamp,</code>

<code>primary key (`id`),</code>

<code>key `measured_on` (`measured_on`)</code>

<code>) engine=innodb;</code>

<code>create table `timestamp_example` (</code>

<code>`measured_on` timestamp not null default current_timestamp on update current_timestamp,</code>

除了原生的日期時間表示方法，還有另一種常用的存儲日期和時間資訊的方法。即使用 int 字段儲存 unix 時間（從1970 年 1 月 1 日協調世界時（utc）建立所經過的秒數）。

mysql 也提供了隻儲存時間資訊中的一部分的方式，通過使用 date、year 或 time 類型。由于這篇文章是關于儲存準确時間點的最佳方式的，我們沒有讨論這些不那麼精确的局部類型。

使用一個簡單的 int 列儲存 unix 時間是最普通的方法。使用 int，你可以確定你要儲存的數字可以快速、可靠地插入到表中，就像這樣：

<code>insert into `vertabelo`.`sampletable`</code>

<code>(</code>

<code>`id`,</code>

<code>`measured_on` ### int 類型的列</code>

<code>)</code>

<code>values</code>

<code>1,</code>

<code>946684801</code>

<code>### 至 01/01/2000 @ 12:00am (utc) 的 unix 時間戳 http://unixtimestamp.com</code>

<code>);</code>

這就是關于它的所有内容了。它僅僅是個簡單的 int 列，mysql 的處理方式是這樣的：在内部使用 4 個位元組儲存那些資料。是以如果你在這個列上使用 select 你将會得到一個數字。如果你想把這個列用作日期進行比較，下面的查詢并不能正确工作：

<code>select</code>

<code>id, measured_on, from_unixtime(measured_on)</code>

<code>from</code>

<code>vertabelo.inttimestampmeasures</code>

<code>where</code>

<code>measured_on &gt; '2016-01-01' ### measured_on 會被作為字元串比較以進行查詢</code>

<code>limit 5;</code>

這是因為 mysql 把 int 視為數字，而非日期。為了進行日期比較，你必須要麼擷取（ lctt 譯注：從 1970-01-01 00:00:00）到 2016-01-01 經過的秒數，要麼使用 mysql 的 from_unixtime() 函數把 int 列轉為 date 類型。下面的查詢展示了 from_unixtime() 函數的用法：

<code>from_unixtime(measured_on) &gt; '2016-01-01'</code>

這會正确地擷取到日期在 2016-01-01 之後的記錄。你也可以直接比較數字和 2016-01-01 的 unix 時間戳表示形式，即 1451606400。這樣做意味着不用使用任何特殊的函數，因為你是在直接比較數字。查詢如下：

<code>measured_on &gt; 1451606400</code>

假如這種方式不夠高效甚至提前做這種轉換是不可行的話，那該怎麼辦？例如，你想擷取 2016 年所有星期三的記錄。要做到這樣而不使用任何 mysql 日期函數，你就不得不查出 2016 年每個星期三的開始和結束時間的 unix 時間戳。然後你不得不寫很大的查詢，至少要在 where 中包含 104 個比較。（2016 年有 52 個星期三，你不得不考慮一天的開始（0:00 am）和結束（11:59:59 pm）...）

結果是你很可能最終會使用 from_unixtime() 轉換函數。既然如此，為什麼不試下真正的日期類型呢？

datetime 和 timestamp 幾乎以同樣的方式工作。兩種都儲存日期和時間資訊，毫秒部分最高精确度都是 6 位數。同時，使用人類可讀的日期形式如 "2016-01-01" （為了便于比較）都能工作。查詢時兩種類型都支援“寬松格式”。寬松的文法允許任何标點符号作為分隔符。例如，"yyyy-mm-dd hh:mm:ss" 和 "yy-mm-dd hh:mm:ss" 兩種形式都可以。在寬松格式情況下以下任何一種形式都能工作：

<code>2012-12-31 11:30:45</code>

<code>2012^12^31 11+30+45</code>

<code>2012/12/31 11*30*45</code>

<code>2012@12@31 11^30^45</code>

預設情況下，datetime 和 timestamp 兩種類型查詢結果都以标準輸出格式顯示 —— 年-月-日 時:分:秒 （如 2016-01-01 23:59:59）。如果使用了毫秒部分，它們應該以小數值出現在秒後面 （如 2016-01-01 23:59:59.5）。

timestamp 和 datetime 的核心不同點主要在于 mysql 在内部如何表示這些資訊：兩種都以二進制而非字元串形式存儲，但在表示日期/時間部分時 timestamp （4 位元組） 比 datetime （5 位元組） 少使用 1 位元組。當儲存毫秒部分時兩種都使用額外的空間 （1-3 位元組）。如果你存儲 150 萬條記錄，這種 1 位元組的差異是微不足道的：

150 萬條記錄 * 每條記錄 1 位元組 / (1048576 位元組/mb) = 1.43 mb

timestamp 節省的 1 位元組是有代價的：你隻能存儲從 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999' 之間的時間。而 datetime 允許你存儲從 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999' 之間的任何時間。

另一個重要的差别 —— 很多 mysql 開發者沒意識到的 —— 是 mysql 使用伺服器的時區轉換 timestamp 值到它的 utc 等價值再儲存。當擷取值是它會再次進行時區轉換，是以你得回了你“原始的”日期/時間值。有可能，下面這些情況會發生。

理想情況下，如果你一直使用同一個時區，mysql 會擷取到和你存儲的同樣的值。以我的經驗，如果你的資料庫涉及時區變換，你可能會遇到問題。例如，伺服器變化（比如，你把資料庫從都柏林的一台伺服器遷移到加利福尼亞的一台伺服器上，或者你隻是修改了一下伺服器的時區）時可能會發生這種情況。不管哪種方式，如果你擷取資料時的時區是不同的，資料就會受影響。

datetime 列不會被資料庫改變。無論時區怎樣配置，每次都會儲存和擷取到同樣的值。就我而言，我認為這是一個更可靠的選擇。

mysql 文檔：

在深入探讨使用各資料類型的性能差異之前，讓我們先看一個總結表格以給你更多了解。每種類型的弱點以紅色顯示。

特性

datetime

timestamp

int （儲存 unix 時間）

原生時間表示

是

否，是以大多數操作需要先使用轉換函數，如 from_unixtime()

能儲存毫秒

是，最高 6 位精度

否

合法範圍

'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999

'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'

若使用 unsigned, '1970-01-01 00:00:01.000000; 理論上最大到 '2106-2-07 06:28:15'

自動初始化（mysql 5.6.5+）

否，必須使用正确的格式

值被轉換到 utc 存儲

可轉換到其它類型

是，如果值在合法的 timestamp 範圍中

是，總是

是，如果值在合法的範圍中并使用轉換函數

5 位元組（如果使用了毫秒部分，再加最多 3 位元組）

4 位元組 （如果使用了毫秒部分，再加最多 3 位元組）

4 位元組 （不允許毫秒部分）

無需使用函數即可作為真實日期可讀

否，你必須格式化輸出

資料分區

是，使用 int 上的任何合法操作

為了比較這些類型的性能，我會使用我建立的一個天氣預報網絡的 150 萬記錄（準确說是 1,497,421）。這個網絡每分鐘都收集資料。為了讓這些測試可複現，我已經删除了一些私有列，是以你可以使用這些資料運作你自己的測試。

基于我原始的表格，我建立了三個版本：

<code>datetimemeasures</code> 表在 <code>measured_on</code> 列使用 datetime 類型，表示天氣預報記錄的測量時間

<code>timestampmeasures</code> 表在 <code>measured_on</code> 列使用 timestamp 類型

<code>inttimestampmeasures</code> 表在 <code>measured_on</code> 列使用 int (unsigned) 類型

這三個表擁有完全相同的資料；唯一的差别就是 <code>measured_on</code> 字段的類型。所有表都在 <code>measured_on</code> 列上設定了一個索引。

“... 一個子產品化、跨平台和多線程的基準測試工具，用以評估那些對運作高負載資料庫的系統非常重要的系統參數。”

為了執行一個給定的測試，你可以使用下面的指令（插入你自己的連接配接參數）：

<code>sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=sysbench_test_file.lua --num-threads=8 --max-requests=100 run</code>

這會正常工作，這裡 <code>sysbench_test_file.lua</code> 是測試檔案，并包含了各個測試中指向各個表的 sql 查詢。

記得這些測試中最重要的不是所需的絕對時間。而是在不同資料類型上執行相同查詢時的相對時間。這兩個基準測試工具的測試時間不一定相同，因為不同工具的工作方式不同。重要的是資料類型的比較，随着我們深入到測試中，這将會變得清楚。

我将使用三種可以評估幾個性能方面的查詢：

時間範圍選擇

在 datetime 和 timestamp 資料類型上這允許我們直接比較而不需要使用任何特殊的日期函數。

同時，我們可以評估在 int 類型的列上使用日期函數相對于使用簡單的數值比較的影響。為了做到這些我們需要把範圍轉換為 unix 時間戳數值。

日期函數選擇

與前個測試中比較操作針對一個簡單的 date 值相反，這個測試使得我們可以評估使用日期函數作為 “where” 子句的一部分的性能。

我們還可以測試一個場景，即我們必須使用一個函數将 int 列轉換為一個合法的 date 類型然後執行查詢。

count() 查詢

作為對前面測試的補充，這将評估在三種不同的表示類型上進行典型的統計查詢的性能。

我們将在這些測試中覆寫一些常見的場景，并看到三種類型上的性能表現。

當在查詢中使用 sql_no_cache 時，伺服器不使用查詢緩存。它既不檢查查詢緩存以确認結果是不是已經在那兒了，也不會儲存查詢結果。是以，每個查詢将反映真實的性能影響，就像每次查詢都是第一次被調用。

這個查詢傳回總計 1,497,421 行記錄中的 75,706 行。

查詢 1 和 datetime：

<code>select sql_no_cache</code>

<code>measured_on</code>

<code>vertabelo.datetimemeasures m</code>

<code>m.measured_on &gt; '2016-01-01 00:00:00.0'</code>

<code>and m.measured_on &lt; '2016-02-01 00:00:00.0';</code>

性能

響應時間 (ms)

sysbench

mysqlslap

最小

152

296

最大

1261

3203

平均

362

809

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=datetime.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.datetimemeasures m where m.measured_on &gt; '2016-01-01 00:00:00.0' and m.measured_on &lt; '2016-02-01 00:00:00.0'" --host=localhost --user=root --concurrency=8 --iterations=100 --no-drop --create-schema=vertabelo</code>

查詢 1 和 timestamp：

<code>vertabelo.timestampmeasures m</code>

214

359

1389

3313

431

1004

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=timestamp.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.timestampmeasures m where m.measured_on &gt; '2016-01-01 00:00:00.0' and m.measured_on &lt; '2016-02-01 00:00:00.0'" --host=localhost --user=root --concurrency=8 --iterations=100 --no-drop --create-schema=vertabelo</code>

查詢 1 和 int：

<code>vertabelo.inttimestampmeasures m</code>

<code>from_unixtime(m.measured_on) &gt; '2016-01-01 00:00:00.0'</code>

<code>and from_unixtime(m.measured_on) &lt; '2016-02-01 00:00:00.0';</code>

2472

7968

6554

10312

4107

8527

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=int.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.inttimestampmeasures m where from_unixtime(m.measured_on) &gt; '2016-01-01 00:00:00.0' and from_unixtime(m.measured_on) &lt; '2016-02-01 00:00:00.0'" --host=localhost --user=root --concurrency=8 --iterations=100 --no-drop --create-schema=vertabelo</code>

另一種 int 上的查詢 1：

由于這是個相當直接的範圍搜尋，而且查詢中的日期可以輕易地轉為簡單的數值比較，我将它包含在了這個測試中。結果證明這是最快的方法 （你大概已經預料到了），因為它僅僅是比較數字而沒有使用任何日期轉換函數：

<code>m.measured_on &gt; 1451617200</code>

<code>and m.measured_on &lt; 1454295600;</code>

88

171

275

2157

165

514

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=basic_int.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.inttimestampmeasures m where m.measured_on &gt; 1451617200 and m.measured_on &lt; 1454295600" --host=localhost --user=root --concurrency=8 --iterations=100 --no-drop --create-schema=vertabelo</code>

測試 1 總結

平均響應時間 (ms)

相對于 datetime 的速度

-

慢 19%

慢 24%

int

慢 1134%

慢 1054%

另一種 int 查詢

快 55%

快 36%

兩種基準測試工具都顯示 datetime 比 timestamp 和 int 更快。但 datetime 沒有我們在另一種 int 查詢中使用的簡單數值比較快。

這個查詢傳回總計 1,497,421 行記錄中的 221,850 行。

查詢 2 和 datetime：

<code>select sql_no_cache measured_on</code>

<code>weekday(m.measured_on) = 0; # monday</code>

1874

4343

6168

7797

3127

6103

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=datetime_1.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.datetimemeasures m where weekday(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo</code>

查詢 2 和 timestamp：

2688

5953

6666

13531

3653

8412

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=timestamp_1.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.timestampmeasures m where weekday(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo</code>

查詢 2 和 int：

<code>weekday(from_unixtime(m.measured_on)) = 0; # monday</code>

2051

5844

7007

10469

3486

8088

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=int_1.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache measured_on from vertabelo.inttimestampmeasures m where weekday(from_unixtime(m.measured_on)) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo</code>

測試 2 總結

慢 17%

慢 38%

慢 11%

慢 32%

再次，在兩個基準測試工具中 datetime 比 timestamp 和 int 快。但在這個測試中，int 查詢 —— 即使它使用了一個函數以轉換日期 —— 比 timestamp 查詢更快得到結果。

這個查詢傳回一行，包含産生于星期一的所有記錄的總數（從總共 1,497,421 行可用記錄中）。

查詢 3 和 datetime：

<code>count(measured_on)</code>

1720

4063

4594

7812

2797

5540

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=datetime_1_count.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache count(measured_on) from vertabelo.datetimemeasures m where weekday(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo</code>

查詢 3 和 timestamp：

1907

4578

5437

10235

3408

7102

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=timestamp_1_count.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache count(measured_on) from vertabelo.timestampmeasures m where weekday(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo</code>

查詢 3 和 int：

2108

5609

4764

9735

3307

7416

<code>sysbench cmd&gt; sysbench --mysql-table-engine=innodb --mysql-db=vertabelo --mysql-user=root --mysql-host=localhost --mysql-password= --test=int_1_count.lua --num-threads=8 --max-requests=100 run</code>

<code>mysqlslap cmd&gt; mysqlslap --query="select sql_no_cache count(measured_on) from vertabelo.inttimestampmeasures m where weekday(from_unixtime(m.measured_on)) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo</code>

測試 3 總結

慢 22%

慢 28%

慢 18%

慢 33%

再一次，兩個基準測試工具都顯示 datetime 比 timestamp 和 int 快。不能判斷 int 是否比 timestamp 快，因為 mysqlslap 顯示 int 比 timestamp 略快而 sysbench 卻相反。

注意： 所有測試都是在一台 windows 10 機器上本地運作的，這台機器擁有一個雙核 i7 cpu，16gb 記憶體，運作 mariadb v10.1.9，使用 innodb 引擎。

基于這些資料，我确信 datetime 是大多數場景下的最佳選擇。原因是：

更快（根據我們的三個基準測試）。

無需任何轉換即是人類可讀的。

不會因為時區變換産生問題。

隻比它的對手們多用 1 位元組

支援更大的日期範圍（從 1000 年到 9999 年）

如果你隻是存儲 unix 時間戳（并且在它的合法日期範圍内），而且你真的不打算在它上面使用任何基于日期的查詢，我覺得使用 int 是可以的。我們已經看到，它執行簡單數值比較查詢時非常快，因為隻是在處理簡單的數字。

原文釋出時間為：2016-09-03

本文來自雲栖社群合作夥伴“linux中國”