警告！別再使用 TIMESTAMP 作為日期字段～
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)??
來源：JAVA日知錄
在日常數(shù)據(jù)庫設(shè)計(jì)中，幾乎每張業(yè)務(wù)表都帶有一個日期列，用于記錄每條記錄產(chǎn)生和變更的時(shí)間。比如用戶表會有一個日期列記錄用戶注冊的時(shí)間、用戶最后登錄的時(shí)間。又比如，電商行業(yè)中的訂單表（核心業(yè)務(wù)表）會有一個訂單產(chǎn)生的時(shí)間列，當(dāng)支付時(shí)間超過訂單產(chǎn)生的時(shí)間，這個訂單可能會被系統(tǒng)自動取消。
日期類型雖然常見，但在表結(jié)構(gòu)設(shè)計(jì)中也容易犯錯，比如很多開發(fā)同學(xué)都傾向使用整型存儲日期類型，同時(shí)也會忽略不同日期類型對于性能可能存在的潛在影響。所以你有必要認(rèn)真看看這篇文章，舉一反三，在自己的業(yè)務(wù)中做好日期類型的設(shè)計(jì)。
日期類型
MySQL 數(shù)據(jù)庫中常見的日期類型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因?yàn)闃I(yè)務(wù)絕大部分場景都需要將日期精確到秒，所以在表結(jié)構(gòu)設(shè)計(jì)中，常見使用的日期類型為DATETIME 和 TIMESTAMP。接下來，我就帶你深入了解這兩種類型，以及它們在設(shè)計(jì)中的應(yīng)用實(shí)戰(zhàn)。
DATETIME
類型 DATETIME 最終展現(xiàn)的形式為：YYYY-MM-DD HH：MM：SS，固定占用 8 個字節(jié)。
從 MySQL 5.6 版本開始，DATETIME 類型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。
例如，DATETIME(6) 表示可以存儲 6 位的毫秒值。同時(shí)，一些日期函數(shù)也支持精確到毫秒，例如常見的函數(shù) NOW、SYSDATE：
mysql>?SELECT?NOW(6);
+----------------------------+
|?NOW(6)?????????????????????|
+----------------------------+
|?2020-09-14?17:50:28.707971?|
+----------------------------+
1?row?in?set?(0.00?sec)
用戶可以將 DATETIME 初始化值設(shè)置為當(dāng)前時(shí)間，并設(shè)置自動更新當(dāng)前時(shí)間的屬性。例如用戶表 User有register_date、last_modify_date兩個字段的定義：
CREATE?TABLE?User?(
????id?BIGINT?NOT?NULL?AUTO_INCREMENT,
????name?VARCHAR(255)?NOT?NULL,
????sex?CHAR(1)?NOT?NULL,
????password?VARCHAR(1024)?NOT?NULL,
????money?INT?NOT?NULL?DEFAULT?0,
????register_date?DATETIME(6)?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP(6),
????last_modify_date?DATETIME(6)?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP(6)?ON?UPDATE?CURRENT_TIMESTAMP(6),
????CHECK?(sex?=?'M'?OR?sex?=?'F'),
????PRIMARY?KEY(id)
);
在上面的表 User 中，列 register_date 表示注冊時(shí)間，DEFAULT CURRENT_TIMESTAMP 表示記錄插入時(shí)，若沒有指定時(shí)間，默認(rèn)就是當(dāng)前時(shí)間。
列 last_modify_date 表示當(dāng)前記錄最后的修改時(shí)間，DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 表示每次修改都會修改為當(dāng)前時(shí)間。
這樣的設(shè)計(jì)保證當(dāng)用戶的金錢（money 字段）發(fā)生了變更，則 last_modify_date 能記錄最后一次用戶金錢發(fā)生變更時(shí)的時(shí)間。來看下面的例子：
mysql>?SELECT?name,money,last_modify_date?FROM?User?WHERE?name?=?'David';
+-------+-------+----------------------------+
|?name??|?money?|?last_modify_date???????????|
+-------+-------+----------------------------+
|?David?|???100?|?2020-09-13?08:08:33.898593?|
+-------+-------+----------------------------+
1?row?in?set?(0.00?sec)
mysql>?UPDATE?User?SET?money?=?money?-?1?WHERE?name?=?'David';
Query?OK,?1?row?affected?(0.06?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
mysql>?SELECT?name,money,last_modify_date?FROM?User?WHERE?name?=?'David';
+-------+-------+----------------------------+
|?name??|?money?|?last_modify_date???????????|
+-------+-------+----------------------------+
|?David?|????99?|?2020-09-14?18:29:17.056327?|
+-------+-------+----------------------------+
1?row?in?set?(0.00?sec)
可以看到，當(dāng)用戶金額發(fā)生修改時(shí)，所對應(yīng)的字段 last_modify_date 也修改成發(fā)生變更的時(shí)間。
TIMESTAMP
除了 DATETIME，日期類型中還有一種 TIMESTAMP 的時(shí)間戳類型，其實(shí)際存儲的內(nèi)容為‘1970-01-01 00:00:00’到現(xiàn)在的毫秒數(shù)。在 MySQL 中，由于類型 TIMESTAMP 占用 4 個字節(jié)，因此其存儲的時(shí)間上限只能到‘2038-01-19 03:14:07’。
同類型 DATETIME 一樣，從 MySQL 5.6 版本開始，類型 TIMESTAMP 也能支持毫秒。與 DATETIME 不同的是，若帶有毫秒時(shí)，類型 TIMESTAMP 占用 7 個字節(jié)，而 DATETIME 無論是否存儲毫秒信息，都占用 8 個字節(jié)。
類型 TIMESTAMP 最大的優(yōu)點(diǎn)是可以帶有時(shí)區(qū)屬性，因?yàn)樗举|(zhì)上是從毫秒轉(zhuǎn)化而來。如果你的業(yè)務(wù)需要對應(yīng)不同的國家時(shí)區(qū)，那么類型 TIMESTAMP 是一種不錯的選擇。比如新聞類的業(yè)務(wù)，通常用戶想知道這篇新聞發(fā)布時(shí)對應(yīng)的自己國家時(shí)間，那么 TIMESTAMP 是一種選擇。
另外，有些國家會執(zhí)行夏令時(shí)。根據(jù)不同的季節(jié)，人為地調(diào)快或調(diào)慢 1 個小時(shí)，帶有時(shí)區(qū)屬性的 TIMESTAMP 類型本身就能解決這個問題。
參數(shù) time_zone 指定了當(dāng)前使用的時(shí)區(qū)，默認(rèn)為 SYSTEM 使用操作系統(tǒng)時(shí)區(qū)，用戶可以通過該參數(shù)指定所需要的時(shí)區(qū)。
如果想使用 TIMESTAMP 的時(shí)區(qū)功能，你可以通過下面的語句將之前的用戶表 User 的注冊時(shí)間字段類型從 DATETIME(6) 修改為 TIMESTAMP(6)：
ALTER?TABLE?User?
CHANGE?register_date?
register_date?TIMESTAMP(6)?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP(6);
這時(shí)通過設(shè)定不同的 time_zone，可以觀察到不同時(shí)區(qū)下的注冊時(shí)間：
mysql>?SELECT?name,regist?er_date?FROM?User?WHERE?name?=?'David';
+-------+----------------------------+
|?name??|?register_date??????????????|
+-------+----------------------------+
|?David?|?2018-09-14?18:28:33.898593?|
+-------+----------------------------+
1?row?in?set?(0.00?sec)
mysql>?SET?time_zone?=?'-08:00';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?SELECT?name,register_date?FROM?User?WHERE?name?=?'David';
+-------+----------------------------+
|?name??|?register_date??????????????|
+-------+----------------------------+
|?David?|?2018-09-14?02:28:33.898593?|
+-------+----------------------------+
1?row?in?set?(0.00?sec)
從上述例子中，你可以看到，中國的時(shí)區(qū)是 +08:00，美國的時(shí)區(qū)是 -08:00，因此改為美國時(shí)區(qū)后，可以看到用戶注冊時(shí)間比之前延遲了 16 個小時(shí)。當(dāng)然了，直接加減時(shí)區(qū)并不直觀，需要非常熟悉各國的時(shí)區(qū)表。
在 MySQL 中可以直接設(shè)置時(shí)區(qū)的名字，如：
mysql>?SET?time_zone?=?'America/Los_Angeles';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?SELECT?NOW();
+---------------------+
|?NOW()???????????????|
+---------------------+
|?2020-09-14?20:12:49?|
+---------------------+
1?row?in?set?(0.00?sec)
mysql>?SET?time_zone?=?'Asia/Shanghai';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?SELECT?NOW();
+---------------------+
|?NOW()???????????????|
+---------------------+
|?2020-09-15?11:12:55?|
+---------------------+
1?row?in?set?(0.00?sec)
講到這兒，想必你已經(jīng)了解了時(shí)間字段類型，接下來我將分享在真實(shí)業(yè)務(wù)設(shè)計(jì)中如何使用好時(shí)間類型。
業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
DATETIME vs TIMESTAMP vs INT，怎么選？
在做表結(jié)構(gòu)設(shè)計(jì)時(shí)，對日期字段的存儲，開發(fā)人員通常會有 3 種選擇：DATETIME、TIMESTAMP、INT。
INT 類型就是直接存儲 '1970-01-01 00:00:00' 到現(xiàn)在的毫秒數(shù)，本質(zhì)和 TIMESTAMP 一樣，因此用 INT 不如直接使用 TIMESTAMP。
當(dāng)然，有些同學(xué)會認(rèn)為 INT 比 TIMESTAMP 性能更好。但是，由于當(dāng)前每個 CPU 每秒可執(zhí)行上億次的計(jì)算，所以無須為這種轉(zhuǎn)換的性能擔(dān)心。更重要的是，在后期運(yùn)維和數(shù)據(jù)分析時(shí)，使用 INT 存儲日期，是會讓 DBA 和數(shù)據(jù)分析人員發(fā)瘋的，INT的可運(yùn)維性太差。
也有的同學(xué)會熱衷用類型 TIMESTEMP 存儲日期，因?yàn)轭愋?TIMESTAMP 占用 4 個字節(jié)，比 DATETIME 小一半的存儲空間。
但若要將時(shí)間精確到毫秒，TIMESTAMP 要 7 個字節(jié)，和 DATETIME 8 字節(jié)差不太多。另一方面，現(xiàn)在距離 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已經(jīng)很近，這是需要開發(fā)同學(xué)好好思考的問題。
總的來說，我建議你使用類型
DATETIME。對于時(shí)區(qū)問題，可以由前端或者服務(wù)這里做一次轉(zhuǎn)化，不一定非要在數(shù)據(jù)庫中解決。
不要忽視 TIMESTAMP 的性能問題
前面已經(jīng)提及，TIMESTAMP 的上限值 2038 年很快就會到來，那時(shí)業(yè)務(wù)又將面臨一次類似千年蟲的問題。另外，TIMESTAMP 還存在潛在的性能問題。
雖然從毫秒數(shù)轉(zhuǎn)換到類型 TIMESTAMP 本身需要的 CPU 指令并不多，這并不會帶來直接的性能問題。但是如果使用默認(rèn)的操作系統(tǒng)時(shí)區(qū)，則每次通過時(shí)區(qū)計(jì)算時(shí)間時(shí)，要調(diào)用操作系統(tǒng)底層系統(tǒng)函數(shù) __tz_convert()，而這個函數(shù)需要額外的加鎖操作，以確保這時(shí)操作系統(tǒng)時(shí)區(qū)沒有修改。所以，當(dāng)大規(guī)模并發(fā)訪問時(shí)，由于熱點(diǎn)資源競爭，會產(chǎn)生兩個問題。
性能不如 DATETIME：DATETIME不存在時(shí)區(qū)轉(zhuǎn)化問題。性能抖動：海量并發(fā)時(shí)，存在性能抖動問題。
為了優(yōu)化 TIMESTAMP 的使用，強(qiáng)烈建議你使用顯式的時(shí)區(qū)，而不是操作系統(tǒng)時(shí)區(qū)。比如在配置文件中顯示地設(shè)置時(shí)區(qū)，而不要使用系統(tǒng)時(shí)區(qū)：
[mysqld]
time_zone?=?"+08:00"
最后，通過命令 mysqlslap 來測試 TIMESTAMP、DATETIME 的性能，命令如下：
#?比較time_zone為System和Asia/Shanghai的性能對比
mysqlslap?-uroot?--number-of-queries=1000000?--concurrency=100?--query='SELECT?NOW()'
最后的性能對比如下：
從表中可以發(fā)現(xiàn)，顯式指定時(shí)區(qū)的性能要遠(yuǎn)遠(yuǎn)好于直接使用操作系統(tǒng)時(shí)區(qū)。所以，日期字段推薦使用 DATETIME，沒有時(shí)區(qū)轉(zhuǎn)化。即便使用 TIMESTAMP，也需要在數(shù)據(jù)庫中顯式地配置時(shí)區(qū)，而不是用系統(tǒng)時(shí)區(qū)。
表結(jié)構(gòu)設(shè)計(jì)規(guī)范：每條記錄都要有一個時(shí)間字段
在做表結(jié)構(gòu)設(shè)計(jì)規(guī)范時(shí)，強(qiáng)烈建議你每張業(yè)務(wù)核心表都增加一個 DATETIME 類型的 last_modify_date 字段，并設(shè)置修改自動更新機(jī)制， 即便標(biāo)識每條記錄最后修改的時(shí)間。
例如，在前面的表 User 中的字段 last_modify_date，就是用于表示最后一次的修改時(shí)間：
CREATE?TABLE?User?(
????id?BIGINT?NOT?NULL?AUTO_INCREMENT,
????name?VARCHAR(255)?NOT?NULL,
????sex?CHAR(1)?NOT?NULL,
????password?VARCHAR(1024)?NOT?NULL,
????money?INT?NOT?NULL?DEFAULT?0,
????register_date?DATETIME(6)?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP(6),
????last_modify_date?DATETIME(6)?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP(6)?ON?UPDATE?CURRENT_TIMESTAMP(6),
????CHECK?(sex?=?'M'?OR?sex?=?'F'),
????PRIMARY?KEY(id)
);
通過字段 last_modify_date 定義的 ON UPDATE CURRENT_TIMESTAMP(6)，那么每次這條記錄，則都會自動更新 last_modify_date 為當(dāng)前時(shí)間。
這樣設(shè)計(jì)的好處是：用戶可以知道每個用戶最近一次記錄更新的時(shí)間，以便做后續(xù)的處理。比如在電商的訂單表中，可以方便對支付超時(shí)的訂單做處理；在金融業(yè)務(wù)中，可以根據(jù)用戶資金最后的修改時(shí)間做相應(yīng)的資金核對等。
在后面的內(nèi)容中，我們也會談到 MySQL 數(shù)據(jù)庫的主從邏輯數(shù)據(jù)核對的設(shè)計(jì)實(shí)現(xiàn)，也會利用到last_modify_date 字段。
總結(jié)
日期類型通常就是使用 DATETIME 和 TIMESTAMP 兩種類型，然而由于類型 TIMESTAMP 存在性能問題，建議你還是盡可能使用類型 DATETIME。我總結(jié)一下今天的重點(diǎn)內(nèi)容：
MySQL 5.6 版本開始 DATETIME和TIMESTAMP精度支持到毫秒；DATETIME占用 8 個字節(jié)，TIMESTAMP占用 4 個字節(jié)，DATETIME(6)依然占用 8 個字節(jié)，TIMESTAMP(6)占用 7 個字節(jié)；TIMESTAMP日期存儲的上限為2038-01-19 03:14:07，業(yè)務(wù)用TIMESTAMP存在風(fēng)險(xiǎn)；使用 TIMESTAMP 必須顯式地設(shè)置時(shí)區(qū)，不要使用默認(rèn)系統(tǒng)時(shí)區(qū)，否則存在性能問題，推薦在配置文件中設(shè)置參數(shù) time_zone = '+08:00'；推薦日期類型使用 DATETIME，而不是TIMESTAMP和 INT 類型；表結(jié)構(gòu)設(shè)計(jì)時(shí)，每個核心業(yè)務(wù)表，推薦設(shè)計(jì)一個 last_modify_date的字段，用以記錄每條記錄的最后修改時(shí)間。
比 Xshell 還好用的 SSH 客戶端神器，MobaXterm 太愛了！
最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。
獲取方式：點(diǎn)“在看”，關(guān)注公眾號并回復(fù)?Java?領(lǐng)取，更多內(nèi)容陸續(xù)奉上。
文章有幫助的話，在看，轉(zhuǎn)發(fā)吧。
謝謝支持喲 (*
