Movatterモバイル変換

MySQL 9.4 Reference Manual

13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME

TIMESTAMP andDATETIME columns can be automatically initialized and updated to the current date and time (that is, the current timestamp).

For anyTIMESTAMP orDATETIME 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 toCURRENT_TIMESTAMP).

In addition, if theexplicit_defaults_for_timestamp system variable is disabled, you can initialize or update anyTIMESTAMP (but notDATETIME) column to the current date and time by assigning it aNULL value, unless it has been defined with theNULL attribute to permitNULL values.

To specify automatic properties, use theDEFAULT CURRENT_TIMESTAMP andON 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 forCURRENT_TIMESTAMP have the same meaning asCURRENT_TIMESTAMP. These areCURRENT_TIMESTAMP(),NOW(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP, andLOCALTIMESTAMP().

Use ofDEFAULT CURRENT_TIMESTAMP andON UPDATE CURRENT_TIMESTAMP is specific toTIMESTAMP andDATETIME. TheDEFAULT clause also can be used to specify a constant (nonautomatic) default value (for example,DEFAULT 0 orDEFAULT '2000-01-01 00:00:00').

Note

The following examples useDEFAULT 0, a default that can produce warnings or errors depending on whether strict SQL mode or theNO_ZERO_DATE SQL mode is enabled. Be aware that theTRADITIONAL SQL mode includes strict mode andNO_ZERO_DATE. SeeSection 7.1.11, “Server SQL Modes”.

TIMESTAMP orDATETIME 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 bothDEFAULT 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 aDEFAULT 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 theDEFAULT 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 anON 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 anON 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 andDATETIME columns have no automatic properties unless they are specified explicitly, with this exception: If theexplicit_defaults_for_timestamp system variable is disabled, thefirstTIMESTAMP column has bothDEFAULT CURRENT_TIMESTAMP andON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly. To suppress automatic properties for the firstTIMESTAMP column, use one of these strategies:

Enable theexplicit_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, ifexplicit_defaults_for_timestamp is disabled, do either of the following:
- Define the column with aDEFAULT clause that specifies a constant default value.
- Specify theNULL 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 firstTIMESTAMP column has no automatic initialization or updating.
The tables differ in how thets1 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 aTIMESTAMP orDATETIME 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 Initialization and the NULL Attribute

If theexplicit_defaults_for_timestamp system variable is disabled,TIMESTAMP columns by default areNOT NULL, cannot containNULL values, and assigningNULL assigns the current timestamp. To permit aTIMESTAMP column to containNULL, explicitly declare it with theNULL attribute. In this case, the default value also becomesNULL unless overridden with aDEFAULT clause that specifies a different default value.DEFAULT NULL can be used to explicitly specifyNULL as the default value. (For aTIMESTAMP column not declared with theNULL attribute,DEFAULT NULL is invalid.) If aTIMESTAMP column permitsNULL values, assigningNULL sets it toNULL, not to the current timestamp.

The following table contains severalTIMESTAMP columns that permitNULL values:

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

ATIMESTAMP column that permitsNULL values doesnot take on the current timestamp at insert time except under one of the following conditions:

Its default value is defined asCURRENT_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, aTIMESTAMP column defined to permitNULL values auto-initializes only if its definition includesDEFAULT CURRENT_TIMESTAMP:

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

If theTIMESTAMP column permitsNULL values but its definition does not includeDEFAULT CURRENT_TIMESTAMP, you must explicitly insert a value corresponding to the current date and time. Suppose that tablest1 andt2 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 theTIMESTAMP 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 theexplicit_defaults_for_timestamp system variable is enabled,TIMESTAMP columns permitNULL values only if declared with theNULL attribute. Also,TIMESTAMP columns do not permit assigningNULL to assign the current timestamp, whether declared with theNULL orNOT NULL attribute. To assign the current timestamp, set the column toCURRENT_TIMESTAMP or a synonym such asNOW().

PREV HOME UP NEXT