MySQL日期類型的選擇

我們平時開發(fā)中不可避免的就是要存儲時間，比如我們要記錄操作表中這條記錄的時間、記錄轉(zhuǎn)賬的交易時間、記錄出發(fā)時間、用戶下單時間等等。

你會發(fā)現(xiàn)時間這個東西與我們開發(fā)的聯(lián)系還是非常緊密的，用的好與不好會給我們的業(yè)務(wù)甚至功能帶來很大的影響。所以，我們有必要重新出發(fā)，好好認(rèn)識一下這個東西。

不要用字符串存儲日期

和絕大部分對數(shù)據(jù)庫不太了解的新手一樣，我在大學(xué)的時候就這樣干過，甚至認(rèn)為這樣是一個不錯的表示日期的方法。畢竟簡單直白，容易上手。

但是，這是不正確的做法，主要會有下面兩個問題：

1. 字符串占用的空間更大！
2. 字符串存儲的日期效率比較低（逐個字符進(jìn)行比對），無法用日期相關(guān)的 API 進(jìn)行計(jì)算和比較。

Datetime 和 Timestamp 之間的抉擇

Datetime 和 Timestamp 是 MySQL 提供的兩種比較相似的保存時間的數(shù)據(jù)類型，可以精確到秒。他們兩者究竟該如何選擇呢？

下面我們來簡單對比一下二者。

時區(qū)信息

DateTime 類型是沒有時區(qū)信息的（時區(qū)無關(guān)） ，DateTime 類型保存的時間都是當(dāng)前會話所設(shè)置的時區(qū)對應(yīng)的時間。這樣就會有什么問題呢？當(dāng)你的時區(qū)更換之后，比如你的服務(wù)器更換地址或者更換客戶端連接時區(qū)設(shè)置的話，就會導(dǎo)致你從數(shù)據(jù)庫中讀出的時間錯誤。

Timestamp 和時區(qū)有關(guān)。Timestamp 類型字段的值會隨著服務(wù)器時區(qū)的變化而變化，自動換算成相應(yīng)的時間，說簡單點(diǎn)就是在不同時區(qū)，查詢到同一個條記錄此字段的值會不一樣。

下面實(shí)際演示一下！

建表 SQL 語句：

CREATE TABLE `time_zone_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數(shù)據(jù)：

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

查看數(shù)據(jù)：

select date_time,time_stamp from time_zone_test;

結(jié)果：

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
+---------------------+---------------------+

現(xiàn)在我們運(yùn)行

修改當(dāng)前會話的時區(qū):

set time_zone='+8:00';

再次查看數(shù)據(jù)：

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
+---------------------+---------------------+

擴(kuò)展：一些關(guān)于 MySQL 時區(qū)設(shè)置的一個常用 sql 命令

# 查看當(dāng)前會話時區(qū)
SELECT @@session.time_zone;
# 設(shè)置當(dāng)前會話時區(qū)
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 數(shù)據(jù)庫全局時區(qū)設(shè)置
SELECT @@global.time_zone;
# 設(shè)置全局時區(qū)
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

占用空間

下圖是 MySQL 日期類型所占的存儲空間

在 MySQL 5.6.4 之前，DateTime 和 Timestamp 的存儲空間是固定的，分別為 8 字節(jié)和 4 字節(jié)。

但是從 MySQL 5.6.4 開始，它們的存儲空間會根據(jù)毫秒精度的不同而變化，DateTime 的范圍是 5~8 字節(jié)，Timestamp 的范圍是 4~7 字節(jié)。

表示范圍

Timestamp 表示的時間范圍更小，只能到 2038 年：

• DateTime：1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999
• Timestamp：1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.499999

性能

由于 TIMESTAMP 需要根據(jù)時區(qū)進(jìn)行轉(zhuǎn)換，所以從毫秒數(shù)轉(zhuǎn)換到 TIMESTAMP 時，不僅要調(diào)用一個簡單的函數(shù)，還要調(diào)用操作系統(tǒng)底層的系統(tǒng)函數(shù)。

這個系統(tǒng)函數(shù)為了保證操作系統(tǒng)時區(qū)的一致性，需要進(jìn)行加鎖操作，這就降低了效率。

DATETIME 不涉及時區(qū)轉(zhuǎn)換，所以不會有這個問題。

為了避免 TIMESTAMP 的時區(qū)轉(zhuǎn)換問題，建議使用指定的時區(qū)，而不是依賴于操作系統(tǒng)時區(qū)。

數(shù)值時間戳是更好的選擇嗎？

很多時候，我們也會使用 int 或者 bigint 類型的數(shù)值也就是數(shù)值時間戳來表示時間。

這種存儲方式的具有 Timestamp 類型的所具有一些優(yōu)點(diǎn)，并且使用它的進(jìn)行日期排序以及對比等操作的效率會更高，跨系統(tǒng)也很方便，畢竟只是存放的數(shù)值。

缺點(diǎn)也很明顯，就是數(shù)據(jù)的可讀性太差了，你無法直觀的看到具體時間。

時間戳的定義如下：

• 時間戳的定義是從一個基準(zhǔn)時間開始算起，這個基準(zhǔn)時間是「1970-1-1 00:00:00 +0:00」，從這個時間開始，用整數(shù)表示，以秒計(jì)時，隨著時間的流逝這個時間整數(shù)不斷增加。
• 這樣一來，我只需要一個數(shù)值，就可以完美地表示時間了，而且這個數(shù)值是一個絕對數(shù)值，即無論的身處地球的任何角落，這個表示時間的時間戳，都是一樣的，生成的數(shù)值都是一樣的，并且沒有時區(qū)的概念，所以在系統(tǒng)的中時間的傳輸中，都不需要進(jìn)行額外的轉(zhuǎn)換了，只有在顯示給用戶的時候，才轉(zhuǎn)換為字符串格式的本地時間。

數(shù)據(jù)庫中實(shí)際操作：

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
|                            1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32       |
+---------------------------+
1 row in set (0.01 sec)

總結(jié)

MySQL 中時間到底怎么存儲才好？Datetime?Timestamp?還是數(shù)值時間戳？

并沒有一個銀彈，很多程序員會覺得數(shù)值型時間戳是真的好，效率又高還各種兼容，但是很多人又覺得它表現(xiàn)的不夠直觀。

每種方式都有各自的優(yōu)勢，根據(jù)實(shí)際場景選擇最合適的才是王道。

下面再對這三種方式做一個簡單的對比，以供大家實(shí)際開發(fā)中選擇正確的存放時間的數(shù)據(jù)類型：

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論