mysql中 datatime與timestamp的區別說明

Posted on by

mysql中有三種日期類型：

date(年-月-日)

create table test(hiredate date);

datetime（日期時間類型）

create table test(hiredate datetime)

timestamp（郵戳類型，保存年-月-日 時-分-秒）

create table test(hiredate timestamp)

datetime 和timestamp的不同點

1、兩者的存儲方式不一樣

對於TIMESTAMP，它把客戶端插入的時間從當前時區轉化為UTC（世界標準時間）進行存儲。查詢時，將其又轉化為客戶端當前時區進行返回。而對於DATETIME，不做任何改變，基本上是原樣輸入和輸出。

2、兩者所能存儲的時間范圍不一樣

timestamp所能存儲的時間范圍為：’1970-01-01 00:00:01.000000′ 到 ‘2038-01-19 03:14:07.999999’。

datetime所能存儲的時間范圍為：’1000-01-01 00:00:00.000000′ 到 ‘9999-12-31 23:59:59.999999’。

總結：TIMESTAMP和DATETIME除瞭存儲范圍和存儲方式不一樣，沒有太大區別。當然，對於跨時區的業務，TIMESTAMP更為合適。

3、自動初始化和更新

自動初始化指的是如果對該字段（譬如上例中的hiredate字段）沒有顯性賦值，則自動設置為當前系統時間。

自動更新指的是如果修改瞭其它字段，則該字段的值將自動更新為當前系統時間。

它與“explicit_defaults_for_timestamp”參數有關。

默認情況下，該參數的值為OFF

mysql> show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
row in set (0.00 sec)

總結：timestamp有自動初始化和更新，當你update某條記錄的時候，該列值會自動更新，這是和datatime最大的區別。

補充：DATETIME與TIMESTAMP的一些區別與問題

今天遇到一個問題，明明數據有更新，update_time字段卻還停留在創建數據的時候。

按常理來說這個字段應該是自動更新的才對。

查瞭一下表結構，

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

發現update_time字段的類型是datetime

由此牽扯出兩個問題，（1）timestamp與datetime的區別；（2）CURRENT_TIMESTAMP為什麼能用於datetime類型

timestamp與datetime的區別

a）DATETIME的默認值為null；TIMESTAMP的字段默認不為空（not null）,默認值為當前時間（CURRENT_TIMESTAMP），如果不做特殊處理，並且update語句中沒有指定該列的更新值，則默認更新為當前時間。

這個區別就解釋瞭為什麼平時我們都不用可以管這個字段就能自動更新瞭，因為多數時候用的是timestamp；而此處用的是datetime，不會有自動更新當前時間的機制，所以需要在上層手動更新該字段

b）DATETIME使用8字節的存儲空間，TIMESTAMP的存儲空間為4字節。因此，TIMESTAMP比DATETIME的空間利用率更高。

這個區別解釋瞭為啥timestamp類型用的多

c）兩者的存儲方式不一樣 ，對於TIMESTAMP，它把客戶端插入的時間從當前時區轉化為UTC（世界標準時間）進行存儲。查詢時，將其又轉化為客戶端當前時區進行返回。而對於DATETIME，不做任何改變，基本上是原樣輸入和輸出。

d）兩者所能存儲的時間范圍不一樣

timestamp所能存儲的時間范圍為：’1970-01-01 00:00:01.000000′ 到 ‘2038-01-19 03:14:07.999999’；

datetime所能存儲的時間范圍為：’1000-01-01 00:00:00.000000′ 到 ‘9999-12-31 23:59:59.999999’。

CURRENT_TIMESTAMP為什麼能用於datetime類型

在mysql 5.6之前的版本，CURRENT_TIMESTAMP隻能用於timestamp類型，

5.6版本之後，CURRENT_TIMESTAMP也能用於datetime類型瞭

select version()查瞭一下數據庫發現確實版本是5.6.29

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：