各種日期數(shù)據(jù)類型及其所占空間
| 類型 | 所占空間 |
|---|---|
| DateTIME | 8字節(jié) |
| Date | 3字節(jié) |
| TIMESTAMP | 4字節(jié) |
| YEAR | 1字節(jié) |
| TIME | 3字節(jié) |
DATETIME &DATE
DATETIME占用8字節(jié)浮入，日期范圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
DATE占用三個字節(jié),日期范圍是'1000-01-01'到'9999-12-31'寝优。
MySQL數(shù)據(jù)庫中,對日期和時間的輸入格式是比較寬松的赡鲜，一下輸入均可以:
- 2011-01-01 00:01:10
- 2011/01/01 00+01+10
- 11/01/01 00@01@10
TIMESTAMP
TIMESTAMP和DATETIME顯示的結(jié)果是一樣的，都是固定的"YYYY-MM-DD HH:MM:SS"的形式喊积，不過TIMESTAMP只占了四個字節(jié)椰拒，顯示范圍是"1970-01-01 00:00:00"到"2038-01-19 03:14:07"逊朽。
TIMESTAMP和DATETIME除了顯示時間不同外罕伯，還有以下不同:
- 在建表時，列為TIMESTAMP的日期類型可以設(shè)置默認(rèn)值叽讳，而DATETIME不行追他；
- 在更新表時，可以設(shè)置TIMESTAMP類型的列自動更新為當(dāng)前時間岛蚤。
create table c(
id INT primary key,
time TIMESTAMP
)ENGINE=InnoDB;
從上圖我們可以看出邑狸，TIMESTAMP類型的行在表創(chuàng)建后會被MySQL設(shè)置自動更新為當(dāng)前時間。我在使用TIMESTAMP類型時吃過虧：因?yàn)樵撔械拇嬖冢?strong>主從數(shù)據(jù)檢校一直出錯涤妒，但排查了好半天的錯誤最后才定位出來单雾。
那如果要取消TIMESTAMP的這種默認(rèn)自動更新,同時又需要TIMESTAMP默認(rèn)值為當(dāng)前值，可以使用:
ALTER TABLE c
CHANGE time time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
YEAR和TIME
YEAR類型占用1個字節(jié)她紫，并且在5.5.27版本以前定義時就可以指定顯示的寬度YEAR(4)或者YEAR(2)硅堆。5.5.27版本以后只能使用YEAR(4)。YEAR的范圍是1970~2070贿讹；
TIME類型占用3個字節(jié)渐逃，顯示范圍是"-838:59:59"~"839:59:59"。為什么TIME類型的時間可以大于23民褂。那是因?yàn)門IME類型不僅可以用來保存一天中的時間茄菊，還可以用來保存時間間隔疯潭。不過我們也不難看出，保存的時間間隔不是很長买羞，840/24=3天半袁勺。TIME類型用得很少。
日期相關(guān)函數(shù)
NOW()畜普、CURRENT_TIMESTAMP和SYSDATE
從上面圖片我們可以看出三個函數(shù)的一些區(qū)別:
- CURRENT_TIMESTAMP和NOW()是一樣的；
- SYSDATE()函數(shù)返回的是執(zhí)行當(dāng)前函數(shù)的時間群叶，NOW返回的是執(zhí)行SQL語句的時間吃挑。
時間加減時間:DATE_ADD、DATE_SUB
mysql> select DATE_ADD('2016-09-28 01:00:00',INTERVAL 1 YEAR) as a,
-> DATE_ADD('2016-09-28 01:00:00',INTERVAL "20:30" HOUR_MINUTE) as b;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2017-09-28 01:00:00 | 2016-09-28 21:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
除了YEAR街立、HOUR_MUNITE數(shù)據(jù)類型外舶衬，還有下面的數(shù)據(jù)類型:
| unit Value | Expected expr Format |
|---|---|
| MICROSECONS | MICROSECONDS |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| WEEK | WEEKS |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| YEAR | YEARS |
| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
| MINUTE_SECOND | 'MINUTES:SECONDS' |
| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
| HOUR_MINUTE | 'HOURS:MINUTES' |
| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
| DAY_HOUR | 'DAYS HOURS' |
| YEAR_MONTH | 'YEARS-MONTHS' |
DATE_FORMAT函數(shù)
DATE_FORMAT函數(shù)沒什么好說的，就是按照需求打印日期格式:
mysql> select date_format(now(),'%Y%m%d') as date;
+----------+
| date |
+----------+
| 20160928 |
+----------+
不過如果濫用這個函數(shù)會造成很嚴(yán)重的后果赎离，一般來說表中的時間列都是使用索引的逛犹，但是下面的語句，MySQL是用不了索引的梁剔，會導(dǎo)致查詢效率非常低下:
select * from table where DATE_FORMAT(date,'%Y-%m-%d')='xxxx-xx-xx'
