故此5.6.5以往DATETIME也落成了那一个效应

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

参考官网网址:

一、简介

自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_故此5.6.5以往DATETIME也落成了那一个效应。timestamp on update
    current_timestamp,同样的你可以通过设置explicit_defaults_for_timestamp为ON来解决此问题。
  • 当explicit_defaults_for_timestamp为OFF时,除非指定timestamp可为NULL,否则MySQL自动为timestamp设置not
    null属性。

故此5.6.5以往DATETIME也落成了那一个效应。在5.6.5之前,以上timestamp的表现可以算是他相比datetime的一个优势,也有人把它视为一个BUG认为他很麻烦,但无论如何在5.6.5之前如果你有将CURRENT_TIMESTAMP设为默认值的需求,那你只能选择timestamp类型,好在5.6.5之后datetime也支持了。

关于explicit_defaults_for_timestamp参数:

在MySQL5.6.6引入了explicit_故此5.6.5以往DATETIME也落成了那一个效应。defaults_for_timestamp参数来解决以上timestamp默认值的问题(此参数在8.0.1之前的版本默认是OFF,只读参数),而在MySQL8.0.2之后此参数默认值变为ON而且可以在线修改。将此参数设为ON后你为timestamp列设置的默认值就可以生效了。

故此5.6.5以往DATETIME也落成了那一个效应。从这里可以看出explicit_defaults_故此5.6.5以往DATETIME也落成了那一个效应。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。

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。

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图