mysql中 datatime與timestamp的區(qū)別說明

mysql中有三種日期類型：

date(年-月-日)

create table test(hiredate date);

datetime（日期時間類型）

create table test(hiredate datetime)

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

create table test(hiredate timestamp)

datetime 和timestamp的不同點

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

對于TIMESTAMP，它把客戶端插入的時間從當前時區(qū)轉化為UTC（世界標準時間）進行存儲。查詢時，將其又轉化為客戶端當前時區(qū)進行返回。而對于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除了存儲范圍和存儲方式不一樣，沒有太大區(qū)別。當然，對于跨時區(qū)的業(yè)務，TIMESTAMP更為合適。

3、自動初始化和更新

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

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

它與“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最大的區(qū)別。

補充：DATETIME與TIMESTAMP的一些區(qū)別與問題

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

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

查了一下表結構，

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

發(fā)現update_time字段的類型是datetime

由此牽扯出兩個問題，（1）timestamp與datetime的區(qū)別；（2）CURRENT_TIMESTAMP為什么能用于datetime類型

timestamp與datetime的區(qū)別

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

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

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

這個區(qū)別解釋了為啥timestamp類型用的多

c）兩者的存儲方式不一樣 ，對于TIMESTAMP，它把客戶端插入的時間從當前時區(qū)轉化為UTC（世界標準時間）進行存儲。查詢時，將其又轉化為客戶端當前時區(qū)進行返回。而對于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()查了一下數據庫發(fā)現確實版本是5.6.29

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