Mysql日期（DATE, TIME, DATETIME, TIMESTAMP）類型的比較

最近發(fā)現(xiàn)自己一直以來(lái)經(jīng)常用一種錯(cuò)誤的方法在比較日期，例如一下語(yǔ)句，a字段是一個(gè)DATETIME類型的列：

select a from t1 where DATEDIFF(a, '2018-09-11') < 0;

該語(yǔ)句是為了查出2018-09-11 日期之前的所有記錄，但這個(gè)語(yǔ)句有個(gè)問(wèn)題，如果a字段加了索引，用這種類型進(jìn)行日期對(duì)比，會(huì)導(dǎo)致查詢不走索引，從而使查詢性能下降。

看了Mysql關(guān)于日期字段對(duì)比的文檔。在進(jìn)行日期比較的時(shí)候，mysql會(huì)自動(dòng)將日期轉(zhuǎn)換成數(shù)字進(jìn)行比較。where條件后，使用字符串格式日期與DATE,DATETIME,TIMESTAMP,TIME類型字段進(jìn)行比較的時(shí)候，對(duì)字符串格式要求不嚴(yán)格，你可以使用任意格式的分隔符來(lái)表示日期，例如"2018-09-11","2018#09#11","2018&09&11"對(duì)于mysql來(lái)說(shuō)，都是相同的日期。如果沒(méi)有分隔符，例如"20180911"，與"2018-09-11"或其他有分隔符的日期，也是相同的。例如下圖

select a from t1 where a < '2018#09#11';
與
select a from t1 where a < '2018-09-11';
與
select a from t1 where a < '20180911';
所代表的意義是相同的，都是查詢?nèi)掌谛∮?018年9月11日的數(shù)據(jù)
也就是說(shuō)上圖的查詢語(yǔ)句，完全可以重寫(xiě)為，這么做的好處？就是會(huì)使用索引，是查詢更快
select a from t1 where a < '2018-09-11';

當(dāng)將日期類型字段與字符串型日期進(jìn)行<，>，>=，<=，between比較的時(shí)候，Mysql會(huì)將字符串類型日期轉(zhuǎn)換成長(zhǎng)整型數(shù)字進(jìn)行比較，從而加快查詢速度。

下面三種情況除外：

1，兩個(gè)表格字段的對(duì)比；

2，日期類型字段與表達(dá)式對(duì)比；

3，使用表達(dá)式對(duì)日期類型字段進(jìn)行對(duì)比；

原因：對(duì)于以上三種情況，mysql會(huì)將日期轉(zhuǎn)換為字符串進(jìn)行比較。

下面的例子，都是可以正常運(yùn)行的：

INSERT INTO t1 (testdate) VALUES (20180912);
INSERT INTO t1 (testdate) VALUES ('20180912');
INSERT INTO t1 (testdate) VALUES ('18-09-12');
INSERT INTO t1 (testdate) VALUES ('2018.09.12');
INSERT INTO t1 (testdate) VALUES ('2018 09 12');
INSERT INTO t1 (testdate) VALUES ('0000-00-00');
SELECT testdate FROM t1 WHERE testdate >= '2018-09-12';
SELECT testdate FROM t1 WHERE testdate >= 20180912;
SELECT MOD(testdate,100) FROM t1 WHERE testdate >= 20180912;
SELECT testdate FROM t1 WHERE testdate >= '20180912';

Mysql允許存儲(chǔ)"0000-00-00"作為DATE類型的“0”值，也稱之為虛擬日期。在某些場(chǎng)景下比儲(chǔ)存NULL值更便捷。如果將一個(gè)不合法的日期值保存到DATE類型字段中，mysql默認(rèn)存儲(chǔ)為"0000-00-00"。 如果不允許存儲(chǔ)"0"值，請(qǐng)啟用NO_ZERO_DATE參數(shù)。