我欲封天耳根小说,我欲封天耳根小说零

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案

警告！別再使用TIMESTAMP作為日期字段

在日常數(shù)據(jù)庫(kù)設(shè)計(jì)中，幾乎每張業(yè)務(wù)表都帶有一個(gè)日期列，用于記錄每條記錄產(chǎn)生和變更的時(shí)間。比如用戶表會(huì)有一個(gè)日期列記錄用戶注冊(cè)的時(shí)間、用戶最后登錄的時(shí)間。又比如，電商行業(yè)中的訂單表(核心業(yè)務(wù)表)會(huì)有一個(gè)訂單產(chǎn)生的時(shí)間列，當(dāng)支付時(shí)間超過訂單產(chǎn)生的時(shí)間，這個(gè)訂單可能會(huì)被系統(tǒng)自動(dòng)取消。

創(chuàng)新互聯(lián)于2013年創(chuàng)立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元天等做網(wǎng)站,已為上家服務(wù),為天等各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220

日期類型雖然常見，但在表結(jié)構(gòu)設(shè)計(jì)中也容易犯錯(cuò)，比如很多開發(fā)同學(xué)都傾向使用整型存儲(chǔ)日期類型，同時(shí)也會(huì)忽略不同日期類型對(duì)于性能可能存在的潛在影響。所以你有必要認(rèn)真看看這篇文章，舉一反三，在自己的業(yè)務(wù)中做好日期類型的設(shè)計(jì)。

日期類型

MySQL 數(shù)據(jù)庫(kù)中常見的日期類型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因?yàn)闃I(yè)務(wù)絕大部分場(chǎng)景都需要將日期精確到秒，所以在表結(jié)構(gòu)設(shè)計(jì)中，常見使用的日期類型為DATETIME 和 TIMESTAMP。接下來(lái)，我就帶你深入了解這兩種類型，以及它們?cè)谠O(shè)計(jì)中的應(yīng)用實(shí)戰(zhàn)。

DATETIME

類型 DATETIME 最終展現(xiàn)的形式為：YYYY-MM-DD HH：MM：SS，固定占用 8 個(gè)字節(jié)。

從 MySQL 5.6 版本開始，DATETIME 類型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存儲(chǔ) 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)更新當(dāng)前時(shí)間的屬性。例如用戶表 User有register_date、last_modify_date兩個(gè)字段的定義：

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 表示注冊(cè)時(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) 表示每次修改都會(huì)修改為當(dāng)前時(shí)間。

這樣的設(shè)計(jì)保證當(dāng)用戶的金錢(money 字段)發(fā)生了變更，則 last_modify_date 能記錄最后一次用戶金錢發(fā)生變更時(shí)的時(shí)間。來(lái)看下面的例子：

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í)，所對(duì)應(yīng)的字段 last_modify_date 也修改成發(fā)生變更的時(shí)間。

TIMESTAMP

除了 DATETIME，日期類型中還有一種 TIMESTAMP 的時(shí)間戳類型，其實(shí)際存儲(chǔ)的內(nèi)容為‘1970-01-01 00:00:00’到現(xiàn)在的毫秒數(shù)。在 MySQL 中，由于類型 TIMESTAMP 占用 4 個(gè)字節(jié)，因此其存儲(chǔ)的時(shí)間上限只能到‘2038-01-19 03:14:07’。

同類型 DATETIME 一樣，從 MySQL 5.6 版本開始，類型 TIMESTAMP 也能支持毫秒。與 DATETIME 不同的是，若帶有毫秒時(shí)，類型 TIMESTAMP 占用 7 個(gè)字節(jié)，而 DATETIME 無(wú)論是否存儲(chǔ)毫秒信息，都占用 8 個(gè)字節(jié)。

類型 TIMESTAMP 最大的優(yōu)點(diǎn)是可以帶有時(shí)區(qū)屬性，因?yàn)樗举|(zhì)上是從毫秒轉(zhuǎn)化而來(lái)。如果你的業(yè)務(wù)需要對(duì)應(yīng)不同的國(guó)家時(shí)區(qū)，那么類型 TIMESTAMP 是一種不錯(cuò)的選擇。比如新聞?lì)惖臉I(yè)務(wù)，通常用戶想知道這篇新聞發(fā)布時(shí)對(duì)應(yīng)的自己國(guó)家時(shí)間，那么 TIMESTAMP 是一種選擇。

