MySQL日期和時間類型筆記
- 2019 年 10 月 13 日
- 筆記
最近在看《MySQL技術內幕：SQL編程》並做了筆記，這是一篇筆記類型部落格，分享出來方便自己複習，也可以幫助其他人
一、日期時間類型所佔空間對比
各種日期時間數據類型所佔的空間：
| 類型 | 所佔空間 |
|---|---|
| DATETIME | 8位元組 |
| DATE | 3位元組 |
| TIMESTAMP | 4位元組 |
| YEAR | 1位元組 |
| TIME | 3位元組 |
二、DATETIME和DATE對比
- DATETIME佔用8位元組，既顯示了日期也顯示時間，可以表示的日期範圍為「1000-01-01 00:00:00」到「9999-12-31 23:59:59」
- DATE佔用3位元組，只顯示日期，不顯示具體時間，可顯示的日期範圍為「1000-01-01」到「9999-12-31」
ok，這裡特意介紹一下TIMESTAMP秒的小數部分問題
備註：5.6.4+版本才支援秒的小數部分，之前版本是不支援的
# 查詢MySQL版本 select version(); # 建表驗證問題 create table t (a datetime); # 寫數據秒後面加上小數 insert into t select '2019-10-11 17:16:12.55555'; # 查詢，發現並沒有查出秒之後的小數 select * from t ; # 使用microsecond，讀取秒之後的小數 select microsecond('2019-10-11 17:16:12.55555') ; # CAST讀取，驗證了5.7+版本查詢時候會出現四捨五入，如下sql得到2019-10-11 17:16:12，而低版本就不會 SELECT CAST('2019-10-11 17:16:12.5555' AS DATETIME) ; # CAST讀取，5.7+版本查詢，四捨五入得到，2019-10-11 17:16:12，低版本正常顯示，具體哪個版本開始的不知道，我在5.7+版本驗證都是會出現四捨五入的情況 select cast('2019-10-11 17:16:12.1234' as datetime) ; # 5.6.4+版本支援秒的小數部分 # 支援的類型有TIME、DATETIME、TIMESTAMP，寫法是type(size)，size為小數部分精度，最大為6 # 刪表，再驗證一下 DROP TABLE t; # 這裡指定精度 CREATE TABLE t (a DATETIME(4)); # 秒後加小數，寫數據 INSERT INTO t SELECT '2019-10-11 17:16:12.55555'; # 查詢，發現可以正常寫數據，不過精度只有4 SELECT * FROM t ; 三、TIMESTAMP類型介紹
TIMESTAMP佔4個位元組，顯示的範圍為「1970-01-01」UTC 到 「2038-01-19 03:14:07」UTC
備註：UTC：協調世界時間、又稱世界統一時間、世界標準時間和國際協調時間
注意：
- 更新表時，可以設置TIMESTAMP類型的列，自動更新時間為當前時間
- 列為TIMESTAMP的日期類型可以設置一個默認值，而DATETIME不支援
例子，驗證一下，設置默認值和自動更新時間
# 新增一張表 CREATE TABLE t ( a INT, b TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE = INNODB ; # 寫一條數據 INSERT INTO t (a) VALUES (1); # 查詢，發現自動賦默認值時間 SELECT * FROM t; 
驗證自動更新時間問題
# 修改欄位為自動更新(數據有改變時候才會自動更新) ALTER TABLE t MODIFY COLUMN b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; # 刪一下表數據 DELETE FROM t; # 寫初始化數據 INSERT INTO t SELECT 1,CURRENT_TIMESTAMP; # 查詢，先記錄下原來時間 SELECT * FROM t; 
# 修改數據 UPDATE t SET a =2; # 如果修改為1，是不會改變時間的 SELECT * FROM t;
四、YEAR和TIME類型對比
- YEAR類型佔用1位元組，書中介紹定義時可以指定顯示的寬度為YEAR(2)或者YEAR(4)，不過在我的mysql版本5.7+驗證，發現只能定義為YEAR或者YEAR(4)，也就是新版是不支援YEAR(2)這種形式的
對於YEAR(4)，其顯示年份的範圍為1901~2155；然後超過這個範圍的話，mysql還可以寫，不過被賦值為0000 - TIME類型只佔3位元組，顯示的範圍為「-838:59:59」~"838:59:59"，TIME的小時是可以大於23也可以為負值的，因為time也可以用來保存時間間隔
五、日期時間函數
- NOW、CURRENT_TIMESTAMP和SYSDATE函數
介紹一下MySQL比較常用的NOW、CURRENT_TIMESTAMP和SYSDATE函數
給個例子，用sleep函數，然後對比sleep函數執行前後，這幾個函數獲取的時間對比
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),SLEEP(2),NOW(),CURRENT_TIMESTAMP(),SYSDATE();
從圖對比可知，NOW()其實就是CURRENT_TIMESTAMP()的近意函數，例子里使用了sleep(2)，隔2秒繼續執行，可以對比得知，NOW、CURRENT_TIMESTAMP其實獲取的都是整條sql開始執行的時間，不管在sleep函數執行前後，而SYSDATE獲取的其實執行sysdate這個函數時候的時間，並非整條sql開始執行的時間，所以在sleep函數執行前後獲取的時間是不同的
- DATE_ADD和DATE_SUB函數
DATE_ADD(date , INTERVAL expr type)和DATE_SUB(datte , INTTERVAL expr type)，expr可以為負數，所以DATE_ADD既可以用於日期相加，也可以用於日期相減。type可以為YEAR、MONTH、DAY、HOUR、MINUTE、SECOND
SELECT NOW()AS NOW,DATE_ADD(NOW(),INTERVAL 1 DAY) AS tomorrow,DATE_SUB(NOW(),INTERVAL 1 DAY);
備註：閏月問題，如果是閏月就返回29日，不是閏月就返回28日
SELECT DATE_ADD('2004-2-29',INTERVAL 1 YEAR); SELECT DATE_ADD('2004-2-29',INTERVAL 4 YEAR);
- DATE_FORMAT函數
DATE_FROMAT和Oracle中的to_char功能有點類型，作用是按照用戶定義的格式列印數據
SELECT DATE_FORMAT(NOW(),'%Y%m%d'); 
