设置TIMESTAMP和DATETIME的自动初始化及自动更新

佚名 6年前 (2018-06-13) Mysql 2146人围观抢沙发百度已收录

最近有一个关于MySQL版本升级的事，涉及到一些关于时间类型的细节问题需要查明，因此到官网找到相关文章，翻出来比较方便自己理解，博客这里也贴一下。

参考官网网址：

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

一、简介 自MySQL 5.6.5开始TIMESTAMP和DATETIME类型可以实现自动初始化或更新为CURRENT_TIMESTAMP的功能，在5.6.5之前这个特性只有TIMESTAMP才能用。鉴于现在时间类型基本都用DATETIME（因为TIMESTAMP的范围很小局限性也很大），因此5.6.5之后DATETIME也实现了这个功能。这个功能详细描述下就是：

DATETIME也可以像TIMESTAMP一样将CURRENT_TIMESTAMP设为默认值
如果你为此时间列设置了自动更新的属性，那么只要一条记录的其他任何列值发生改变，时间列都会自动更新为CURRENT_TIMESTAMP。

假如你不想让设置自动更新属性的时间列随其他列值的改变而改变，你可以为他显式的赋一个值。 TIMESTAMP类型：

在8.0.2版本之前，如果你定义了这样一个列：ts timestamp [not null] default xxx,那么除非你显式的给此列赋一个非NULL的值，否则一律插入的当前时间戳,无论你的default值设置的是什么，你可以将其视为一个BUG。你可以通过将explicit_defaults_for_timestamp设置为ON来避免这个现象。
在8.0.2版本之前，如果你定义了一个ts timestamp [not null]列，但没有设置默认值，那么实质上这个列是ts timestamp not null default current_timestamp on update current_timestamp，同样的你可以通过设置explicit_defaults_for_timestamp为ON来解决此问题。
当explicit_defaults_for_timestamp为OFF时，除非指定timestamp可为NULL，否则MySQL自动为timestamp设置not null属性。

在5.6.5之前，以上timestamp的表现可以算是他相比datetime的一个优势，也有人把它视为一个BUG认为他很麻烦，但无论如何在5.6.5之前如果你有将CURRENT_TIMESTAMP设为默认值的需求，那你只能选择timestamp类型，好在5.6.5之后datetime也支持了。 关于explicit_defaults_for_timestamp参数： 在MySQL5.6.6引入了explicit_defaults_for_timestamp参数来解决以上timestamp默认值的问题（此参数在8.0.1之前的版本默认是OFF，只读参数），而在MySQL8.0.2之后此参数默认值变为ON而且可以在线修改。将此参数设为ON后你为timestamp列设置的默认值就可以生效了。从这里可以看出explicit_defaults_for_timestamp=on的作用就是，使得你自己为timestamp列设置的default值生效，当你未设置默认值和自更新时MySQL也不会自作主张的为not null时间列添加current_timestamp和on update current_timestamp的默认选项。此外此参数在8.0.11中提示已经要逐步弃用了。 二、如何设置时间列（本文如无版本号提示，示例统一是MySQL5.6的环境） 如果你要设置一个自动初始化+自动更新为CURRENT_TIMESTAMP的时间列（TIMESTAMP or DATETIME），那么使用“DEFAULT CURRENT_TIMESTAMP”+“ON UPDATE CURRENT_TIMESTAMP”的组合，定义的顺序无所谓，可替代CURRENT_TIMESTAMP的关键字有：CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, 以及LOCALTIMESTAMP()。官网详细的列出了可能出现的默认值+自动更新选项的组合： 1）

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

2）

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

3）

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

4）自更新的timestamp列如果未设置默认值且未明确指定可为NULL，则默认值为0，0代表0000-00-00 00:00:00。　

--当然想要设置包含0值的时间列你还需要修改下默认的SQL_MODE，去除NO_ZERO_IN_DATE和NO_ZERO_DATE项。
CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
CREATE TABLE t1 (
dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL
dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);
--自更新的DATETIME列如果未设置默认值且定义了not null，那么默认值为0。

5）

CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);

对于本次创建的3个表：

3个表的第一个timestamp列都没有自动初始化和自动更新的属性。（自动初始化是指调用的系统时间函数）
3个表的差别在于ts1列对NULL值的处理，t1.ts1非空，当你为他赋值为NULL时插入的其实是当前时间戳，t2.ts1,t3.ts1允许null值，因此当你插入NULL时最终被插入的就是NULL。
t2和t3对于ts1的默认值处理也不一样，t2.ts1允许null值且未定义默认值，因此默认值就是NULL，t3.ts1允许null值且定义了default 0,因此默认值就是0。