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

mysql中有三種日期類型：

date(年-月-日)

create table test(hiredate date);

datetime（日期時(shí)間類型）

create table test(hiredate datetime)

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

create table test(hiredate timestamp)

datetime 和timestamp的不同點(diǎn)

1、兩者的存儲(chǔ)方式不一樣

對(duì)于TIMESTAMP，它把客戶端插入的時(shí)間從當(dāng)前時(shí)區(qū)轉(zhuǎn)化為UTC（世界標(biāo)準(zhǔn)時(shí)間）進(jìn)行存儲(chǔ)。查詢時(shí)，將其又轉(zhuǎn)化為客戶端當(dāng)前時(shí)區(qū)進(jìn)行返回。而對(duì)于DATETIME，不做任何改變，基本上是原樣輸入和輸出。

2、兩者所能存儲(chǔ)的時(shí)間范圍不一樣

timestamp所能存儲(chǔ)的時(shí)間范圍為：'1970-01-01 00:00:01.000000' 到 ‘2038-01-19 03:14:07.999999'。

datetime所能存儲(chǔ)的時(shí)間范圍為：'1000-01-01 00:00:00.000000' 到 ‘9999-12-31 23:59:59.999999'。

總結(jié)：TIMESTAMP和DATETIME除了存儲(chǔ)范圍和存儲(chǔ)方式不一樣，沒(méi)有太大區(qū)別。當(dāng)然，對(duì)于跨時(shí)區(qū)的業(yè)務(wù)，TIMESTAMP更為合適。

3、自動(dòng)初始化和更新

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

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

它與“explicit_defaults_for_timestamp”參數(shù)有關(guān)。

默認(rèn)情況下，該參數(shù)的值為OFF

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

總結(jié)：timestamp有自動(dòng)初始化和更新，當(dāng)你update某條記錄的時(shí)候，該列值會(huì)自動(dòng)更新，這是和datatime最大的區(qū)別。

補(bǔ)充：DATETIME與TIMESTAMP的一些區(qū)別與問(wèn)題

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

按常理來(lái)說(shuō)這個(gè)字段應(yīng)該是自動(dòng)更新的才對(duì)。

查了一下表結(jié)構(gòu)，

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

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

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

timestamp與datetime的區(qū)別

a）DATETIME的默認(rèn)值為null；TIMESTAMP的字段默認(rèn)不為空（not null）,默認(rèn)值為當(dāng)前時(shí)間（CURRENT_TIMESTAMP），如果不做特殊處理，并且update語(yǔ)句中沒(méi)有指定該列的更新值，則默認(rèn)更新為當(dāng)前時(shí)間。

這個(gè)區(qū)別就解釋了為什么平時(shí)我們都不用可以管這個(gè)字段就能自動(dòng)更新了，因?yàn)槎鄶?shù)時(shí)候用的是timestamp；而此處用的是datetime，不會(huì)有自動(dòng)更新當(dāng)前時(shí)間的機(jī)制，所以需要在上層手動(dòng)更新該字段

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

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

c）兩者的存儲(chǔ)方式不一樣 ，對(duì)于TIMESTAMP，它把客戶端插入的時(shí)間從當(dāng)前時(shí)區(qū)轉(zhuǎn)化為UTC（世界標(biāo)準(zhǔn)時(shí)間）進(jìn)行存儲(chǔ)。查詢時(shí)，將其又轉(zhuǎn)化為客戶端當(dāng)前時(shí)區(qū)進(jìn)行返回。而對(duì)于DATETIME，不做任何改變，基本上是原樣輸入和輸出。

d）兩者所能存儲(chǔ)的時(shí)間范圍不一樣

timestamp所能存儲(chǔ)的時(shí)間范圍為：'1970-01-01 00:00:01.000000' 到 ‘2038-01-19 03:14:07.999999'；

datetime所能存儲(chǔ)的時(shí)間范圍為：'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()查了一下數(shù)據(jù)庫(kù)發(fā)現(xiàn)確實(shí)版本是5.6.29

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。