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

官方文档地址：11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME

---

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

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

• 插入记录时没有为自动初始化的列指定值，该列将会被设置为当前时间戳。
• 当行中任何其他列的值相对于当前值有更改时，自动更新的列将自动更新为当前时间戳。如果所有其他列都被设置为当前值，则自动更新的列保持不变。
  
  如果要防止自动更新的列在其他列更改时更新，请显式将其设置为当前值。如果其他列没有改变，但是想要更新自动更新的列，可以显式地将其设置为你想要更新的值（例如，将其设置为CURRENT_TIMESTAMP）。

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

要指定自动属性，在列定义中使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE 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'）。

注意

下面的示例使用DEFAULT 0，根据启用的是严格的 SQL 模式还是NO_ZERO_DATE SQL 模式，这个默认值可以产生警告或错误。注意，TRADITIONAL SQL 模式包括严格的模式和NO_ZERO_DATE。参见 5.1.11 服务器 SQL 模式。

TIMESTAMP 或 DATETIME 列可以同时指定有默认值和自动更新值，也可以只指定一个，或者两者都不指定。不同的列可以有不同的自动属性组合。下面的规则描述了这些可能性：

（1）对于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
);

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

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不允许为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 t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

如果启用了explicit_defaults_for_timestamp系统变量，则 TIMESTAMP 列只允许用NULL属性声明为NULL值。同样，TIMESTAMP 列也不允许为当前时间戳赋值NULL，不管是否使用NULL属性声明。要指定当前时间戳，请将列设置为CURRENT_TIMESTAMP或一个同义词，比如NOW()。