我們平時開發中不可避免的就是要存儲時間,比如我們要記錄操作表中這條記錄的時間、記錄轉賬的交易時間、記錄出發時間等等。你會發現這個時間這個東西與我們開發的聯繫還是非常緊密的,用的好與不好會給我們的業務甚至功能帶來很大的影響。所以,我們有必要重新出發,好好認識一下這個東西。
這是一篇短小精悍的文章,仔細閱讀一定能學到不少東西!如果文章有什麼不對的話,也勞煩指出,非常感謝!
1.切記不要用字符串存儲日期
我記得我在大學的時候就這樣幹過,而且現在很多對數據庫不太瞭解的新手也會這樣幹,可見,這種存儲日期的方式的優點還是有的,就是簡單直白,容易上手。
但是,這是不正確的做法,主要會有下面兩個問題:
- 字符串佔用的空間更大!
- 字符串存儲的日期比較效率比較低(逐個字符進行比對),無法用日期相關的 API 進行計算和比較。
2.Datetime 和 Timestamp 之間抉擇
Datetime 和 Timestamp 是 MySQL 提供的兩種比較相似的保存時間的數據類型。他們兩者究竟該如何選擇呢?
通常我們都會首選 Timestamp。 下面說一下為什麼這樣做!
2.1 DateTime 類型沒有時區信息的
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 date_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 命令
# 查看當前會話時區SELECT @@session.time_zone;# 設置當前會話時區SET time_zone = 'Europe/Helsinki';SET time_zone = "+00:00";# 數據庫全局時區設置SELECT @@global.time_zone;# 設置全局時區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.總結
MySQL 中時間到底怎麼存儲才好?Datetime?Timestamp? 數值保存的時間戳?
好像並沒有一個銀彈,很多程序員會覺得數值型時間戳是真的好,效率又高還各種兼容,但是很多人又覺得它表現的不夠直觀。這裡插一嘴,《高性能 MySQL 》這本神書的作者就是推薦 Timestamp,原因是數值表示時間不夠直觀。下面是原文:
每種方式都有各自的優勢,根據實際場景才是王道。下面再對這三種方式做一個簡單的對比,以供大家實際開發中選擇正確的存放時間的數據類型:
如果還有什麼問題歡迎給我留言!如果文章有什麼問題的話,也勞煩指出,再此感激不盡!
後面的文章我會介紹:Java8 對日期的支持以及為啥不能用 SimpleDateFormat。
閱讀更多 java互聯網架構 的文章