MySQL8.0下DATE,DATETIME和TIMESTAMP的自动初始化和更新

这篇文章介绍了MySQL8.0中DATE、DATETIME和TIMESTAMP的区别与相似点,强调了自动初始化和更新特性。在8.0版本后,三者都支持自动初始化,但TIMESTAMP具有时区自动变动特性。文章还提到,可以使用TIMESTAMP处理跨时区业务,避免数据库时区配置带来的复杂性。
摘要由CSDN通过智能技术生成

DATE日期类型

简单的来说,DATE表示的是没有时间的日期,以’YYYY-MM-DD’格式检索和显示值 。
而DATETIME和TIMESTAMP都是表示日期和时间,以’YYYY-MM-DD hh:mm:ss’格式检索和显示值。

相对来说,DATE类型很简单,下面主要解释下DATETIME和TIMESTAMP的相同点和不同点。

DATETIME和TIMESTAMP的不同

两种时间类型都可以表示日期和时间,都能保留到微妙(6位)精度,都支持自动初始化和更新,这些会在之后详细解释。

它们的不同点如下表所示:

不同点DATETIEMTIMESTAMP
取值范围‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’‘1970-01-01 00:00:01’~‘2038-01-19 03:14:07’
是否支持时区自动变动不支持支持UTC时区自动变动

在之前的版本中TIMESTAMP相交DATETIME来说还可以自动初始化和更新,但是在8.0之后TIMESTAMP和DATETIME都支持了自动初始化和更新。所以比较重要的区别就在于是否支持时区自动变动。

什么是时区自动变动?

假设当前为UTC+8:00时区,分别以DATETIME和TIMESTAMP存入时间为2019-01-01 12:00:00
当时区切换为UTC+7:00时区时:

时间类型日期时间
DATETIME2019-01-01 12:00:00
TIMESTAMP2019-01-01 11:00:00

当切换回UTC+8:00时:

时间类型日期时间
DATETIME2019-01-01 12:00:00
TIMESTAMP2019-01-01 12:00:00

DATETIME和TIMESTAMP的相同点

微秒小数部分

DATETIME或TIMESTAMP 值可以包括高达微秒(6位)精度的尾随小数秒部分。特别是,插入到一个DATETIME或一 TIMESTAMP列中的值中的任何小数部分都被存储而不是被丢弃。与包括分数部分,这些类型的格式为:
DATETIME:   ‘1000-01-01 00:00:00.000000’~‘9999-12-31 23:59:59.999999’
TIMESTAMP:‘1970-01-01 00:00:01.000000’~‘2038-01-19 03:14:07.999999’

自动初始化和更新

TIMESTAMP和 DATETIME列可以自动初始化并更新为当前日期和时间(即当前时间戳)。
并且可以分别设定是否具有默认值和是否会被自动更新值。
我们可以通过以下代码来实现自动初始化默认值和自动更新值

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

其中,DEFAULT 为设置自动初始化默认值,可以为常量定值,也可以使用CURRENT_TIMESTAMP设置为当前时间戳。
ON UPDATE CURRENT_TIMESTAMP为设置自动更新当前时间戳。
两个配置语句前后顺序无关紧要。

在之前的版本中,TIMESTAMP可以通过为他们赋值NULL而实现自动更新时间戳,在8.0版本之后,默认情况下,会直接赋值为NULL,如果设置为NOT NULL属性,也会进行报错。
如果需要兼容之前版本的自动更新,可以将explicit_defaults_for_timestamp禁用,才可以使用非标准行为,但是非标准行为,会在未来版本的MySQL中弃用。

NOTE: CURRENT_TIMESTAMP 直接写在 SQL 中 IDEA会提示

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

翻译过来就是,CURRENT_TIMESTAMPCURRENT_TIMESTAMP()NOW()是相同的

所以可以用 NOW()来替换上述SQL 中的CURRENT_TIMESTAMP
具体可以参看这篇博客:一文看懂mysql时间函数now()、current_timestamp() 和sysdate()

宽松的日期格式识别

作为字母’YYYY-MM-DD hh:mm:ss’或’YY-MM-DD hh:mm:ss’格式的字符串 。允许使用 “ 宽松 ”语法:
任何标点符号都可以用作日期部分之间的分隔符。例如,‘2012-12-31 11:30:45’, ‘2012^12^31 11+30+45’, ‘2012/12/31 113045’,和 '2012@12@31 11^30^45’是相等的。

作为字符串中没有分隔符 'YYYYMMDDhhmmss’或 'YYMMDDhhmmss’格式的字符串,前提是该字符串作为日期有意义。例如, '20070523091528’并且 '070523091528’被解释为 ‘2007-05-23 09:15:28’,但是 ‘071122129015’非法(它具有无意义的微小部分)并且变为’0000-00-00 00:00:00’。

作为数字 YYYYMMDDhhmmss或 YYMMDDhhmmss格式的数字,只要该数字作为日期有意义。例如, 19830905132800并被 830905132800解释为 ‘1983-09-05 13:28:00’。

其他规则

两位数年份规则

如果日期年份输入只有两位数,那么MySQL会按照以下规则解释两位数的年份:

  • 范围00-69中的年份值将转换为2000-2069
  • 范围70-99中的年份值将转换为1970-1999

无效的日期输入值

无效的DATE, DATETIME或 TIMESTAMP值将转换为适当类型(或)的 “ 零 ”值。 ‘0000-00-00’‘0000-00-00 00:00:00’

总结

  • 尽量不要使用timestamp,因为只支持到2038年,今年已经2020年了,只有18年的寿命
  • 最近项目中涉及到跨时区的业务处理,所以如果需要考虑前后端时区一致问题,建议使用datatime ,把所有的时区问题交由代码和前后端管理,而不是通过配置数据库时区来完成时区的管理。

本文根据官方文档整理和总结而成,更为详细的内容请参考MySQL的官方文档:
MySQL 8.0参考手册 / DATE,DATETIME和TIMESTAMP类型
MySQL 8.0参考手册 / TIMESTAMP和DATETIME的自动初始化和更新

  • 5
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值