簡單對比

占用空間

MySQL 常用的日期時間類型常用的是datetime、timestamp。除此之外 還有用的不多的YEAR DATE TIME
注意5.6.4的版本

從上表可以看到，DATETIME默認占用5個字節(jié)，而TIMESTAMP默認占用4個字節(jié)，如果需要更高精度的存儲（秒后的小數(shù)點個數(shù)，比如毫秒）那么需要額外的存儲空間。

優(yōu)缺對比

• DATETIME占用字節(jié)較多，但表示范圍較大，與時區(qū)無關(guān)。
• TIMESTAMPA:只能表示1970-2038年的時間，且B:不能用于分區(qū)列（真的么？還得查查 好像又不一定 也有說 官方文檔說從MySQL 5.1.43開始，除了TIMESTAMP 外，其他日期類型都不接受！？），因為這種數(shù)據(jù)類型受時區(qū)限制，會受數(shù)據(jù)庫時區(qū)的影響。**C:**當MySQL參數(shù)time_zone=system時，查詢timestamp字段會調(diào)用系統(tǒng)時區(qū)做時區(qū)轉(zhuǎn)換，而由于系統(tǒng)時區(qū)存在全局鎖問題，在多并發(fā)大數(shù)據(jù)量訪問時會導致線程上下文頻繁切換，CPU使用率暴漲，系統(tǒng)響應變慢設(shè)置假死。（但C似乎只在一個博客看到這個問題，真的存在么？）為了避免這種問題，記得手動設(shè)置時區(qū)

timestamp在mysql中定義的是int類型的數(shù)據(jù)，然后1970年到2038年的秒數(shù)剛好21億，為了限制，所以只能截止到2038年。雖然現(xiàn)在可以設(shè)置數(shù)字精度了 但是數(shù)據(jù)精度提高的代價是其內(nèi)部存儲空間的變大，但仍未改變時間戳類型的最小和最大取值范圍。
**但是我覺得吧 隨著時間臨近，mysql會更新的。**而且還有這么多年呢 肯定也會有其他東西取代他

此外還有語言提供的字符串類型，10位（精確到秒）或13位（精確到毫秒）。其中13位必須bigint存儲，占用8字節(jié)，而且在顯示的時候，mysql不會自動轉(zhuǎn)成我們常見的日期格式，所以不推薦使用。

如何存儲毫秒或者更高級別的小數(shù)？

意思就是，毫秒部分需要以參數(shù)形式傳參給數(shù)據(jù)類型，默認是不保存毫秒的，可以保存1-6位。如果需要保存三位的毫秒值，數(shù)據(jù)類型可以定義為DATETIME(3)或TIMESTAMP(3)，不需要保存毫秒的話，只需要將類型直接寫為DATETIME TIMESTAMP。

時間戳詳解

一個方便的用法

在創(chuàng)建新記錄和修改現(xiàn)有記錄的時候都對這個數(shù)據(jù)列刷新：（datetime也能用）

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

顯示格式（非存儲格式）

TIMESTAMP值可以從1970的某時的開始一直到2037年，精度為一秒，其值作為數(shù)字顯示。
TIMESTAMP值顯示尺寸的格式如下表所示：

+---------------+----------------+
| 列類型　　　　| 顯示格式　　　 |
| TIMESTAMP(14) | YYYYMMDDHHMMSS |　
| TIMESTAMP(12) | YYMMDDHHMMSS　 |
| TIMESTAMP(10) | YYMMDDHHMM　　 |
| TIMESTAMP(8)　| YYYYMMDD　　　 |
| TIMESTAMP(6)　| YYMMDD　　　　 |
| TIMESTAMP(4)　| YYMM　　　　　 |
| TIMESTAMP(2)　| YY　　　　　　 |
+---------------+----------------+

“完整”TIMESTAMP格式是14位，但TIMESTAMP列也可以用更短的顯示尺寸，創(chuàng)造最常見的顯示尺寸是6、8、12、和14。
你可以在創(chuàng)建表時指定一個任意的顯示尺寸，但是定義列長為0或比14大均會被強制定義為列長14。
列長在從1～13范圍的奇數(shù)值尺寸均被強制為下一個更大的偶數(shù)。
這有以下含義