另外，有些國(guó)家會(huì)執(zhí)行夏令時(shí)。根據(jù)不同的季節(jié)，人為地調(diào)快或調(diào)慢 1 個(gè)小時(shí)，帶有時(shí)區(qū)屬性的 TIMESTAMP 類型本身就能解決這個(gè)問題。

參數(shù) time_zone 指定了當(dāng)前使用的時(shí)區(qū)，默認(rèn)為 SYSTEM 使用操作系統(tǒng)時(shí)區(qū)，用戶可以通過該參數(shù)指定所需要的時(shí)區(qū)。

如果想使用 TIMESTAMP 的時(shí)區(qū)功能，你可以通過下面的語(yǔ)句將之前的用戶表 User 的注冊(cè)時(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ū)下的注冊(cè)時(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)

從上述例子中，你可以看到，中國(guó)的時(shí)區(qū)是 +08:00，美國(guó)的時(shí)區(qū)是 -08:00，因此改為美國(guó)時(shí)區(qū)后，可以看到用戶注冊(cè)時(shí)間比之前延遲了 16 個(gè)小時(shí)。當(dāng)然了，直接加減時(shí)區(qū)并不直觀，需要非常熟悉各國(guó)的時(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í)間字段類型，接下來(lái)我將分享在真實(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í)，對(duì)日期字段的存儲(chǔ)，開發(fā)人員通常會(huì)有 3 種選擇：DATETIME、TIMESTAMP、INT。

INT 類型就是直接存儲(chǔ) '1970-01-01 00:00:00' 到現(xiàn)在的毫秒數(shù)，本質(zhì)和 TIMESTAMP 一樣，因此用 INT 不如直接使用 TIMESTAMP。

當(dāng)然，有些同學(xué)會(huì)認(rèn)為 INT 比 TIMESTAMP 性能更好。但是，由于當(dāng)前每個(gè) CPU 每秒可執(zhí)行上億次的計(jì)算，所以無(wú)須為這種轉(zhuǎn)換的性能擔(dān)心。更重要的是，在后期運(yùn)維和數(shù)據(jù)分析時(shí)，使用 INT 存儲(chǔ)日期，是會(huì)讓 DBA 和數(shù)據(jù)分析人員發(fā)瘋的，INT的可運(yùn)維性太差。

也有的同學(xué)會(huì)熱衷用類型 TIMESTEMP 存儲(chǔ)日期，因?yàn)轭愋?TIMESTAMP 占用 4 個(gè)字節(jié)，比 DATETIME 小一半的存儲(chǔ)空間。

但若要將時(shí)間精確到毫秒，TIMESTAMP 要 7 個(gè)字節(jié)，和 DATETIME 8 字節(jié)差不太多。另一方面，現(xiàn)在距離 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已經(jīng)很近，這是需要開發(fā)同學(xué)好好思考的問題。

總的來(lái)說(shuō)，我建議你使用類型 DATETIME。對(duì)于時(shí)區(qū)問題，可以由前端或者服務(wù)這里做一次轉(zhuǎn)化，不一定非要在數(shù)據(jù)庫(kù)中解決。

不要忽視 TIMESTAMP 的性能問題

前面已經(jīng)提及，TIMESTAMP 的上限值 2038 年很快就會(huì)到來(lái)，那時(shí)業(yè)務(wù)又將面臨一次類似千年蟲的問題。另外，TIMESTAMP 還存在潛在的性能問題。

雖然從毫秒數(shù)轉(zhuǎn)換到類型 TIMESTAMP 本身需要的 CPU 指令并不多，這并不會(huì)帶來(lái)直接的性能問題。但是如果使用默認(rèn)的操作系統(tǒng)時(shí)區(qū)，則每次通過時(shí)區(qū)計(jì)算時(shí)間時(shí)，要調(diào)用操作系統(tǒng)底層系統(tǒng)函數(shù) __tz_convert()，而這個(gè)函數(shù)需要額外的加鎖操作，以確保這時(shí)操作系統(tǒng)時(shí)區(qū)沒有修改。所以，當(dāng)大規(guī)模并發(fā)訪問時(shí)，由于熱點(diǎn)資源競(jìng)爭(zhēng)，會(huì)產(chǎn)生兩個(gè)問題。

