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

2023-08-04 06:33| 来源: 网络整理| 查看: 265

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位）精度，都支持自动初始化和更新，这些会在之后详细解释。

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

不同点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:00TIMESTAMP2019-01-01 11:00:00

当切换回UTC+8：00时：

时间类型日期时间DATETIME2019-01-01 12:00:00TIMESTAMP2019-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的自动初始化和更新

【本文地址】

公司简介

联系我们