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

當你需要保存日期時間數據時，一個問題來了：你應該使用 MySQL 中的什麼類型？使用 MySQL 原生的 DATE 類型還是使用 INT 欄位把日期和時間保存為一個純數字呢？

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

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

原生的 MySQL Datetime 數據類型

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。

作為一個例子：

CREATE TABLE `datetime_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `measured_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `measured_on` (`measured_on`)
) ENGINE=InnoDB;

CREATE TABLE `timestamp_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `measured_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `measured_on` (`measured_on`)
) ENGINE=InnoDB;

除了原生的日期時間表示方法，還有另一種常用的存儲日期和時間信息的方法。即使用 INT 欄位保存 Unix 時間（從1970 年 1 月 1 日協調世界時（UTC）建立所經過的秒數）。

MySQL 也提供了只保存時間信息中的一部分的方式，通過使用 Date、Year 或 Time 類型。由於這篇文章是關於保存準確時間點的最佳方式的，我們沒有討論這些不那麼精確的局部類型。

使用 INT 類型保存 Unix 時間

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

INSERT INTO `vertabelo`.`sampletable`
(
 `id`,
 `measured_on` ### INT 類型的列
)
VALUES
(
 1,
 946684801
 ### 至 01/01/2000 @ 12:00am (UTC) 的 UNIX 時間戳 http://unixtimestamp.com
);

這就是關於它的所有內容了。它僅僅是個簡單的 INT 列，MySQL 的處理方式是這樣的：在內部使用 4 個位元組保存那些數據。所以如果你在這個列上使用 SELECT 你將會得到一個數字。如果你想把這個列用作日期進行比較，下面的查詢並不能正確工作：

SELECT
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
    measured_on > '2016-01-01' ### measured_on 會被作為字元串比較以進行查詢
LIMIT 5;

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

SELECT
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
    FROM_UNIXTIME(measured_on) > '2016-01-01'
LIMIT 5;

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

SELECT
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
   measured_on > 1451606400
LIMIT 5;

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

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

使用 Datetime 和 Timestamp

Datetime 和 Timestamp 幾乎以同樣的方式工作。兩種都保存日期和時間信息，毫秒部分最高精確度都是 6 位數。同時，使用人類可讀的日期形式如 "2016-01-01" （為了便於比較）都能工作。查詢時兩種類型都支持「寬鬆格式」。寬鬆的語法允許任何標點符號作為分隔符。例如，"YYYY-MM-DD HH:MM:SS" 和 "YY-MM-DD HH:MM:SS" 兩種形式都可以。在寬鬆格式情況下以下任何一種形式都能工作：

2012-12-31 11:30:45
2012^12^31 11+30+45
2012/12/31 11*30*45
2012@12@31 11^30^45

其它寬鬆格式也是允許的；你可以在 MySQL 參考手冊 找到所有的格式。

默認情況下，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 文檔：

MySQL 把 TIMESTAMP 值從當前的時區轉換到 UTC 再存儲，獲取時再從 UTC 轉回當前的時區。（其它類型如 DATETIME 不會這樣，它們會「原樣」保存。） 默認情況下，每個連接的當前時區都是伺服器的時區。時區可以基於連接設置。只要時區設置保持一致，你就能得到和保存的相同的值。如果你保存了一個 TIMESTAMP 值，然後改變了時區再獲取這個值，獲取到的值和你存儲的是不同的。這是因為在寫入和查詢的會話上沒有使用同一個時區。當前時區可以通過系統變數 time_zone 的值得到。更多信息，請查看 MySQL Server Time Zone Support。

對比總結

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

基準測試 INT、Timestamp 和 Datetime 的性能

為了比較這些類型的性能，我會使用我創建的一個天氣預報網路的 150 萬記錄（準確說是 1,497,421）。這個網路每分鐘都收集數據。為了讓這些測試可復現，我已經刪除了一些私有列，所以你可以使用這些數據運行你自己的測試。

基於我原始的表格，我創建了三個版本：

• datetimemeasures 表在 measured_on 列使用 Datetime 類型，表示天氣預報記錄的測量時間
• timestampmeasures 表在 measured_on 列使用 Timestamp 類型
• inttimestampmeasures 表在 measured_on 列使用 INT (unsigned) 類型

這三個表擁有完全相同的數據；唯一的差別就是 measured_on 欄位的類型。所有表都在 measured_on 列上設置了一個索引。

基準測試工具

為了評估這些數據類型的性能，我使用了兩種方法。一種基於 Sysbench，它的官網是這麼描述的：

「... 一個模塊化、跨平台和多線程的基準測試工具，用以評估那些對運行高負載資料庫的系統非常重要的系統參數。」

