MySQL table design-time Update_time Automatic Update

11.3.5 Automatic initialization and Updating for TIMESTAMP and DATETIME

Original address: https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can is automatically initializated and updated to the current date and time (th At are, the current timestamp). Before 5.6.5, this is true if only TIMESTAMP for, and for at the most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for Versions preceding 5.6.5.

For Any timestamp  or datetime   column in a table, can assign the timestamp as the default value, the Auto-update value, or both:

• An auto-initialized column was set to the current timestamp for inserted Rows that specify no value for the column.

• An auto-updated column was automatically updated to the current timestamp when the value of any oth Er column in the row was changed from its current value. An auto-updated column remains unchanged if any other columns is set to their the current values. To prevent a auto-updated column from the updating when the other columns the change, explicitly set it to its current value. To update a auto-updated column even when other columns does not change, explicitly set it to the value it should has (for example, set it To current_timestamp ).

In addition, can initialize or update any timestamp  column to the current D Ate and time by assigning it a null value, unless it had 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 is present in a column definition, either can occur first. Any of the synonyms For current_timestamp  have the same meaning Ascurrent_timestamp . These are current_timestamp () ,  now () ,  localtime ,  localtime () ,  localtimestamp , And localtimestamp () .

Use of and are specific to and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP TIMESTAMP DATETIME . DEFAULTThe clause also can be used to specify a constant (nonautomatic) default value; For example, DEFAULT 0 or DEFAULT ‘2000-01-01 00:00:00‘ .

Note

The following examples use DEFAULT 0 , a default if can produce warnings or errors depending on whether strict SQL mode or th E NO_ZERO_DATE -SQL mode is enabled. Be aware that the TRADITIONAL SQL mode includes strict mode and NO_ZERO_DATE . See section 5.1.7, "Server SQL Modes".

TIMESTAMPor 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 ON UPDATE CURRENT_TIMESTAMP and, the column have the current timestamp for it 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 no  on UPDATE current_timestamp  clause, the column has the given default value and are not AU Tomatically updated to the current timestamp.

  The default depends on whether The default  clause Specifies current_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_timest AMP); 

  With a constant, the default is the given value. The column has no automatic properties at all.

  create TABLE T1 (ts TIMESTAMP default 0, dt DATETIME default 0); 

• ON UPDATE CURRENT_TIMESTAMPwith a clause and a constant DEFAULT clause, the column was automatically updated to the current timestamp and have the Given constant default value.

  CREATE TABLE T1 (  ts TIMESTAMP default 0 on update current_timestamp,  DT DATETIME default 0 on update current_time STAMP);

• ON UPDATE CURRENT_TIMESTAMP DEFAULT with a clause but no clause, the column was automatically updated to the current timestamp and does not having T The He current timestamp to its default value.

  The default in this case is type dependent. Have TIMESTAMP a default of 0 unless defined NULL with the attribute, in which case the default is NULL .

  CREATE TABLE T1 (  ts1 TIMESTAMP on update current_timestamp,     --default 0  ts2 TIMESTAMP NULL on Update current _timestamp--default NULL);

  DATETIMEHave a default NULL of unless defined NOT NULL with the 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 is specified explicitly, with this exception:by default, 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 strate Gies:

• Enable the explicit_defaults_for_timestamp   System variable. If This variable is enabled, The default current_timestamp  and on UPDATE current_timestamp  clauses that specify automatic initialization and updating is Available, but is not assigned to Any timestamp  column unless explicitly Included in the column definition.

• Alternatively, if is explicit_defaults_for_timestamp disabled (the default), 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 permit NULL values, which means so you cannot assign the current timestamp by setting th e column to NULL . Assigning NULL sets the column to NULL .

Consider these table definitions:

CREATE TABLE T1 (  ts1 TIMESTAMP default 0,  ts2 TIMESTAMP default current_timestamp on                UPDATE Current_timestam P); 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_time STAMP);

The tables has these properties:

• In each table definition, the first TIMESTAMP column has no automatic initialization or updating.

• The tables differ in how The ts1  column handles  null  values. For t1 ,  ts1  is  not NULL  and Assigning it a value Ofnull  sets it to the current timestamp. For t2  and t3 ,  ts1  permits null  and assigning it a value Of null  sets it tonull .

• t2And t3 differ in the default value for ts1 . t2for, was ts1 defined to permit NULL , so the default was also in the absence of a NULL explicit DEFAULT clause. t3for, permits but have an ts1 NULL explicit default of 0.

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value m UST 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));

Automatic Timestamp Properties before MySQL 5.6.5

Before MySQL 5.6.5, support for automatic initialization and updating are more limited:

• default current_timestamp   And on UPDATE current_timestamp  cannot be used with  datetime  columns.

• default current_timestamp  and  on UPDATE current_timestamp  can be used with at most one  TIMESTAMP column per table. It is not a possible to having the current timestamp being the default value for one column and the Auto-update value for another Column.

You can choose whether to use these properties and which TIMESTAMP column should has them. It need not being the first one in a table, is automatically initialized, or updated to the current timestamp. To specify automatic initialization or updating for a different TIMESTAMP column, you must suppress the automatic properties F Or the first one, as previously described. Then, for the other TIMESTAMP column, the rules for the DEFAULT and ON UPDATE clauses is the same TIMESTAMP as for the first column, ex Cept that if you omit both clauses, no automatic initialization or updating occurs.

TIMESTAMP initialization and the NULL Attribute

By default,TIMESTAMPColumns isNOT NULL, cannot containNULLvalues, and assigningNULLAssigns the current timestamp. To permit aTIMESTAMPColumn to containNULL, explicitly declare it with theNULLAttribute. In this case, the default value also becomesNULLUnless overridden with aDEFAULTClause that specifies a different default value.DEFAULT NULLCan is used to explicitly specifyNULLAs the default value. (For aTIMESTAMPColumn not declared with theNULLAttributeDEFAULT NULLis invalid.) If ATIMESTAMPColumn PermitsNULLvalues, assigningNULLSets it toNULL, 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 CURR Ent_timestamp);

A column that permits values does don't take on the current TIMESTAMP NULL timestamp at insert time except under O NE of the following conditions:

• Its default value is defined as and CURRENT_TIMESTAMP no value are specified for the column

• CURRENT_TIMESTAMPor any of its synonyms such as are NOW() explicitly inserted into the column

In other words, a- TIMESTAMP column defined to permit NULL values auto-initializes only if it definition includes DEFAULT CURRENT_TIMESTAMP :

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT current_timestamp);

IF TIMESTAMP The column permits NULL values but it definition does not include DEFAULT CURRENT_TIMESTAMP , you must explicitly insert a value cor Responding to the current date and time. Suppose that tables and has t1 t2 these definitions:

CREATE TABLE T1 (ts TIMESTAMP NULL DEFAULT ' 0000-00-00 00:00:00 '); CREATE TABLE T2 (ts TIMESTAMP null DEFAULT null);

TIMESTAMPto set the column in either table to the current timestamp at insert time, explicitly assign it, the value. For example:

INSERT into T1 values (now ()), insert into T2 values (CURRENT_TIMESTAMP);

MySQL table design-time Update_time Automatic Update