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

2023-05-16

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

DATE日期类型
DATETIME和TIMESTAMP的不同
- 什么是时区自动变动？
DATETIME和TIMESTAMP的相同点
- 微秒小数部分
- 自动初始化和更新
- 宽松的日期格式识别
其他规则
- 两位数年份规则
- 无效的日期输入值
总结

DATE日期类型

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

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

DATETIME和TIMESTAMP的不同

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

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

不同点	DATETIEM	TIMESTAMP
取值范围	‘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时区时：

时间类型	日期时间
DATETIME	2019-01-01 12:00:00
TIMESTAMP	2019-01-01 11:00:00

当切换回UTC+8：00时：

时间类型	日期时间
DATETIME	2019-01-01 12:00:00
TIMESTAMP	2019-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_TIMESTAMP、CURRENT_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的自动初始化和更新

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)