13.2.5 TIMESTAMP 和 DATETIME 的自动初始化和更新
TIMESTAMP
and DATETIME
columns can be automatically initialized and updated to the current date and time (that is, the current timestamp).
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:
-
An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.
-
An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to
CURRENT_TIMESTAMP
).
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.
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()
.
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'
).
The following examples use DEFAULT 0
, a default that can produce warnings or errors depending on whether strict SQL mode or the NO_ZERO_DATE
SQL mode is enabled. Be aware that the TRADITIONAL
SQL mode includes strict mode and NO_ZERO_DATE
. See Section 7.1.11, “Server SQL Modes”.
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:
-
With both
DEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
, the column has the current timestamp for its default value and is automatically updated to the current timestamp.CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
-
With a
DEFAULT
clause but noON UPDATE CURRENT_TIMESTAMP
clause, the column has the given default value and is not automatically updated to the current timestamp.The default depends on whether the
DEFAULT
clause specifiesCURRENT_TIMESTAMP
or a constant value. WithCURRENT_TIMESTAMP
, the default is the current timestamp.CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP );
With a constant, the default is the given value. In this case, the column has no automatic properties at all.
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0, dt DATETIME DEFAULT 0 );
-
With an
ON UPDATE CURRENT_TIMESTAMP
clause and a constantDEFAULT
clause, the column is automatically updated to the current timestamp and has the given constant default value.CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP );
-
With an
ON UPDATE CURRENT_TIMESTAMP
clause but noDEFAULT
clause, the column is automatically updated to the current timestamp but does not have the current timestamp for its default value.The default in this case is type dependent.
TIMESTAMP
has a default of 0 unless defined with theNULL
attribute, in which case the default isNULL
.CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL );
DATETIME
has a default ofNULL
unless defined with theNOT NULL
attribute, in which case the default is 0.CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0 );
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:
-
Enable the
explicit_defaults_for_timestamp
system variable. In this case, theDEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
clauses that specify automatic initialization and updating are available, but are not assigned to anyTIMESTAMP
column unless explicitly included in the column definition. -
Alternatively, if
explicit_defaults_for_timestamp
is disabled, do either of the following:-
Define the column with a
DEFAULT
clause that specifies a constant default value. -
Specify the
NULL
attribute. This also causes the column to permitNULL
values, which means that you cannot assign the current timestamp by setting the column toNULL
. AssigningNULL
sets the column toNULL
, not the current timestamp. To assign the current timestamp, set the column toCURRENT_TIMESTAMP
or a synonym such asNOW()
.
-
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:
-
In each table definition, the first
TIMESTAMP
column has no automatic initialization or updating. -
The tables differ in how the
ts1
column handlesNULL
values. Fort1
,ts1
isNOT NULL
and assigning it a value ofNULL
sets it to the current timestamp. Fort2
andt3
,ts1
permitsNULL
and assigning it a value ofNULL
sets it toNULL
. -
t2
andt3
differ in the default value forts1
. Fort2
,ts1
is defined to permitNULL
, so the default is alsoNULL
in the absence of an explicitDEFAULT
clause. Fort3
,ts1
permitsNULL
but has an explicit default of 0.
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)
);
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.
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
);
A TIMESTAMP
column that permits NULL
values does not take on the current timestamp at insert time except under one of the following conditions:
-
Its default value is defined as
CURRENT_TIMESTAMP
and no value is specified for the column -
CURRENT_TIMESTAMP
or any of its synonyms such asNOW()
is explicitly inserted into the column
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);
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);
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());
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()
.