MySQL教程

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”.


数据运维技术 » MySQL Variables explicit_defaults_for_timestamp 数据库 参数变量解释及正确配置使用
分享到：

相关推荐