淺談Mysql時間的儲存 datetime還是時間戳timestamp

2022-07-26 14:03:18

簡單對比

佔用空間

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

從上表可以看到，DATETIME預設佔用5個位元組，而TIMESTAMP預設佔用4個位元組，如果需要更高精度的儲存（秒後的小數點個數，比如毫秒）那麼需要額外的儲存空間。

優缺對比

DATETIME佔用位元組較多，但表示範圍較大，與時區無關。
TIMESTAMPA:只能表示1970-2038年的時間，且B:不能用於分割區列（真的麼？還得查查好像又不一定也有說官方檔案說從MySQL 5.1.43開始，除了TIMESTAMP 外，其他日期型別都不接受！？），因為這種資料型別受時區限制，會受資料庫時區的影響。**C:**當MySQL引數time_zone=system時，查詢timestamp欄位會呼叫系統時區做時區轉換，而由於系統時區存在全域性鎖問題，在多並行巨量資料量存取時會導致執行緒上下文頻繁切換，CPU使用率暴漲，系統響應變慢設定假死。（但C似乎只在一個部落格看到這個問題，真的存在麼？）為了避免這種問題，記得手動設定時區

timestamp在mysql中定義的是int型別的資料，然後1970年到2038年的秒數剛好21億，為了限制，所以只能截止到2038年。雖然現在可以設定數位精度了但是資料精度提高的代價是其內部儲存空間的變大，但仍未改變時間戳型別的最小和最大取值範圍。
**但是我覺得吧隨著時間臨近，mysql會更新的。**而且還有這麼多年呢肯定也會有其他東西取代他

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

如何儲存毫秒或者更高階別的小數？

意思就是，毫秒部分需要以引數形式傳參給資料型別，預設是不儲存毫秒的，可以儲存1-6位。如果需要儲存三位的毫秒值，資料型別可以定義為DATETIME(3)或TIMESTAMP(3)，不需要儲存毫秒的話，只需要將型別直接寫為DATETIME TIMESTAMP。

時間戳詳解

一個方便的用法

在建立新記錄和修改現有記錄的時候都對這個資料列重新整理：（datetime也能用）

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

顯示格式（非儲存格式）

TIMESTAMP值可以從1970的某時的開始一直到2037年，精度為一秒，其值作為數位顯示。
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列也可以用更短的顯示尺寸，創造最常見的顯示尺寸是6、8、12、和14。
你可以在建立表時指定一個任意的顯示尺寸，但是定義列長為0或比14大均會被強制定義為列長14。
列長在從1～13範圍的奇數值尺寸均被強制為下一個更大的偶數。
這有以下含義

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

java可能遇到的坑

詳情請看原文：原文連結：https://www.jb51.net/article/255355.htm
送 sql 前，會將 jdbc 中的 Date 物件引數，根據 serverTimeZone 設定的時區轉化為日期字串後，再傳送 sql 請求給 mysql server，同樣在 mysql server 返回查詢結果後，結果中的日期值也是日期字串，mysql 驅動會根據 serverTimeZone 設定的時區，將日期字串轉化為 Date 物件。

因此，當 serverTimeZone 與資料庫實際時區不一致時，會發生時區轉換錯誤，導致時間偏差，如下：

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

b、然後，由於 serverTimeZone 設定的是東 8 區，mysql 驅動會將這個 Date 物件轉為2020-02-23 08:00:00，注意這時已經是字串了，然後再將 sql 傳送給 mysql，注意這裡的 sql 裡面已經將 Date 引數替換為2020-02-23 08:00:00了，因為 Date 物件本身是無法走網路的。

c、然後 mysql 資料庫接收到這個時間字串2020-02-23 08:00:00後，由於資料庫時區設定是東 9 區，它會認為這個時間是東 9 區的，它會以東 9 區解析這個時間字串，這時資料庫儲存的時間是東9區的2020-02-23 08:00:00，也就是東8區的2020-02-23 07:00:00，儲存的時間就偏差了 1 個小時。

d、查詢結果裡時間為什麼又對了呢，因為查詢結果返回了東 9 區的時間字串，而 java 應用又將其理解為是東 8 區的時間，負負得正了！

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

到此這篇關於淺談Mysql時間的儲存 datetime還是時間戳timestamp的文章就介紹到這了,更多相關Mysql時間的儲存內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！