老生常談！數(shù)據(jù)庫(kù)如何存儲(chǔ)時(shí)間？你真的知道嗎？
本文轉(zhuǎn)載自公眾號(hào)：JavaGuide 作者:SnailClimb
我們平時(shí)開(kāi)發(fā)中不可避免的就是要存儲(chǔ)時(shí)間，比如我們要記錄操作表中這條記錄的時(shí)間、記錄轉(zhuǎn)賬的交易時(shí)間、記錄出發(fā)時(shí)間等等。你會(huì)發(fā)現(xiàn)這個(gè)時(shí)間這個(gè)東西與我們開(kāi)發(fā)的聯(lián)系還是非常緊密的，用的好與不好會(huì)給我們的業(yè)務(wù)甚至功能帶來(lái)很大的影響。所以，我們有必要重新出發(fā)，好好認(rèn)識(shí)一下這個(gè)東西。
這是一篇短小精悍的文章，仔細(xì)閱讀一定能學(xué)到不少東西!如果文章有什么不對(duì)的話，也勞煩指出，Guide 哥感激不盡!
1.切記不要用字符串存儲(chǔ)日期
我記得我在大學(xué)的時(shí)候就這樣干過(guò)，而且現(xiàn)在很多對(duì)數(shù)據(jù)庫(kù)不太了解的新手也會(huì)這樣干，可見(jiàn)，這種存儲(chǔ)日期的方式的優(yōu)點(diǎn)還是有的，就是簡(jiǎn)單直白，容易上手。
但是，這是不正確的做法，主要會(huì)有下面兩個(gè)問(wèn)題：
- 字符串占用的空間更大!
- 字符串存儲(chǔ)的日期比較效率比較低(逐個(gè)字符進(jìn)行比對(duì))，無(wú)法用日期相關(guān)的 API 進(jìn)行計(jì)算和比較。
2.Datetime 和 Timestamp 之間抉擇
Datetime 和 Timestamp 是 MySQL 提供的兩種比較相似的保存時(shí)間的數(shù)據(jù)類(lèi)型。他們兩者究竟該如何選擇呢?
通常我們都會(huì)首選 Timestamp。 下面說(shuō)一下為什么這樣做!
2.1 DateTime 類(lèi)型沒(méi)有時(shí)區(qū)信息的
DateTime 類(lèi)型是沒(méi)有時(shí)區(qū)信息的(時(shí)區(qū)無(wú)關(guān)) ，DateTime 類(lèi)型保存的時(shí)間都是當(dāng)前會(huì)話所設(shè)置的時(shí)區(qū)對(duì)應(yīng)的時(shí)間。這樣就會(huì)有什么問(wèn)題呢?當(dāng)你的時(shí)區(qū)更換之后，比如你的服務(wù)器更換地址或者更換客戶端連接時(shí)區(qū)設(shè)置的話，就會(huì)導(dǎo)致你從數(shù)據(jù)庫(kù)中讀出的時(shí)間錯(cuò)誤。不要小看這個(gè)問(wèn)題，很多系統(tǒng)就是因?yàn)檫@個(gè)問(wèn)題鬧出了很多笑話。
Timestamp 和時(shí)區(qū)有關(guān)。Timestamp 類(lèi)型字段的值會(huì)隨著服務(wù)器時(shí)區(qū)的變化而變化，自動(dòng)換算成相應(yīng)的時(shí)間，說(shuō)簡(jiǎn)單點(diǎn)就是在不同時(shí)區(qū)，查詢到同一個(gè)條記錄此字段的值會(huì)不一樣。
下面實(shí)際演示一下!
建表 SQL 語(yǔ)句：
- 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)前會(huì)話的時(shí)區(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 時(shí)區(qū)設(shè)置的一個(gè)常用 sql 命令
- # 查看當(dāng)前會(huì)話時(shí)區(qū)
- SELECT @@session.time_zone;
- # 設(shè)置當(dāng)前會(huì)話時(shí)區(qū)
- SET time_zone = 'Europe/Helsinki';
- SET time_zone = "+00:00";
- # 數(shù)據(jù)庫(kù)全局時(shí)區(qū)設(shè)置
- SELECT @@global.time_zone;
- # 設(shè)置全局時(shí)區(qū)
- SET GLOBAL time_zone = '+8:00';
- SET GLOBAL time_zone = 'Europe/Helsinki';
2.2 DateTime 類(lèi)型耗費(fèi)空間更大
Timestamp 只需要使用 4 個(gè)字節(jié)的存儲(chǔ)空間，但是 DateTime 需要耗費(fèi) 8 個(gè)字節(jié)的存儲(chǔ)空間。但是，這樣同樣造成了一個(gè)問(wèn)題，Timestamp 表示的時(shí)間范圍更小。
- 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 中有細(xì)微差別。”3 再看 MySQL 日期類(lèi)型存儲(chǔ)空間
下圖是 MySQL 5.6 版本中日期類(lèi)型所占的存儲(chǔ)空間：
可以看出 5.6.4 之后的 MySQL 多出了一個(gè)需要 0 ～ 3 字節(jié)的小數(shù)位。Datatime 和 Timestamp 會(huì)有幾種不同的存儲(chǔ)空間占用。
為了方便，本文我們還是默認(rèn) Timestamp 只需要使用 4 個(gè)字節(jié)的存儲(chǔ)空間，但是 DateTime 需要耗費(fèi) 8 個(gè)字節(jié)的存儲(chǔ)空間。
4.數(shù)值型時(shí)間戳是更好的選擇嗎?
很多時(shí)候，我們也會(huì)使用 int 或者 bigint 類(lèi)型的數(shù)值也就是時(shí)間戳來(lái)表示時(shí)間。
這種存儲(chǔ)方式的具有 Timestamp 類(lèi)型的所具有一些優(yōu)點(diǎn)，并且使用它的進(jìn)行日期排序以及對(duì)比等操作的效率會(huì)更高，跨系統(tǒng)也很方便，畢竟只是存放的數(shù)值。缺點(diǎn)也很明顯，就是數(shù)據(jù)的可讀性太差了，你無(wú)法直觀的看到具體時(shí)間。
時(shí)間戳的定義如下：
“時(shí)間戳的定義是從一個(gè)基準(zhǔn)時(shí)間開(kāi)始算起，這個(gè)基準(zhǔn)時(shí)間是「1970-1-1 00:00:00 +0:00」，從這個(gè)時(shí)間開(kāi)始，用整數(shù)表示，以秒計(jì)時(shí)，隨著時(shí)間的流逝這個(gè)時(shí)間整數(shù)不斷增加。這樣一來(lái)，我只需要一個(gè)數(shù)值，就可以完美地表示時(shí)間了，而且這個(gè)數(shù)值是一個(gè)絕對(duì)數(shù)值，即無(wú)論的身處地球的任何角落，這個(gè)表示時(shí)間的時(shí)間戳，都是一樣的，生成的數(shù)值都是一樣的，并且沒(méi)有時(shí)區(qū)的概念，所以在系統(tǒng)的中時(shí)間的傳輸中，都不需要進(jìn)行額外的轉(zhuǎn)換了，只有在顯示給用戶的時(shí)候，才轉(zhuǎn)換為字符串格式的本地時(shí)間。”數(shù)據(jù)庫(kù)中實(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)
5.總結(jié)
MySQL 中時(shí)間到底怎么存儲(chǔ)才好?Datetime?Timestamp? 數(shù)值保存的時(shí)間戳?
好像并沒(méi)有一個(gè)銀彈，很多程序員會(huì)覺(jué)得數(shù)值型時(shí)間戳是真的好，效率又高還各種兼容，但是很多人又覺(jué)得它表現(xiàn)的不夠直觀。這里插一嘴，《高性能 MySQL 》這本神書(shū)的作者就是推薦 Timestamp，原因是數(shù)值表示時(shí)間不夠直觀。下面是原文：
每種方式都有各自的優(yōu)勢(shì)，根據(jù)實(shí)際場(chǎng)景才是王道。下面再對(duì)這三種方式做一個(gè)簡(jiǎn)單的對(duì)比，以供大家實(shí)際開(kāi)發(fā)中選擇正確的存放時(shí)間的數(shù)據(jù)類(lèi)型：
如果還有什么問(wèn)題歡迎給我留言!如果文章有什么問(wèn)題的話，也勞煩指出，Guide 哥感激不盡!
