mysql中的date、datetime、timestamp你还不知道怎么使用吗
一、引言
在mysql中表示日期和时间的数据类型,大体上有下面几种,
名称 |
定义方式 |
格式 |
范围 |
date |
date |
YYYY-MM-DD |
'1000-01-01' to '9999-12-31' |
time |
time[.fraction] |
hh:mm:ss[.000000] |
'-838:59:59.000000' to '838:59:59.000000' |
datetime |
datetime[.fraction] |
YYYY-MM-DD hh:mm:ss[.000000] |
'1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
timestamp |
timestamp[.fraction] |
YYYY-MM-DD hh:mm:ss[.000000] |
'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC |
year |
year |
YYYY |
'1901' to '2155' |
二、日期/时间类型
1、date
date仅包含日期,mysql存储和显示的格式是'YYYY-MM-DD',可以表示的范围是'1000-01-01' to '9999-12-31'
2、datetime
datetime包含日期、时间两部分,mysql存储和显示的格式是'YYYY-MM-DD hh:mm:ss',可以表示的范围是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
3、timestamp
timestamp包含日期、时间两部分,在存储时存储的是时间戳,可以表示的范围是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC,这里需要注意timestamp是有时区概念的,该类型可跟随时区变化,默认情况下是服务器的时区,如果一个数据库连接设置了时区参数,那么该类型的值会根据连接设置的时区来存储和返回值。
在前面说到在存储时存储的是时间戳,也就是从1970-01-01 00:00:00 UTC到要存储的时间所相差的毫秒数。关于UTC是世界协调是,感兴趣的可以自己查阅资料。
-
上面介绍了date、datetime、timestamp这三种数据类型,还有一个点需要着重强调,datetime、timestamp是可以有小数位的,一共最多可以有6位,所以在定义是可以是datetime(3),要说明的是默认情况下是datetime(0),由于是0所以通常情况下就倍省略了; -
timestamp的范围刚才也看到了,最大到'2038-01-19 03:14:07' UTC,所以很多情况下最好不用该类型,避免超过最大范围产生问题,可以datetime来代替; -
datetime、tiemstap类型有自动初始话和更新为当前的日期和时间的功能;
三、实践
create table test(
id int not null auto_increment,
t_date date default null,
t_date_time datetime not null default current_timestamp,
t_timestamp timestamp not null default current_timestamp on update current_timestamp,
t_date_time2 datetime(3) not null default current_timestamp(3),
primary key(id)
);
可以看到t_date_time是datetime类型,默认是自动插入当前日期+时间
t_timestamp是timestamp类型,默认自动插入当前日期+时间,且更新是更新为当前日期和时间
t_date_time2是datetime(3)类型,默认自动插入当前日期+时间,且current_timestamp(3),
下面插入数据,
可以看到t_date_time、t_timestamp、t_date_time2都是可以自动赋值为当前日期+时间(我这里系统时间就是2022-05-01,大家不用误会),下面修改下,
可以看到t_timestamp的时间已经自动更新为当前'2022-05-01 06:37:32'了。很奇怪吧。
四、总结
本文主要分享了,mysql中常见的日期、时间数据类型,下面几条需谨记,