mysql中的date、datetime、timestamp你還不知道怎麼使用嗎

北漂程式設計師發表於2022-05-01
MySql

在後端開發中經常會碰到涉及資料庫的場景，不知道螢幕前的你有這樣的疑惑沒有，每每在遇到時間、日期欄位總是讓人頭疼，分不清到底是選date還是datetime，亦或是timestamp，真是抓耳撓腮啊，怎麼辦吶，大都翻看之前的表結構，把之前的時間、日期型別抄過來，草草了事。以至於在後面的開發中總是為時間、日期型別做特殊處理，過後便不再理會這事了，今天就把這事一次性說明白。這裡的環境是mysql

一、引言

在mysql中表示日期和時間的資料型別，大體上有下面幾種，

序號 名稱 描述 定義方式 格式 範圍
1 date 日期 date YYYY-MM-DD '1000-01-01' to '9999-12-31'
2 time 時間 time[.fraction] hh:mm:ss[.000000] '-838:59:59.000000' to '838:59:59.000000'
3 datetime 日期+時間 datetime[.fraction] YYYY-MM-DD hh:mm:ss[.000000] '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
4 timestamp 時間戳 timestamp[.fraction] YYYY-MM-DD hh:mm:ss[.000000] '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
5 year 年 year YYYY '1901' to '2155'

注意在上面的表格中，我把日期和時間分開進行了描述，主要是因為在很多場景中很容易把這兩個詞當作同一個意思，在這裡時間指的時分秒，日期指的是年月日。

比較常用的有date、datetime、timestamp，今天就對這三種表示日期和時間的型別進行分析，看下在實際開發過程中如何選擇？

二、日期/時間型別

為了弄清楚這三種型別之間的區別，我從網上也看了不少資料，最後發現還是要去官網找尋真理，感興趣的小夥伴可以看下mysql的官方文件：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html，

關於這些內容從下方的官方截圖都可以找到，

下面具體總結下

1、date

date僅包含日期，mysql儲存和顯示的格式是'YYYY-MM-DD'，可以表示的範圍是'1000-01-01' to '9999-12-31'

2、datetime

datetime包含日期、時間兩部分，mysql儲存和顯示的格式是'YYYY-MM-DD hh:mm:ss'，可以表示的範圍是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

3、timestamp

timestamp包含日期、時間兩部分，在儲存時儲存的是時間戳，可以表示的範圍是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC，這裡需要注意timestamp是有時區概念的，該型別可跟隨時區變化，預設情況下是伺服器的時區，如果一個資料庫連線設定了時區引數，那麼該型別的值會根據連線設定的時區來儲存和返回值。

在前面說到在儲存時儲存的是時間戳，也就是從1970-01-01 00:00:00 UTC到要儲存的時間所相差的毫秒數。關於UTC是世界協調是，感興趣的可以自己查閱資料。

 

還有強調下，

  1. 上面介紹了date、datetime、timestamp這三種資料型別，還有一個點需要著重強調，datetime、timestamp是可以有小數位的，一共最多可以有6位，所以在定義是可以是datetime(3)，要說明的是預設情況下是datetime(0)，由於是0所以通常情況下就倍省略了；
  2. timestamp的範圍剛才也看到了，最大到'2038-01-19 03:14:07' UTC，所以很多情況下最好不用該型別，避免超過最大範圍產生問題，可以datetime來代替；
  3. datetime、tiemstap型別有自動初始話和更新為當前的日期和時間的功能；

三、實踐

上面說了那麼多理論知識，下面肯定要實踐下，實踐出真知嘛，

下面是一個建表語句，

create table test(
 id int not null  auto_increment,
 t_date date default null,
 t_date_time datetime not null default current_timestamp,
 t_timestamp timestamp not null default current_timestamp on update  current_timestamp,
 t_date_time2 datetime(3) not null default current_timestamp(3),
 primary key(id)
 
 );

可以看到t_date_time是datetime型別，預設是自動插入當前日期+時間

t_timestamp是timestamp型別，預設自動插入當前日期+時間，且更新是更新為當前日期和時間

t_date_time2是datetime(3)型別，預設自動插入當前日期+時間，且current_timestamp(3)，

下面插入資料，

可以看到t_date_time、t_timestamp、t_date_time2都是可以自動賦值為當前日期+時間（我這裡系統時間就是2022-05-01，大家不用誤會），下面修改下，

可以看到t_timestamp的時間已經自動更新為當前'2022-05-01 06:37:32'了。很奇怪吧。

四、總結

本文主要分享了，mysql中常見的日期、時間資料型別，下面幾條需謹記，

1、datetime、timestamp都可以滿足需求的情況下，優先選擇datetime，因為其表示的範圍更大；

2、格式化方式為'YYYY-MM-DD hh:mm:ss[.000000]'，在進行插入的時候使用的是相應的字串的格式進行插入；

3、自動初始化和更新語句要牢記，'dt datetime not null default current_timestamp on update current_timestamp'；

4、current_timestamp可以寫成current_timestamp(0)；

5、標識記錄的新增或更新時間的欄位，設定為資料“default current_timestamp on update current_timestamp‘更省心；

 

相關文章