1. 首页
  2. mysql基础

07-七、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 中 )

不使用 datedatetimetimestamp 三个类型的另外一个非重要原因,是一直记不住如何设置 datetimetimestamp 的默认值。

一般情况下,我在创建表结构的时候都会添加 created_atupdated_at 作为记录创建时间和记录最近更新时间,这两个时间都很好理解,如果使用 datetimetimestamp 则可以设置一些约束在 insert 数据和 update 数据时自动填充其值。

但是,我一贯的用法,就是把它们定义为一个整数类型 ( int ),然后使用应用程序级别的时间戳填充,其实,这是很简单愚蠢的。

MySQL 可以帮我们做这些事情,前提就是使用 datetimetimestamp 两个类型。

timestampdatetime 列可以自动初始化并更新为当前日期和时间 ( 即当前时间戳 ) 。

对于 MySQL 表中的任何 timestampdatetime 列,我们可以将设置当前时间戳为默认值,自动更新值或两者皆有:

1、如果将某个列设置为插入时自动初始化为当前时间戳,那么就可以不用为这些列指定任何值。
2、当行中任何其他列的值发生变更时,设置了自动更新的列将自动更新为当前时间戳。

当然了,这里有一个要点,如果 update 时任何其它列的数据并没有发生变更,那么自动更新列保持不变。

如果要更新自动更新的列,即使其他列未更改,则需要显式设置值为应具有的值,例如使用 CURRENT_TIMESTAMP()

如果要防止在其他列更改时更新自动更新列,则需要显式的设置自动更新列的值为当前值

除了以上这些规则,如果禁用 ( disabled ) 了 explicit_defaults_for_timestamp 系统变量,则可以通过为其指定 NULL 值来初始化或更新任何 timestamp(但不是 datetime )列到当前日期和时间,除非已使用 NULL 属性定义允许 NULL 值

正如上一章节中所提到的那样,要指定自动属性,可以在创建表时在列定义中使用 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 两个约束,约束的顺序无关紧要。

如果这两个约束同时存在于某个列定义中,那么任何一个都可能先执行。

CURRENT_TIMESTAMP 可以使用任何同义词代替,任何 CURRENT_TIMESTAMP 的任何同义词与 CURRENT_TIMESTAMP 具有相同的含义,例如 CURRENT_TIMESTAMP()NOW()LOCALTIMELOCALTIME()LOCALTIMESTAMPLOCALTIMESTAMP()

当在 datetime 和 timestamp 列上使用了 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 约束时,其实还是可以添加 DEFAULT 约束的,default 约束用于指定常量 (非自动 ) 默认值;例如 DEFAULT 0DEFAULT '2018-09-14 00:00:00'

下面的示例中可能会使用到 default 0 ,这是一个默认值,但可以产生警告或错误,具体取决于是否启用了严格的 SQL 模式或 NO_ZERO_DATE SQL 模式。

timestamp 和 datetime 列可以同时指定默认值和自动更新值为当前时间戳,也可以值指定其中一个,或者两个都不指定。不同的列可以具有不同的自动属性组合。下面的规则则描述了一些可能的使用场景

1、同时指定 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP,那么该列会自动设置当前时间为默认值,并自动更新为当前时间戳


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

2、使用了 default 约束,当没有使用 ON UPDATE CURRENT_TIMESTAMP 约束时,该列会自动使用给定的默认值,但不会自动更新为当前时间戳

而默认的值取决于 default 子句是指定 CURRENT_TIMESTAMP 还是常量值。

如果使用 CURRENT_TIMESTAMP,默认值是当前时间戳


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、如果使用了 ON UPDATE CURRENT_TIMESTAMP 约束和常量 default 约束,该列将自动更新为当前时间戳并具有给定的常量默认值


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

4、如果使用了 ON UPDATE CURRENT_TIMESTAMP 约束当没有使用 default 约束,该列将自动更新为当前时间戳,且不会使用当前时间戳作为其默认值

这种情况下的默认值取决于所使用的类型。

timestamp 类型的默认值为 0,除非使用了 NULL 约束,那么默认值就是 NULL


CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL );

datetime 类型的默认值为 NULL ,除非使用 NOT NULL 约束,那么默认值就是 0


CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0 );

需要注意的是,datetimetimstamp 默认是么有任何自动属性的,如果需要自动属性需要显式的指明,但有一个例外情况:

「 如果禁用了系统变量 explicit_defaults_for_timestamp ,且没有任何一个 timestamp 类型的列明确的设置了自动属性,那么第一个 timestamp 类型的列会自动设置为 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP

如果要禁止第一个 timestamp 列自动设置 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 属性,有以下几种方法

  • 启用 explicit_defaults_for_timestamp 系统变量。

在这种情况下,可以为 timestamp 列设置自动初始化和自动更新的 DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP,但需要明确指明,否则任何 timetamp 都不会获得这些自动属性
* 或者,保持禁用 explicit_defaults_for_timestamp ,但通过以下方法来改变:

  • 使用 default 约束为 timestamp 指定一个默认的常量值。
  • 使用 null 约束。

但因为使用了 NULL 约束,导致列允许 NULL 值,也就意味着无法通过将列设置为 NULL 来分配当前时间戳。

使用 NULL 约束会将列设置为 NULL,而不是当前时间戳,如果要分配当前时间戳,则需要将列设置为 CURRENT_TIMESTAMP 或同义词,例如NOW()

比如下面这个表结构定义


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);

这些表 t1、t2、t3 具有以下特征

  • 每个表结构定义中,第一个 TIMESTAMP 列没有设置自动初始化或自动更新。
  • 这些表的不同之处在于 ts1 列如何处理 NULL 值

  • 对于 t1,ts1 为 NOT NULL,并为其赋值为 NULL,这会将其值设置为当前时间戳

  • 对于 t2 和 t3 ,ts1 允许 NULL,并为其赋值 NULL
  • t2 和 t3 在 ts1 的默认值上有所不同。

  • 对于 t2,ts1 被定义为允许 NULL,因此在没有显式 DEFAULT 子句的情况下,缺省值也为 NULL

  • 对于 t3,ts1 允许 NULL 但显式默认值为 0

对于 datetime 和 timestamp 列,无论在任何位置定义了小数位数,则必须在整个列定义中使用相同的值,例如


CREATE TABLE t1 ( ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) );

但下面这种是不允许的


CREATE TABLE t1 ( ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3) );

希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:

  1. 关注我们的 GitHub 博客,让我们成为长期关系
  2. 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
  3. 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
  4. JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。

    本文著作权归作者所有,如若转载,请注明出处

    转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com

    标题:07-七、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 中 )

    链接:https://www.javajike.com/article/1623.html

« 08-八、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 下 )
06-六、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 上 )»

相关推荐

QR code