• 雖然你建表時定義了列TIMESTAMP(8)，但在你進行數(shù)據(jù)插入與更新時TIMESTAMP列實際上保存了14位的數(shù)據(jù)（包括年月日時分秒），只不過在你進行查詢時MySQL返回給你的是8位的年月日數(shù)據(jù)。如果你使用ALTER TABLE拓寬一個狹窄的TIMESTAMP列，以前被“隱蔽”的信息將被顯示。
• 同樣，縮小一個TIMESTAMP列不會導致信息失去，除了感覺上值在顯示時，較少的信息被顯示出。
• 盡管TIMESTAMP值被存儲為完整精度，直接操作存儲值的唯一函數(shù)是UNIX_TIMESTAMP()；由于MySQL返回TIMESTAMP列的列值是進過格式化后的檢索的值，這意味著你可能不能使用某些函數(shù)來操作TIMESTAMP列（例如HOUR()或SECOND()），除非TIMESTAMP值的相關(guān)部分被包含在格式化的值中。
• 例如，一個TIMESTAMP列只有被定義為TIMESTAMP(10)以上時，TIMESTAMP列的HH部分才會被顯示，因此在更短的TIMESTAMP值上使用HOUR()會產(chǎn)生一個不可預知的結(jié)果。
• 不合法TIMESTAMP值被變換到適當類型的“零”值(00000000000000)。（DATETIME,DATE亦然）

java可能遇到的坑

詳情請看原文：原文鏈接：http://www.fzitv.net/article/255355.htm
送 sql 前，會將 jdbc 中的 Date 對象參數(shù)，根據(jù) serverTimeZone 配置的時區(qū)轉(zhuǎn)化為日期字符串后，再發(fā)送 sql 請求給 mysql server，同樣在 mysql server 返回查詢結(jié)果后，結(jié)果中的日期值也是日期字符串，mysql 驅(qū)動會根據(jù) serverTimeZone 配置的時區(qū)，將日期字符串轉(zhuǎn)化為 Date 對象。

因此，當 serverTimeZone 與數(shù)據(jù)庫實際時區(qū)不一致時，會發(fā)生時區(qū)轉(zhuǎn)換錯誤，導致時間偏差，如下：

a、比如 sql 參數(shù)是一個 Date 對象，時間值是東 8 區(qū)的2020-02-23 08:00:00，注意它里面存儲的可不是2020-02-23 08:00:00這個字符串，它是 Date 對象(絕對時間)，只是我用文字表達出來是東 8 區(qū)的2020-02-23 08:00:00。

b、然后，由于 serverTimeZone 配置的是東 8 區(qū)，mysql 驅(qū)動會將這個 Date 對象轉(zhuǎn)為2020-02-23 08:00:00，注意這時已經(jīng)是字符串了，然后再將 sql 發(fā)送給 mysql，注意這里的 sql 里面已經(jīng)將 Date 參數(shù)替換為2020-02-23 08:00:00了，因為 Date 對象本身是無法走網(wǎng)絡的。

c、然后 mysql 數(shù)據(jù)庫接收到這個時間字符串2020-02-23 08:00:00后，由于數(shù)據(jù)庫時區(qū)配置是東 9 區(qū)，它會認為這個時間是東 9 區(qū)的，它會以東 9 區(qū)解析這個時間字符串，這時數(shù)據(jù)庫保存的時間是東9區(qū)的2020-02-23 08:00:00，也就是東8區(qū)的2020-02-23 07:00:00，保存的時間就偏差了 1 個小時。

d、查詢結(jié)果里時間為什么又對了呢，因為查詢結(jié)果返回了東 9 區(qū)的時間字符串，而 java 應用又將其理解為是東 8 區(qū)的時間，負負得正了！

時間戳查詢的時候 能否返回原生的時間戳呢

到此這篇關(guān)于淺談Mysql時間的存儲 datetime還是時間戳timestamp的文章就介紹到這了,更多相關(guān)Mysql時間的存儲 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論