警告！別再使用 TIMESTAMP 作為日期字段~
在日常數(shù)據(jù)庫設計中，幾乎每張業(yè)務表都帶有一個日期列，用于記錄每條記錄產生和變更的時間。比如用戶表會有一個日期列記錄用戶注冊的時間、用戶最后登錄的時間。又比如，電商行業(yè)中的訂單表（核心業(yè)務表）會有一個訂單產生的時間列，當支付時間超過訂單產生的時間，這個訂單可能會被系統(tǒng)自動取消。
日期類型雖然常見，但在表結構設計中也容易犯錯，比如很多開發(fā)同學都傾向使用整型存儲日期類型，同時也會忽略不同日期類型對于性能可能存在的潛在影響。所以你有必要認真看看這篇文章，舉一反三，在自己的業(yè)務中做好日期類型的設計。
日期類型
MySQL 數(shù)據(jù)庫中常見的日期類型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因為業(yè)務絕大部分場景都需要將日期精確到秒，所以在表結構設計中，常見使用的日期類型為DATETIME 和 TIMESTAMP。接下來，我就帶你深入了解這兩種類型，以及它們在設計中的應用實戰(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ù) NOW、SYSDATE：
mysql>?SELECT?NOW(6);
+----------------------------+
|?NOW(6)?????????????????????|
+----------------------------+
|?2020-09-14?17:50:28.707971?|
+----------------------------+
1?row?in?set?(0.00?sec)
用戶可以將?DATETIME?初始化值設置為當前時間，并設置自動更新當前時間的屬性。例如用戶表 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?表示注冊時間，DEFAULT CURRENT_TIMESTAMP?表示記錄插入時，若沒有指定時間，默認就是當前時間。
列?last_modify_date?表示當前記錄最后的修改時間，DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)?表示每次修改都會修改為當前時間。
這樣的設計保證當用戶的金錢（money 字段）發(fā)生了變更，則?last_modify_date?能記錄最后一次用戶金錢發(fā)生變更時的時間。來看下面的例子：
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)
可以看到，當用戶金額發(fā)生修改時，所對應的字段?last_modify_date?也修改成發(fā)生變更的時間。
TIMESTAMP
除了?DATETIME，日期類型中還有一種?TIMESTAMP?的時間戳類型，其實際存儲的內容為‘1970-01-01 00:00:00’到現(xiàn)在的毫秒數(shù)。在 MySQL 中，由于類型?TIMESTAMP?占用 4 個字節(jié)，因此其存儲的時間上限只能到‘2038-01-19 03:14:07’。
同類型?DATETIME?一樣，從 MySQL 5.6 版本開始，類型?TIMESTAMP?也能支持毫秒。與?DATETIME?不同的是，若帶有毫秒時，類型?TIMESTAMP?占用 7 個字節(jié)，而?DATETIME?無論是否存儲毫秒信息，都占用 8 個字節(jié)。
類型?TIMESTAMP?最大的優(yōu)點是可以帶有時區(qū)屬性，因為它本質上是從毫秒轉化而來。如果你的業(yè)務需要對應不同的國家時區(qū)，那么類型?TIMESTAMP?是一種不錯的選擇。比如新聞類的業(yè)務，通常用戶想知道這篇新聞發(fā)布時對應的自己國家時間，那么?TIMESTAMP?是一種選擇。
另外，有些國家會執(zhí)行夏令時。根據(jù)不同的季節(jié)，人為地調快或調慢 1 個小時，帶有時區(qū)屬性的?TIMESTAMP?類型本身就能解決這個問題。另外，關于mysql數(shù)據(jù)庫面試題，公眾號Java精選，回復java面試，獲取最新面試題資料，支持在線隨時隨地刷題。
參數(shù)?time_zone?指定了當前使用的時區(qū)，默認為?SYSTEM?使用操作系統(tǒng)時區(qū)，用戶可以通過該參數(shù)指定所需要的時區(qū)。
如果想使用?TIMESTAMP?的時區(qū)功能，你可以通過下面的語句將之前的用戶表 User 的注冊時間字段類型從?DATETIME(6)?修改為?TIMESTAMP(6)：
ALTER?TABLE?User?
CHANGE?register_date?
register_date?TIMESTAMP(6)?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP(6);
這時通過設定不同的?time_zone，可以觀察到不同時區(qū)下的注冊時間：
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)
從上述例子中，你可以看到，中國的時區(qū)是?+08:00，美國的時區(qū)是?-08:00，因此改為美國時區(qū)后，可以看到用戶注冊時間比之前延遲了 16 個小時。當然了，直接加減時區(qū)并不直觀，需要非常熟悉各國的時區(qū)表。
在 MySQL 中可以直接設置時區(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)
講到這兒，想必你已經了解了時間字段類型，接下來我將分享在真實業(yè)務設計中如何使用好時間類型。
業(yè)務表結構設計實戰(zhàn)
DATETIME vs TIMESTAMP vs INT，怎么選？
在做表結構設計時，對日期字段的存儲，開發(fā)人員通常會有 3 種選擇：DATETIME、TIMESTAMP、INT。
INT 類型就是直接存儲 '1970-01-01 00:00:00' 到現(xiàn)在的毫秒數(shù)，本質和?TIMESTAMP?一樣，因此用 INT 不如直接使用?TIMESTAMP。
當然，有些同學會認為 INT 比?TIMESTAMP?性能更好。但是，由于當前每個 CPU 每秒可執(zhí)行上億次的計算，所以無須為這種轉換的性能擔心。更重要的是，在后期運維和數(shù)據(jù)分析時，使用 INT 存儲日期，是會讓 DBA 和數(shù)據(jù)分析人員發(fā)瘋的，INT的可運維性太差。
也有的同學會熱衷用類型?TIMESTEMP?存儲日期，因為類型?TIMESTAMP?占用 4 個字節(jié)，比?DATETIME?小一半的存儲空間。
但若要將時間精確到毫秒，TIMESTAMP?要 7 個字節(jié)，和?DATETIME?8 字節(jié)差不太多。另一方面，現(xiàn)在距離?TIMESTAMP?的最大值‘2038-01-19 03:14:07’已經很近，這是需要開發(fā)同學好好思考的問題。
總的來說，我建議你使用類型?
DATETIME。對于時區(qū)問題，可以由前端或者服務這里做一次轉化，不一定非要在數(shù)據(jù)庫中解決。
不要忽視 TIMESTAMP 的性能問題
前面已經提及，TIMESTAMP?的上限值 2038 年很快就會到來，那時業(yè)務又將面臨一次類似千年蟲的問題。另外，TIMESTAMP?還存在潛在的性能問題。
雖然從毫秒數(shù)轉換到類型?TIMESTAMP?本身需要的 CPU 指令并不多，這并不會帶來直接的性能問題。但是如果使用默認的操作系統(tǒng)時區(qū)，則每次通過時區(qū)計算時間時，要調用操作系統(tǒng)底層系統(tǒng)函數(shù)?__tz_convert()，而這個函數(shù)需要額外的加鎖操作，以確保這時操作系統(tǒng)時區(qū)沒有修改。所以，當大規(guī)模并發(fā)訪問時，由于熱點資源競爭，會產生兩個問題。
性能不如? DATETIME：DATETIME?不存在時區(qū)轉化問題。性能抖動：海量并發(fā)時，存在性能抖動問題。
為了優(yōu)化?TIMESTAMP?的使用，強烈建議你使用顯式的時區(qū)，而不是操作系統(tǒng)時區(qū)。比如在配置文件中顯示地設置時區(qū)，而不要使用系統(tǒng)時區(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)，顯式指定時區(qū)的性能要遠遠好于直接使用操作系統(tǒng)時區(qū)。所以，日期字段推薦使用?DATETIME，沒有時區(qū)轉化。即便使用?TIMESTAMP，也需要在數(shù)據(jù)庫中顯式地配置時區(qū)，而不是用系統(tǒng)時區(qū)。
表結構設計規(guī)范：每條記錄都要有一個時間字段
在做表結構設計規(guī)范時，強烈建議你每張業(yè)務核心表都增加一個?DATETIME?類型的?last_modify_date?字段，并設置修改自動更新機制， 即便標識每條記錄最后修改的時間。
例如，在前面的表 User 中的字段?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)
);
通過字段?last_modify_date?定義的?ON UPDATE CURRENT_TIMESTAMP(6)，那么每次這條記錄，則都會自動更新?last_modify_date?為當前時間。
這樣設計的好處是：用戶可以知道每個用戶最近一次記錄更新的時間，以便做后續(xù)的處理。比如在電商的訂單表中，可以方便對支付超時的訂單做處理；在金融業(yè)務中，可以根據(jù)用戶資金最后的修改時間做相應的資金核對等。
在后面的內容中，我們也會談到 MySQL 數(shù)據(jù)庫的主從邏輯數(shù)據(jù)核對的設計實現(xiàn)，也會利用到last_modify_date?字段。
總結
日期類型通常就是使用?DATETIME?和?TIMESTAMP?兩種類型，然而由于類型?TIMESTAMP?存在性能問題，建議你還是盡可能使用類型?DATETIME。我總結一下今天的重點內容：
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è)務用?TIMESTAMP?存在風險；使用 TIMESTAMP 必須顯式地設置時區(qū)，不要使用默認系統(tǒng)時區(qū)，否則存在性能問題，推薦在配置文件中設置參數(shù)? time_zone = '+08:00'；推薦日期類型使用? DATETIME，而不是?TIMESTAMP?和 INT 類型；表結構設計時，每個核心業(yè)務表，推薦設計一個? last_modify_date?的字段，用以記錄每條記錄的最后修改時間。
來源：JAVA日知錄