性能不如 DATETIME：DATETIME 不存在時(shí)區(qū)轉(zhuǎn)化問題。
性能抖動(dòng)：海量并發(fā)時(shí)，存在性能抖動(dòng)問題。

為了優(yōu)化 TIMESTAMP 的使用，強(qiáng)烈建議你使用顯式的時(shí)區(qū)，而不是操作系統(tǒng)時(shí)區(qū)。比如在配置文件中顯示地設(shè)置時(shí)區(qū)，而不要使用系統(tǒng)時(shí)區(qū)：

[mysqld]
time_zone = "+08:00"

最后，通過命令 mysqlslap 來(lái)測(cè)試 TIMESTAMP、DATETIME 的性能，命令如下：

# 比較time_zone為System和Asia/Shanghai的性能對(duì)比

mysqlslap -uroot --number-of-queries=1000000 --concurrency=100 --query='SELECT NOW()'

最后的性能對(duì)比如下：

從表中可以發(fā)現(xiàn)，顯式指定時(shí)區(qū)的性能要遠(yuǎn)遠(yuǎn)好于直接使用操作系統(tǒng)時(shí)區(qū)。所以，日期字段推薦使用 DATETIME，沒有時(shí)區(qū)轉(zhuǎn)化。即便使用 TIMESTAMP，也需要在數(shù)據(jù)庫(kù)中顯式地配置時(shí)區(qū)，而不是用系統(tǒng)時(shí)區(qū)。

表結(jié)構(gòu)設(shè)計(jì)規(guī)范：每條記錄都要有一個(gè)時(shí)間字段

在做表結(jié)構(gòu)設(shè)計(jì)規(guī)范時(shí)，強(qiáng)烈建議你每張業(yè)務(wù)核心表都增加一個(gè) DATETIME 類型的 last_modify_date 字段，并設(shè)置修改自動(dòng)更新機(jī)制，即便標(biāo)識(shí)每條記錄最后修改的時(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)，那么每次這條記錄，則都會(huì)自動(dòng)更新 last_modify_date 為當(dāng)前時(shí)間。

這樣設(shè)計(jì)的好處是：用戶可以知道每個(gè)用戶最近一次記錄更新的時(shí)間，以便做后續(xù)的處理。比如在電商的訂單表中，可以方便對(duì)支付超時(shí)的訂單做處理;在金融業(yè)務(wù)中，可以根據(jù)用戶資金最后的修改時(shí)間做相應(yīng)的資金核對(duì)等。

在后面的內(nèi)容中，我們也會(huì)談到 MySQL 數(shù)據(jù)庫(kù)的主從邏輯數(shù)據(jù)核對(duì)的設(shè)計(jì)實(shí)現(xiàn)，也會(huì)利用到last_modify_date 字段。

總結(jié)

日期類型通常就是使用 DATETIME 和 TIMESTAMP 兩種類型，然而由于類型 TIMESTAMP 存在性能問題，建議你還是盡可能使用類型 DATETIME。我總結(jié)一下今天的重點(diǎn)內(nèi)容：

MySQL 5.6 版本開始 DATETIME 和 TIMESTAMP 精度支持到毫秒；
DATETIME 占用 8 個(gè)字節(jié)，TIMESTAMP 占用 4 個(gè)字節(jié)，DATETIME(6) 依然占用 8 個(gè)字節(jié)，TIMESTAMP(6) 占用 7 個(gè)字節(jié)；
TIMESTAMP 日期存儲(chǔ)的上限為 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í)，每個(gè)核心業(yè)務(wù)表，推薦設(shè)計(jì)一個(gè) last_modify_date 的字段，用以記錄每條記錄的最后修改時(shí)間。

分享標(biāo)題：警告！別再使用TIMESTAMP作為日期字段
當(dāng)前URL：http://fisionsoft.com.cn/article/dhsiogp.html