目錄
- 前言
- 不要使用字符串存儲(chǔ)時(shí)間類型
- MySQL 中的日期類型
- DATETIME
- TIMESTAMEP
- TIMESTAMP 的性能問(wèn)題
- 數(shù)值型時(shí)間戳(INT)
- DATETIME vs TIMESTAMP vs INT,怎么選?
- 總結(jié)
前言
平時(shí)開(kāi)發(fā)中經(jīng)常需要記錄時(shí)間,比如用于記錄某條記錄的創(chuàng)建時(shí)間以及修改時(shí)間。在數(shù)據(jù)庫(kù)中存儲(chǔ)時(shí)間的方式有很多種,比如 MySQL 本身就提供了日期類型,比如 DATETIME,TIMESTAMEP 等,我們也可以直接存儲(chǔ)時(shí)間戳為 INT 類型,也有人直接將時(shí)間存儲(chǔ)為字符串類型。
那么到底哪種存儲(chǔ)時(shí)間的方式更好呢?
不要使用字符串存儲(chǔ)時(shí)間類型
這是初學(xué)者很容易犯的錯(cuò)誤,容易直接將字段設(shè)置為 VARCHAR 類型,存儲(chǔ)"2021-01-01 00:00:00"這樣的字符串。當(dāng)然這樣做的優(yōu)點(diǎn)是比較簡(jiǎn)單,上手快。
但是極力不推薦這樣做,因?yàn)檫@樣做有兩個(gè)比較大的問(wèn)題:
- 字符串占用的空間大
- 這樣存儲(chǔ)的字段比較效率太低,只能逐個(gè)字符比較,無(wú)法使用 MySQL 提供的日期API
MySQL 中的日期類型
MySQL 數(shù)據(jù)庫(kù)中常見(jiàn)的日期類型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因?yàn)橐话愣夹枰獙⑷掌诰_到秒,其中比較合適的有DATETIME,TIMESTAMEP。
DATETIME
DATETIME 在數(shù)據(jù)庫(kù)中存儲(chǔ)的形式為:YYYY-MM-DD HH:MM:SS,固定占用 8 個(gè)字節(jié)。
從 MySQL 5.6 版本開(kāi)始,DATETIME 類型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。例如,DATETIME(6) 表示可以存儲(chǔ) 6 位的毫秒值。
TIMESTAMEP
TIMESTAMP 實(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'。
從 MySQL 5.6 版本開(kāi)始,類型 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 是一種選擇。Timestamp 類型字段的值會(huì)隨著服務(wù)器時(shí)區(qū)的變化而變化,自動(dòng)換算成相應(yīng)的時(shí)間,說(shuō)簡(jiǎn)單點(diǎn)就是在不同時(shí)區(qū),查詢到同一個(gè)條記錄此字段的值會(huì)不一樣。
TIMESTAMP 的性能問(wèn)題
TIMESTAMP 還存在潛在的性能問(wèn)題。
雖然從毫秒數(shù)轉(zhuǎn)換到類型 TIMESTAMP 本身需要的 CPU 指令并不多,這并不會(huì)帶來(lái)直接的性能問(wèn)題。但是如果使用默認(rèn)的操作系統(tǒng)時(shí)區(qū),則每次通過(guò)時(shí)區(qū)計(jì)算時(shí)間時(shí),要調(diào)用操作系統(tǒng)底層系統(tǒng)函數(shù) __tz_convert(),而這個(gè)函數(shù)需要額外的加鎖操作,以確保這時(shí)操作系統(tǒng)時(shí)區(qū)沒(méi)有修改。所以,當(dāng)大規(guī)模并發(fā)訪問(wèn)時(shí),由于熱點(diǎn)資源競(jìng)爭(zhēng),會(huì)產(chǎn)生兩個(gè)問(wèn)題:
- 性能不如 DATETIME:DATETIME 不存在時(shí)區(qū)轉(zhuǎn)化問(wèn)題。
- 性能抖動(dòng):海量并發(fā)時(shí),存在性能抖動(dòng)問(wèn)題。
為了優(yōu)化 TIMESTAMP 的使用,建議使用顯式的時(shí)區(qū),而不是操作系統(tǒng)時(shí)區(qū)。比如在配置文件中顯示地設(shè)置時(shí)區(qū),而不要使用系統(tǒng)時(shí)區(qū):
[mysqld]
time_zone = "+08:00"
簡(jiǎn)單總結(jié)一下這兩種數(shù)據(jù)類型的優(yōu)缺點(diǎn):
- DATETIME 沒(méi)有存儲(chǔ)的時(shí)間上限,而TIMESTAMP存儲(chǔ)的時(shí)間上限只能到‘2038-01-19 03:14:07'
- DATETIME 不帶時(shí)區(qū)屬性,需要前端或者服務(wù)端處理,但是僅從數(shù)據(jù)庫(kù)保存數(shù)據(jù)和讀取數(shù)據(jù)而言,性能更好
- TIMESTAMP 帶有時(shí)區(qū)屬性,但是每次需要通過(guò)時(shí)區(qū)計(jì)算時(shí)間,并發(fā)訪問(wèn)時(shí)會(huì)有性能問(wèn)題
- 存儲(chǔ) DATETIME 比 TIMESTAMEP 多占用一部分空間
數(shù)值型時(shí)間戳(INT)
很多時(shí)候,我們也會(huì)使用 int 或者 bigint 類型的數(shù)值也就是時(shí)間戳來(lái)表示時(shí)間。
這種存儲(chǔ)方式的具有 Timestamp 類型的所具有一些優(yōu)點(diǎn),并且使用它的進(jìn)行日期排序以及對(duì)比等操作的效率會(huì)更高,跨系統(tǒng)也很方便,畢竟只是存放的數(shù)值。缺點(diǎn)也很明顯,就是數(shù)據(jù)的可讀性太差了,你無(wú)法直觀的看到具體時(shí)間。
如果需要查看某個(gè)時(shí)間段內(nèi)的數(shù)據(jù)
select * from t where created_at > UNIX_TIMESTAMP('2021-01-01 00:00:00');
DATETIME vs TIMESTAMP vs INT,怎么選?
每種方式都有各自的優(yōu)勢(shì),下面再對(duì)這三種方式做一個(gè)簡(jiǎn)單的對(duì)比:
日期類型 |
占用空間 |
日期格式 |
日期范圍 |
是否存在時(shí)區(qū)問(wèn)題 |
DATETIME |
8 字節(jié) |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 ~9999-12-31 23:59:59 |
是 |
TIMESTAMP |
4 字節(jié) |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:00 ~2038-01-19 03:14:07 |
否 |
INT |
4 字節(jié) |
全數(shù)字時(shí)間戳 |
1000-01-01 00:00:01 之后的時(shí)間 |
否 |
TIMESTAMP 與 INT 本質(zhì)一樣,但是相比而言雖然 INT 對(duì)開(kāi)發(fā)友好,但是對(duì) DBA 以及數(shù)據(jù)分析人員不友好,可讀性差。所以《高性能 MySQL 》的作者推薦 TIMESTAMP 的原因就是它的數(shù)值表示時(shí)間更加直觀。下面是原文:
至于時(shí)區(qū)問(wèn)題,可以由前端或者服務(wù)這里做一次轉(zhuǎn)化,不一定非要在數(shù)據(jù)庫(kù)中解決。
總結(jié)
本文比較了幾種最常使用的存儲(chǔ)時(shí)間的方式,我最推薦的還是 DATETIME。理由如下:
- TIMESTAMP 比數(shù)值型時(shí)間戳可讀性更好
- DATETIME 的存儲(chǔ)上限為 9999-12-31 23:59:59,如果使用 TIMESTAMP,則 2038 年需要考慮解決方案
- DATETIME 由于不需要時(shí)區(qū)轉(zhuǎn)換,所以性能比 TIMESTAMP 好
- 如果需要將時(shí)間存儲(chǔ)到毫秒,TIMESTAMP 要 7 個(gè)字節(jié),和 DATETIME 8 字節(jié)差不太多
到此這篇關(guān)于MySQL中存儲(chǔ)時(shí)間的文章就介紹到這了,更多相關(guān)MySQL存儲(chǔ)時(shí)間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- windows無(wú)法啟動(dòng)MySQL服務(wù)報(bào)錯(cuò)1067的解決方法
- mysql 數(shù)據(jù)插入優(yōu)化方法之concurrent_insert
- MySQL的Query Cache圖文詳解
- mysql優(yōu)化之query_cache_limit參數(shù)說(shuō)明
- MySQL連表查詢分組去重的實(shí)現(xiàn)示例
- MySQL在Windows中net start mysql 啟動(dòng)MySQL服務(wù)報(bào)錯(cuò) 發(fā)生系統(tǒng)錯(cuò)誤解決方案