TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前的日期和时间（即当前时间戳）。TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (that is, the current timestamp).

对于表中的任何 TIMESTAMP 或 DATETIME 列，你可以将当前时间戳指定为默认值、自动更新值，或者两者兼有：For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

此外，如果explicit_defaults_for_timestamp系统变量被禁用，您可以通过赋值为NULL来初始化或更新任何TIMESTAMP（但不能是DATETIME）列为当前日期和时间，除非它已被定义为允许NULL值的NULL属性。In addition, if the explicit_defaults_for_timestamp system variable is disabled, you can initialize or update any TIMESTAMP (but not DATETIME) column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

要指定自动属性，请在列定义中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。子句的顺序无关紧要。如果在列定义中同时存在这两个子句，则任意一个可以先出现。CURRENT_TIMESTAMP 的任何同义词都与 CURRENT_TIMESTAMP 含义相同。这些同义词包括 CURRENT_TIMESTAMP()、NOW()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP 和 LOCALTIMESTAMP()。To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 是特定于 TIMESTAMP 和 DATETIME 的。DEFAULT 子句也可用于指定常量（非自动）默认值（例如，DEFAULT 0 或 DEFAULT '2000-01-01 00:00:00'）。Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP and DATETIME. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value (for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00').

TIMESTAMP 或 DATETIME 列定义可以指定当前时间戳作为默认值和自动更新值的一种、另一种或两者都不是。不同的列可以具有不同的自动属性组合。以下规则描述了这些可能性：TIMESTAMP or DATETIME column definitions can specify the current timestamp for both the default and auto-update values, for one but not the other, or for neither. Different columns can have different combinations of automatic properties. The following rules describe the possibilities:

TIMESTAMP 和 DATETIME 列没有自动属性，除非它们被显式指定，唯一的例外是：如果 explicit_defaults_for_timestamp 系统变量被禁用，第一个 TIMESTAMP 列在未显式指定的情况下会具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP。要抑制第一个 TIMESTAMP 列的自动属性，可使用以下策略之一：TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly. To suppress automatic properties for the first TIMESTAMP column, use one of these strategies:

考虑以下表定义：Consider these table definitions:

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

这些表具有以下属性：The tables have these properties:

如果一个 TIMESTAMP 或 DATETIME 列定义在任何地方包含明确的小数秒精度值，则整个列定义中必须使用相同的值。这是允许的：If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition. This is permitted:

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

这是不允许的：This is not permitted:

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

TIMESTAMP 初始化与 NULL 属性TIMESTAMP Initialization and the NULL Attribute

如果 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，而不是当前时间戳。If the explicit_defaults_for_timestamp system variable is disabled, TIMESTAMP columns by default are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL, explicitly declare it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is invalid.) If a TIMESTAMP column permits NULL values, assigning NULL sets it to NULL, not to the current timestamp.

下表包含几个允许 NULL 值的 TIMESTAMP 列：The following table contains several TIMESTAMP columns that permit NULL values:

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

允许 NULL 值的 TIMESTAMP 列 不会 在插入时使用当前时间戳，除非满足以下条件之一：A TIMESTAMP column that permits NULL values does not take on the current timestamp at insert time except under one of the following conditions:

换句话说，定义为允许 NULL 值的 TIMESTAMP 列仅在其定义中包含 DEFAULT CURRENT_TIMESTAMP 时才会自动初始化：In other words, a TIMESTAMP column defined to permit NULL values auto-initializes only if its definition includes DEFAULT CURRENT_TIMESTAMP:

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

如果 TIMESTAMP 列允许 NULL 值，但其定义中未包含 DEFAULT CURRENT_TIMESTAMP，则必须显式插入与当前日期和时间对应的值。假设表 t1 和 t2 拥有以下定义：If the TIMESTAMP column permits NULL values but its definition does not include DEFAULT CURRENT_TIMESTAMP, you must explicitly insert a value corresponding to the current date and time. Suppose that tables t1 and t2 have these definitions:

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

要在插入时将任一表中的 TIMESTAMP 列设置为当前时间戳，需显式地将其赋值为该值。例如：To set the TIMESTAMP column in either table to the current timestamp at insert time, explicitly assign it that value. For example:

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()。If the explicit_defaults_for_timestamp system variable is enabled, TIMESTAMP columns permit NULL values only if declared with the NULL attribute. Also, TIMESTAMP columns do not permit assigning NULL to assign the current timestamp, whether declared with the NULL or NOT NULL attribute. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().