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

轉載：https://blog.csdn.net/u013262534/article/details/82629290

最近發現自己一直以來經常用一種錯誤的方法在比較日期，例如一下語句，a欄位是一個DATETIME型別的列：

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

該語句是為了查出2018-09-11 日期之前的所有記錄，但這個語句有個問題，如果a欄位加了索引，用這種型別進行日期對比，會導致查詢不走索引，從而使查詢效能下降。

看了Mysql關於日期欄位對比的文件。在進行日期比較的時候，mysql會自動將日期轉換成數字進行比較。where條件後，使用字串格式日期與DATE,DATETIME,TIMESTAMP,TIME型別欄位進行比較的時候，對字串格式要求不嚴格，你可以使用任意格式的分隔符來表示日期，例如"2018-09-11","2018#09#11","2018&09&11"對於mysql來說，都是相同的日期。如果沒有分隔符，例如"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';
 
所代表的意義是相同的，都是查詢日期小於2018年9月11日的資料
也就是說上圖的查詢語句，完全可以重寫為，這麼做的好處？就是會使用索引，是查詢更快
 
select a from t1 where a < '2018-09-11';

當將日期型別欄位與字串型日期進行<，>，>=，<=，between比較的時候，Mysql會將字串型別日期轉換成長整型數字進行比較，從而加快查詢速度。

下面三種情況除外：

1，兩個表格欄位的對比；

2，日期型別欄位與表示式對比；

3，使用表示式對日期型別欄位進行對比；

原因：對於以上三種情況，mysql會將日期轉換為字串進行比較。

下面的例子，都是可以正常執行的：
 

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允許儲存"0000-00-00"作為DATE型別的“0”值，也稱之為虛擬日期。在某些場景下比儲存NULL值更便捷。如果將一個不合法的日期值儲存到DATE型別欄位中，mysql預設儲存為"0000-00-00"。 如果不允許儲存"0"值，請啟用NO_ZERO_DATE引數。


mysql的5種時間型別的比較
轉載：https://blog.csdn.net/weixin_39220472/article/details/80458341


可以設定預設值：

ts_time1 time NOT NULL DEFAULT NOW();
ts_time3 year NOT NULL DEFAULT NOW();
ts_time2 date NOT NULL DEFAULT CURRENT_TIMESTAMP();
ts_time2 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP();