MySQL :: MySQL 9.0 参考手册 :: 13.2.5 TIMESTAMP 和 DATETIME 的自动初始化和更新

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / TIMESTAMP 和 DATETIME 的自动初始化和更新

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

TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间（即当前时间戳）。

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

对于插入的行，如果未指定列的值，则自动初始化的列将设置为当前时间戳。
当行中任何其他列的值从其当前值更改时，自动更新的列将自动更新为当前时间戳。如果所有其他列都设置为其当前值，则自动更新的列将保持不变。若要防止自动更新的列在其他列更改时更新，请将其显式设置为其当前值。若要即使其他列未更改也更新自动更新的列，请将其显式设置为其应有的值（例如，将其设置为 CURRENT_TIMESTAMP）。

此外，如果 explicit_defaults_for_timestamp 系统变量被禁用，则您可以通过为其分配 NULL 值来初始化或更新任何 TIMESTAMP（但不是 DATETIME）列，除非已使用 NULL 属性将其定义为允许 NULL 值。

若要指定自动属性，请在列定义中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。子句的顺序无关紧要。如果列定义中同时存在两个子句，则它们可以按任意顺序出现。CURRENT_TIMESTAMP 的任何同义词都具有与 CURRENT_TIMESTAMP 相同的含义。这些是 CURRENT_TIMESTAMP()、NOW()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP 和 LOCALTIMESTAMP()。

DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 的使用特定于 TIMESTAMP 和 DATETIME。 DEFAULT 子句还可以用于指定常量（非自动）默认值（例如，DEFAULT 0 或 DEFAULT '2000-01-01 00:00:00'）。

注意

以下示例使用 DEFAULT 0，这是一种默认值，可能会产生警告或错误，具体取决于是否启用了严格 SQL 模式或 NO_ZERO_DATE SQL 模式。请注意，TRADITIONAL SQL 模式包含严格模式和 NO_ZERO_DATE。请参见第 7.1.11 节，“服务器 SQL 模式”。

TIMESTAMP 或 DATETIME 列定义可以为默认值和自动更新值指定当前时间戳，可以为其中一个指定，但不能为另一个指定，也可以为两者都不指定。不同的列可以具有不同的自动属性组合。以下规则描述了各种可能性。

使用 DEFAULT CURRENT_TIMESTAMP 和 ON 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
);
```
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
);
```

TIMESTAMP 和 DATETIME 列没有自动属性，除非显式指定，但有一个例外：如果 explicit_defaults_for_timestamp 系统变量被禁用，则第一个 TIMESTAMP 列将同时具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，即使两者都没有显式指定。若要抑制第一个 TIMESTAMP 列的自动属性，请使用以下策略之一。

启用 explicit_defaults_for_timestamp 系统变量。在这种情况下，DEFAULT CURRENT_TIMESTAMP 和 ON 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，ts1为NOT NULL，并且为其分配NULL值会将其设置为当前时间戳。对于t2和t3，ts1允许NULL，并且为其分配NULL值会将其设置为NULL。
t2和t3在ts1的默认值上有所不同。对于t2，ts1被定义为允许NULL，因此在没有显式DEFAULT子句的情况下，默认值也是NULL。对于t3，ts1允许NULL，但具有显式默认值 0。

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

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

TIMESTAMP 初始化和 NULL 属性

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

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

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

允许NULL值的TIMESTAMP列不会在插入时获取当前时间戳，除非满足以下条件之一

其默认值定义为CURRENT_TIMESTAMP，并且没有为该列指定任何值
CURRENT_TIMESTAMP或其任何同义词，如NOW()，被显式插入到该列中

换句话说，定义为允许NULL值的TIMESTAMP列仅在其定义包含DEFAULT CURRENT_TIMESTAMP时才会自动初始化

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

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

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以分配当前时间戳，无论是否使用NULL或NOT NULL属性声明。要分配当前时间戳，请将列设置为CURRENT_TIMESTAMP或其同义词，如NOW()。

PREV HOME UP NEXT