mysql(5.5)所支持的日期時(shí)間類(lèi)型有：DATETIME、 TIMESTAMP刹孔、DATE桌吃、TIME、YEAR榄攀。
五種類(lèi)型表示如下:
日期時(shí)間類(lèi)型 | 占用空間 | 日期時(shí)間格式|最小值|最大值|零值表示
----|------|----|---|---|
YEAR | 1bytes | YYYY|1901 |2155 |0000
TIME | 3bytes| HH:MM:SS| -838:59:59|838:59:59|00:00:00
DATE |4bytes|YYYY-MM-DD|1000-01-01|9999-12-31|0000-00-00
DATETIME|8bytes|YYYY-MM-DD HH:MM:SS| 1000-01-01 00:00:00|9999-12-31 23:59:59|0000-00-00 00:00:00
TIMESTAMP|4bytes|YYYY-MM-DDHH:MM:SS|19700101080001|2038 年的某個(gè)時(shí)刻|00000000000000
當(dāng)插入值超出有效取值范圍時(shí)嗜傅，系統(tǒng)會(huì)報(bào)錯(cuò)，并將零值插入到數(shù)據(jù)庫(kù)中檩赢。
YEAR類(lèi)型
YEAR 用于表示年份,YEAR有2位和4位兩種格式吕嘀。 默認(rèn)是4位。如果實(shí)際應(yīng)用只有保存年份的需求贞瞒，那么用 1 bytes 保存 YEAR 類(lèi)型完全可以.不但能夠節(jié)約存儲(chǔ)空間偶房，還能提高表的操作效率。
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_time_1` year DEFAULT NULL,
`create_time_2` year(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
當(dāng)希望使用兩位格式的YEAR時(shí),要顯示聲明為year(2);當(dāng)希望使用四位格式的YEAR時(shí),可以直接聲明為year,系統(tǒng)默認(rèn)是四位,也可以顯示聲明為year(4).year(2)和year(4)賦值方法如下
- year(4)賦值
當(dāng)類(lèi)型為year(4)時(shí),直接插入4位字符串或者4位數(shù)字即可,有效值范圍是1901-2155,超過(guò)這個(gè)范圍賦值,系統(tǒng)會(huì)給出一個(gè)警告,并插入零值0000 - year(2)賦值
1.插入2位字符串或者兩位數(shù)字军浆，這種情況下如果插入‘00’‘69’或者00-69棕洋，則相當(dāng)于插入20002069；如果插入‘70’‘99’或者70-99瘾敢，則相當(dāng)于插入19701999拍冠。也可以看出當(dāng)聲明為year(2)時(shí),其實(shí)year類(lèi)型的有效范圍已經(jīng)被縮小了,且含義表達(dá)不如year(4)清楚明了.
insert into test(create_time_1)values(2017);
insert into test(create_time_1)values('2017');
insert into test(create_time_2)values('00');
insert into test(create_time_2)values('69');
insert into test(create_time_2)values(70);
insert into test(create_time_1)values(2255);#超出有效范圍
從上圖可以看出,當(dāng)插入值超過(guò)1901-2155范圍賦值,系統(tǒng)會(huì)給出一個(gè)警告,并插入零值0000.
TIME
TIME類(lèi)型表示為“時(shí):分:秒”尿这，即 ‘HH：MM：SS’.其有效值范圍是-838:59:59 838:59:59.一般而言小時(shí)范圍是023簇抵，但是為了表示某些特殊時(shí)間間隔，MySQL將TIME的小時(shí)范圍擴(kuò)大了射众，而且支持負(fù)值.TIME類(lèi)型只占3個(gè)字節(jié)碟摆，如果只是存儲(chǔ)時(shí)間數(shù)據(jù)，它最合適了叨橱。
對(duì)TIME類(lèi)型賦值典蜕，其格式有一下幾種
- ‘HH：MM：SS’標(biāo)準(zhǔn)格式
- 'HHMMSS'格式，系統(tǒng)能夠自動(dòng)轉(zhuǎn)化為標(biāo)準(zhǔn)格式罗洗。
- D HH：MM：SS’格式愉舔，此格式相當(dāng)于插入‘（D*24+HH）：MM：SS’。比如插入‘2 23:50:50’伙菜，相當(dāng)于插入了‘71:50:50’轩缤。
- ‘HH：MM’或‘SS’格式，其效果是其他未被表示位的值賦為零值。比如插入‘30’火的，相當(dāng)于插入了‘00:00:30’壶愤；如果插入‘11:25’，相當(dāng)于插入了‘11:25:00’馏鹤。
- ‘D HH’和‘D HH：MM’格式,效果按上面的例子可以推理出來(lái)了吧征椒。
如果我們想插入系統(tǒng)的當(dāng)前時(shí)間，則可以插入CURRENT_TIME或者NOW()湃累。
CREATE TABLE `test_time` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_time` time DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into test_time(create_time)values(current_time);
insert into test_time(create_time)values(now());
insert into test_time(create_time)values('11:11:11');
insert into test_time(create_time)values('121212');
insert into test_time(create_time)values('2 23:50:50');
insert into test_time(create_time)values('13:13');
insert into test_time(create_time)values('14');
#超出有效范圍
insert into test_time(create_time)values('840:60:60');
DATE
DATE類(lèi)型表示為"年-月-日",即"YYYY-MM-DD".其有效值范圍是1000-01-01-9999-12-31.除了標(biāo)準(zhǔn)語(yǔ)法格式"YYYY-MM-DD"勃救，MySQL還支持一些不嚴(yán)格的語(yǔ)法格式，分隔符“-”可以用“@”治力、“.”等其他符號(hào)替代剪芥。
在插入數(shù)據(jù)時(shí)，也可以使用“YY-MM-DD”格式琴许，YY轉(zhuǎn)化成對(duì)應(yīng)的年份的規(guī)則與YEAR類(lèi)型類(lèi)似税肪。
如果我們想插入系統(tǒng)的當(dāng)前時(shí)間，則可以插入CURRENT_DATE或者NOW()榜田。
CREATE TABLE `test_date` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into test_date(create_date)values(current_date);
insert into test_date(create_date)values(now());
insert into test_date(create_date)values('2017-01-01');
insert into test_date(create_date)values('2017@02@01');
insert into test_date(create_date)values('10000-12-31');
DATETIME
DATETIME就是 DATE 和 TIME 的組合標(biāo)準(zhǔn)格式為“YYYY-MM-DD HH：MM：SS”益兄，具體賦值方法與上面各種類(lèi)型的方法相似。
CREATE TABLE `test_datetime` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_datetime` datetime DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,##我看公司的是這樣寫(xiě)的,為什么錯(cuò)了呢.
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
TIMESTAMP類(lèi)型
TIMESTAMP 用于表示年-月-日 時(shí):分:秒箭券，但是記錄的年份比較短暫净捅。TIMESTAMP 和時(shí)區(qū)相關(guān)，更能反映當(dāng)前時(shí)間辩块。當(dāng)插入日期時(shí)蛔六，會(huì)先轉(zhuǎn)換為本地時(shí)區(qū)后再存放；當(dāng)查詢?nèi)掌跁r(shí)废亭，會(huì)將日期轉(zhuǎn)換為本地時(shí)區(qū)后再顯示国章。所以不同時(shí)區(qū)的人看到的同一時(shí)間是不一樣的。它的插入也與插入其他日期和時(shí)間數(shù)據(jù)類(lèi)型類(lèi)似豆村。如果記錄的日期需要讓不同時(shí)區(qū)的人使用液兽，最好使用 TIMESTAMP。
那么TIMESTAMP類(lèi)型如何插入當(dāng)前時(shí)間掌动？
- 可以使用CURRENT_TIMESTAMP四啰；
- 輸入NULL，系統(tǒng)自動(dòng)輸入當(dāng)前的TIMESTAMP
- 無(wú)任何輸入粗恢，系統(tǒng)自動(dòng)輸入當(dāng)前的TIMESTAMP柑晒。
TIMESTAMP是一個(gè)非常特殊的時(shí)間類(lèi)型.
- 如果表中只有一個(gè)TIMESTAMP類(lèi)型的字段.系統(tǒng)會(huì)默認(rèn)設(shè)置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
- 如果表中有兩個(gè)TIMESTAMP類(lèi)型的字段,系統(tǒng)會(huì)如何應(yīng)對(duì)呢.
用以下定義來(lái)創(chuàng)建一個(gè)表.
CREATE TABLE `test_timestamp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_timestamp` timestamp,
`create_timestamp2` timestamp ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
用show來(lái)看看系統(tǒng)自動(dòng)補(bǔ)全了什么
CREATE TABLE `test_timestamp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`create_timestamp2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
從系統(tǒng)補(bǔ)全結(jié)果可以看出 表中的第一個(gè) TIMESTAMP 列系統(tǒng)會(huì)默認(rèn)設(shè)置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,這與表中只有一個(gè) TIMESTAMP 列設(shè)置是相同的。第二個(gè) TIMESTAMP 列眷射，系統(tǒng)會(huì)默認(rèn)設(shè)置DEFAULT '0000-00-00 00:00:00',且沒(méi)有on update設(shè)置.這就告訴我們,在我們自己定義表示表時(shí),不可以這樣
CREATE TABLE `test_timestamp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`create_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
`create_timestamp2` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
從報(bào)錯(cuò)結(jié)果中可以看出,一個(gè)表中可以有兩個(gè)TIMESTAMP類(lèi)型的列,但是這兩個(gè)列不可以同時(shí)設(shè)置DEFAULT CURRENT_TIMESTAMP 或ON UPDATE CURRENT_TIMESTAMP 屬性.
- 時(shí)間類(lèi)型為 Timestamp的列,即使此列規(guī)定NOT NULL,如果往這屬性的列中插入Null值的話匙赞，仍可以插入NULL成功,且系統(tǒng)會(huì)插入一個(gè)當(dāng)前時(shí)間.當(dāng)有兩個(gè) Timestamp的列時(shí),如果往這兩個(gè)列中均插入Null值的話,則兩個(gè)都會(huì)插入系統(tǒng)當(dāng)前時(shí)間.
注意:并不是所有的時(shí)間類(lèi)型都是特例,datetime,date,time,year類(lèi)型如果規(guī)定NOT NULL,則不可以插入NULL值. - 無(wú)任何輸入.第一個(gè)系統(tǒng)會(huì)插入一個(gè)當(dāng)前時(shí)間,第二列系統(tǒng)會(huì)插入一個(gè)零值.
insert into test_timestamp(id) values(null);#無(wú)任何輸入
insert into test_timestamp values(null,null,null);#插入NULL
