MySQL 資料庫如何實現儲存時間

2022-03-25 13:02:42

1.切記不要用字串儲存日期

字串佔用的空間更大！

字串儲存的日期比較效率比較低（逐個字元進行比對），無法用日期相關的 API 進行計算和比較。

2.Datetime 和 Timestamp 之間抉擇

Datetime 和 Timestamp 是 MySQL 提供的兩種比較相似的儲存時間的資料型別。

他們兩者究竟該如何選擇呢？

通常我們都會首選 Timestamp

2.1 DateTime 型別沒有時區資訊的

DateTime 型別是沒有時區資訊的（時區無關）

當你的時區更換之後，比如你的伺服器更換地址或者更換使用者端連線時區設定的話，就會導致你從資料庫中讀出的時間錯誤。

Timestamp 和時區有關。

Timestamp 型別欄位的值會隨著伺服器時區的變化而變化，自動換算成相應的時間，說簡單點就是在不同時區，查詢到同一個條記錄此欄位的值會不一樣。

下面實際演示一下！

建表 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;

插入資料：

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

檢視資料：

select dsqlate_time,time_stamp from time_zone_test;

結果：

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

修改當前對談的時區:

set time_zone='+8:00';

再次檢視資料：

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

擴充套件：一些關於 MySQL 時區設定的一個常用 sql 命令

1. 檢視當前對談時區

    SELECT @@session.time_zone;

2. 設定當前對談時區

    SET time_zone = 'Europe/Helsinki';
    SET time_zone = "+00:00";

3. 資料庫全域性時區設定

    SELECT @@global.time_zone;

4. 設定全域性時區

    SET GLOBAL time_zone = '+8:00';
    SET GLOBAL time_zone = 'Europe/Helsinki';

2.2 DateTime 型別耗費空間更大

Timestamp 只需要使用 4 個位元組的儲存空間，但是 DateTime 需要耗費 8 個位元組的儲存空間。但是，這樣同樣造成了一個問題，Timestamp 表示的時間範圍更小。

DateTime ：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp： 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Timestamp 在不同版本的 MySQL 中有細微差別。

3.再看 MySQL 日期型別儲存空間

下圖是 MySQL 5.6 版本中日期型別所佔的儲存空間：

可以看出 5.6.4 之後的 MySQL 多出了一個需要 0 ～ 3 位元組的小數位。Datatime 和 Timestamp 會有幾種不同的儲存空間佔用。

為了方便，本文我們還是預設 Timestamp 只需要使用 4 個位元組的儲存空間，但是 DateTime 需要耗費 8 個位元組的儲存空間。

4.數值型時間戳是更好的選擇嗎？

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

這種儲存方式的具有 Timestamp 型別的所具有一些優點，並且使用它的進行日期排序以及對比等操作的效率會更高，跨系統也很方便，畢竟只是存放的數值。缺點也很明顯，就是資料的可讀性太差了，你無法直觀的看到具體時間。

時間戳的定義如下：

時間戳的定義是從一個基準時間開始算起，這個基準時間是「1970-1-1 00:00:00 +0:00」，從這個時間開始，用整數表示，以秒計時，隨著時間的流逝這個時間整數不斷增加。

這樣一來，我只需要一個數值，就可以完美地表示時間了，而且這個數值是一個絕對數值，即無論的身處地球的任何角落，這個表示時間的時間戳，都是一樣的，生成的數值都是一樣的，並且沒有時區的概念，所以在系統的中時間的傳輸中，都不需要進行額外的轉換了，只有在顯示給使用者的時候，才轉換為字串格式的本地時間。

資料庫中實際操作：

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)

5.總結

推薦 Timestamp，原因是數值表示時間不夠直觀

每種方式都有各自的優勢，根據實際場景才是王道。下面再對這三種方式做一個簡單的對比，以供大家實際開發中選擇正確的存放時間的資料型別：

6.設定mysql date型別欄位預設值支援0000格式

[mysqld]
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。