MySQL教程 2023-02-26

MySQL Variables explicit_defaults_for_timestamp 数据库参数变量解释及正确配置使用

本站中文解释

explicit_defaults_for_timestamp是MySQL中的一个参数变量，当该变量设置为ON时，服务器将在创建表时指定TIMESTAMP字段的默认值；当该变量设置为OFF时，服务器将不再为 TIMESTAMP 字段指定任何默认值，转而根据 SQL 标准做出相应的行为：在插入批量数据前，没有提供该字段的值，则设其字段值为零值（0），而在插入批量数据中某条数据的某字段值没有提供，则设其字段值为该条数据前面一条数据的值。

要设置该变量，需在MySQL服务启动前，可以通过命令行里的–explict_defaults_for_timestamp参数，或者在MySQL的配置文件中添加：explict_defaults_for_timestamp=0/1 来设置，0是OFF，1是ON。

官方英文解释

explicit_defaults_for_timestamp

Command-Line Format	`--explicit-defaults-for-timestamp[={OFF\|ON}]`
Deprecated	Yes
System Variable	`explicit_defaults_for_timestamp`
Scope	Global, Session
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

This system variable determines whether the server enables
certain nonstandard behaviors for default values and
NULL-value handling in
TIMESTAMP columns. By default,
explicit_defaults_for_timestamp
is disabled, which enables the nonstandard behaviors.

If
explicit_defaults_for_timestamp
is disabled, the server enables the nonstandard behaviors and
handles TIMESTAMP columns as
follows:

TIMESTAMP columns not
explicitly declared with the NULL
attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value
of NULL is permitted and sets the
column to the current timestamp.
The first TIMESTAMP column
in a table, if not explicitly declared with the
NULL attribute or an explicit
DEFAULT or ON UPDATE
attribute, is automatically declared with the
DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP attributes.
TIMESTAMP columns following
the first one, if not explicitly declared with the
NULL attribute or an explicit
DEFAULT attribute, are automatically
declared as DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp).
For inserted rows that specify no explicit value for such
a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Depending on whether strict SQL mode or the
NO_ZERO_DATE SQL mode is
enabled, a default value of '0000-00-00 00:00:00' may be invalid. Be aware that the
TRADITIONAL SQL mode
includes strict mode and
NO_ZERO_DATE. See
Section 5.1.10, “Server SQL Modes”.

The nonstandard behaviors just described are deprecated;
expect them to be removed in a future release of MySQL.

If
explicit_defaults_for_timestamp
is enabled, the server disables the nonstandard behaviors and
handles TIMESTAMP columns as
follows:

It is not possible to assign a
TIMESTAMP column a value of
NULL to set it to the current
timestamp. To assign the current timestamp, set the column
to CURRENT_TIMESTAMP or a
synonym such as NOW().
TIMESTAMP columns not
explicitly declared with the NOT NULL
attribute are automatically declared with the
NULL attribute and permit
NULL values. Assigning such a column a
value of NULL sets it to
NULL, not the current timestamp.
TIMESTAMP columns declared
with the NOT NULL attribute do not
permit NULL values. For inserts that
specify NULL for such a column, the
result is either an error for a single-row insert if
strict SQL mode is enabled, or '0000-00-00 00:00:00' is inserted for multiple-row inserts
with strict SQL mode disabled. In no case does assigning
the column a value of NULL set it to
the current timestamp.
TIMESTAMP columns
explicitly declared with the NOT NULL
attribute and without an explicit
DEFAULT attribute are treated as having
no default value. For inserted rows that specify no
explicit value for such a column, the result depends on
the SQL mode. If strict SQL mode is enabled, an error
occurs. If strict SQL mode is not enabled, the column is
declared with the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar
to how MySQL treats other temporal types such as
DATETIME.
No TIMESTAMP column is
automatically declared with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. Those attributes
must be explicitly specified.
The first TIMESTAMP column
in a table is not handled differently from
TIMESTAMP columns following
the first one.

If
explicit_defaults_for_timestamp
is disabled at server startup, this warning appears in the
error log:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).

As indicated by the warning, to disable the deprecated
nonstandard behaviors, enable the
explicit_defaults_for_timestamp
system variable at server startup.

Note

explicit_defaults_for_timestamp
is itself deprecated because its only purpose is to permit
control over deprecated
TIMESTAMP behaviors that are
to be removed in a future release of MySQL. When removal of
those behaviors occurs,
explicit_defaults_for_timestamp
no longer has any purpose, and you can expect it to be
removed as well.

For additional information, see
Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.