Documentation Home
MySQL 8.0 参考手册  / 第 11 章数据类型  / 11.2 日期和时间数据类型  /  11.2.6 TIMESTAMP 和 DATETIME 的自动初始化和更新

11.2.6 TIMESTAMP 和 DATETIME 的自动初始化和更新

从 MySQL 5.6.5 开始,TIMESTAMPDATETIME可以自动初始化并更新为当前日期和时间(即当前时间戳)。在 5.6.5 之前,这仅适用于TIMESTAMP,并且每个表最多一TIMESTAMP列。以下说明首先介绍 MySQL 5.6.5 及以上版本的自动初始化和更新,然后介绍 5.6.5 之前版本的差异。

对于表中的任何TIMESTAMPDATETIME列,您可以将当前时间戳指定为默认值、自动更新值或两者:

  • 对于没有为该列指定值的插入行,自动初始化列设置为当前时间戳。

  • 当行中任何其他列的值从其当前值更改时,自动更新列将自动更新为当前时间戳。如果所有其他列都设置为其当前值,则自动更新的列将保持不变。要防止自动更新的列在其他列更改时更新,请将其显式设置为其当前值。要更新自动更新的列,即使其他列没有更改,请将其显式设置为应有的值(例如,将其设置为 CURRENT_TIMESTAMP)。

此外,如果 explicit_defaults_for_timestamp 系统变量被禁用,您可以通过为其分配一个值来将任何 TIMESTAMP(但不是 DATETIME)列初始化或更新为当前日期和时间NULL,除非它已被定义NULL为允许NULL值的属性。

要指定自动属性,请在列定义中使用DEFAULT CURRENT_TIMESTAMPand子句。ON UPDATE CURRENT_TIMESTAMP子句的顺序无关紧要。如果两者都存在于列定义中,则其中一个可以先出现。的任何同义词CURRENT_TIMESTAMP都与 具有相同的含义 CURRENT_TIMESTAMP。这些是 CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMPLOCALTIMESTAMP()

DEFAULT CURRENT_TIMESTAMPand 的 使用ON UPDATE CURRENT_TIMESTAMP特定于 TIMESTAMPand DATETIME。该 DEFAULT子句还可用于指定常量(非自动)默认值(例如, DEFAULT 0or DEFAULT '2000-01-01 00:00:00')。

笔记

以下示例使用DEFAULT 0, 一个默认值,它可以产生警告或错误,具体取决于是否 NO_ZERO_DATE启用了严格 SQL 模式或 SQL 模式。请注意, TRADITIONALSQL 模式包括严格模式和 NO_ZERO_DATE. 请参阅 第 5.1.10 节,“服务器 SQL 模式”

TIMESTAMPDATETIME列定义可以为默认值和自动更新值指定当前时间戳,为一个而不为另一个指定当前时间戳,或者两者都不指定。不同的列可以有不同的自动属性组合。以下规则描述了可能性:

  • 对于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
    );
  • 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
    );
  • 使用ON UPDATE CURRENT_TIMESTAMP 子句和常量DEFAULT子句,列会自动更新为当前时间戳并具有给定的常量默认值。

    CREATE TABLE t1 (
      ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
      dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
    );
  • 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
    );

    DATETIMENULL除非使用属性定义, 否则NOT NULL默认值为 0,在这种情况下默认值为 0。

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

TIMESTAMPDATETIME除非明确指定,否则列没有自动属性,但以下情况除外:如果 系统 explicit_defaults_for_timestamp 变量被禁用,则第一 TIMESTAMP列同时具有这两个 属性DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP如果两者都没有明确指定。要抑制第一 TIMESTAMP列的自动属性,请使用以下策略之一:

  • 启用 explicit_defaults_for_timestamp 系统变量。在这种情况下,指定自动初始化和更新的DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP子句可用,但不会分配给任何TIMESTAMP 列,除非明确包含在列定义中。

  • 或者,如果 explicit_defaults_for_timestamp 禁用,请执行以下任一操作:

    • DEFAULT 使用指定常量默认值 的子句定义列。

    • 指定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);

这些表具有以下属性:

  • 在每个表定义中,第一 TIMESTAMP列没有自动初始化或更新。

  • 这些表在ts1列处理NULL值的方式上有所不同。对于 t1, ts1is NOT NULL并为其分配一个值, NULL将其设置为当前时间戳。对于t2and t3ts1允许NULL并为其赋值 将其NULL设置为 NULL

  • t2t3的默认值不同ts1。对于 t2,ts1被定义为 permit NULL,因此默认也是 NULL在没有显式 DEFAULT子句的情况下。对于 t3ts1允许 NULL但具有明确的默认值 0。

如果TIMESTAMPDATETIME列定义在任何地方包含显式小数秒精度值,则必须在整个列定义中使用相同的值。这是允许的:

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

MySQL 5.6.5 之前的自动时间戳属性

在 MySQL 5.6.5 之前,对自动初始化和更新的支持比较有限:

  • DEFAULT CURRENT_TIMESTAMP并且ON UPDATE CURRENT_TIMESTAMP不能与 DATETIME列一起使用。

  • DEFAULT CURRENT_TIMESTAMP并且每个表ON UPDATE CURRENT_TIMESTAMP最多可以与一TIMESTAMP列一起使用。不可能让当前时间戳成为一列的默认值和另一列的自动更新值。

您可以选择是否使用这些属性以及 TIMESTAMP应该在哪一列中使用这些属性。它不必是自动初始化或更新为当前时间戳的表中的第一个。要为不同的 TIMESTAMP列指定自动初始化或更新,您必须抑制第一个列的自动属性,如前所述。然后,对于另一 TIMESTAMP列,DEFAULTON UPDATE 子句的规则与第一 TIMESTAMP列相同,只是如果省略这两个子句,则不会发生自动初始化或更新。

TIMESTAMP 初始化和 NULL 属性

如果 explicit_defaults_for_timestamp 系统变量被禁用, TIMESTAMP列默认为 NOT NULL, 不能包含 NULL值,并且分配 NULL分配当前时间戳。要允许TIMESTAMP列包含 ,请使用属性NULL显式声明它 。NULL在这种情况下,默认值也变为NULL除非被DEFAULT指定不同默认值的子句覆盖。DEFAULT NULL可用于明确指定NULL为默认值。(对于未用属性 TIMESTAMP 声明的列,无效。)如果 列允许 NULLDEFAULT NULLTIMESTAMPNULL值,分配 NULL将其设置为NULL,而不是当前时间戳。

下表包含多个 TIMESTAMP允许 NULL值的列:

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

TIMESTAMP允许 NULL值 的列在插入时 采用当前时间戳,除非在以下情况之一:

换句话说,TIMESTAMP 定义为允许NULL值的列只有在其定义包括以下内容时才会自动初始化 DEFAULT CURRENT_TIMESTAMP

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

如果TIMESTAMP列允许 NULL值但其定义不包括DEFAULT CURRENT_TIMESTAMP,则必须显式插入与当前日期和时间对应的值。假设表t1t2具有以下定义:

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);

TIMESTAMP在插入时将任一表中的列设置为当前时间戳,请显式为其分配该值。例如:

INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (NOW());

如果 explicit_defaults_for_timestamp 启用了系统变量,则 TIMESTAMP列 仅在使用属性NULL声明时才允许值 。NULL此外, TIMESTAMP列不允许分配NULL以分配当前时间戳,无论是用NULLor NOT NULL属性声明的。要分配当前时间戳,请将列设置为 CURRENT_TIMESTAMP或同义词,例如NOW()