這個工具是 MySQL 文檔中推薦的。

如果你使用 Windows （就像我），你可以下載一個包含可執行文件和我使用的測試查詢的 zip 文件。它們基於 一種推薦的基準測試方法。

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

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

這會正常工作，這裡 sysbench_test_file.lua 是測試文件，並包含了各個測試中指向各個表的 SQL 查詢。

為了進一步驗證結果，我也運行了 mysqlslap。它的官網是這麼描述的：

「mysqlslap 是一個診斷程序，為模擬 MySQL 伺服器的客戶端負載並報告各個階段的用時而設計。它工作起來就像是很多客戶端在同時訪問伺服器。」

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

基準測試

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

• 時間範圍選擇
  • 在 Datetime 和 Timestamp 數據類型上這允許我們直接比較而不需要使用任何特殊的日期函數。
  • 同時，我們可以評估在 INT 類型的列上使用日期函數相對於使用簡單的數值比較的影響。為了做到這些我們需要把範圍轉換為 Unix 時間戳數值。
• 日期函數選擇
  • 與前個測試中比較操作針對一個簡單的 DATE 值相反，這個測試使得我們可以評估使用日期函數作為 「WHERE」 子句的一部分的性能。
  • 我們還可以測試一個場景，即我們必須使用一個函數將 INT 列轉換為一個合法的 DATE 類型然後執行查詢。
• count() 查詢
  • 作為對前面測試的補充，這將評估在三種不同的表示類型上進行典型的統計查詢的性能。

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

關於 SQL_NO_CACHE

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

測試 1：選擇一個日期範圍中的值

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

查詢 1 和 Datetime：

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
      AND m.measured_on < &apos;2016-02-01 00:00:00.0&apos;;

性能

Sysbench cmd> 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

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.datetimemeasures m WHERE m.measured_on > &apos;2016-01-01 00:00:00.0&apos; AND m.measured_on < &apos;2016-02-01 00:00:00.0&apos;" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

查詢 1 和 Timestamp：

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
        AND m.measured_on < &apos;2016-02-01 00:00:00.0&apos;;

性能

Sysbench cmd> 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

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.timestampmeasures m WHERE m.measured_on > &apos;2016-01-01 00:00:00.0&apos; AND m.measured_on < &apos;2016-02-01 00:00:00.0&apos;" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

查詢 1 和 INT：

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    FROM_UNIXTIME(m.measured_on) > '2016-01-01 00:00:00.0'
        AND FROM_UNIXTIME(m.measured_on) < &apos;2016-02-01 00:00:00.0&apos;;

性能

Sysbench cmd> 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

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE FROM_UNIXTIME(m.measured_on) > &apos;2016-01-01 00:00:00.0&apos; AND FROM_UNIXTIME(m.measured_on) < &apos;2016-02-01 00:00:00.0&apos;" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

另一種 INT 上的查詢 1：

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

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    m.measured_on > 1451617200
        AND m.measured_on < 1454295600;

性能

Sysbench cmd> 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

mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE m.measured_on > 1451617200 AND m.measured_on < 1454295600" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

測試 1 總結

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

測試 2：選擇星期一產生的記錄

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

查詢 2 和 Datetime：

SELECT SQL_NO_CACHE measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

Sysbench cmd> 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

mysqlslap cmd> 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

查詢 2 和 Timestamp：

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

Sysbench cmd> 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

mysqlslap cmd> 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

查詢 2 和 INT：

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0; # MONDAY

性能

Sysbench cmd> 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

mysqlslap cmd> 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

測試 2 總結

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

測試 3：選擇星期一產生的記錄總數

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

查詢 3 和 Datetime：

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

Sysbench cmd> 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

mysqlslap cmd> 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

查詢 3 和 Timestamp：

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

Sysbench cmd> 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

mysqlslap cmd> 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

查詢 3 和 INT：

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0; # MONDAY

性能

Sysbench cmd> 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

mysqlslap cmd> 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

測試 3 總結

再一次，兩個基準測試工具都顯示 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 是可以的。我們已經看到，它執行簡單數值比較查詢時非常快，因為只是在處理簡單的數字。

Timestamp 怎麼樣呢？如果 Datetime 相對於 Timestamp 的優勢不適用於你特殊的場景，你最好使用時間戳。閱讀這篇文章後，你對三種類型間的區別應該有了更好的理解，可以根據你的需要做出最佳的選擇。

via: http://www.vertabelo.com/blog/technical-articles/what-datatype-should-you-use-to-represent-time-in-mysql-we-compare-datetime-timestamp-and-int

作者：Francisco Claria 譯者：bianjp 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive