MySQL系列之日期和时间类型笔记
MySQL系列之日期和时间类型笔记
最近在看《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');