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

最新推荐文章于 2024-08-08 05:13:37 发布

assember

最新推荐文章于 2024-08-08 05:13:37 发布

阅读量1.3w

点赞数 6

分类专栏： MySQL

原文链接：https://dev.mysql.com/doc/refman/8.0/en/datetime.html

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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

摘要由CSDN通过智能技术生成

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’。