mysql 表设计时的update_time自动更新

概述11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME原文地址:https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.htmlAs of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at 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, 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, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULLvalue, 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_TIMESTAMPclauses 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 asCURRENT_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 andDATETIME. 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'.NoteThe 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 5.1.7, “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 and ON 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 no ON 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 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_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 constant DEFAULT 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 no DEFAULT 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 the NULL attribute, in which case the default is NULL.CREATE TABLE t1 (ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL);DATETIME has a default of NULL unless defined with the NOT NULL attribute, in which case the default is 0.CREATE TABLE t1 (dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULLdt2 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: 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 TIMESTAMPcolumn, use one of these strategies:Enable the explicit_defaults_for_timestamp system variable.

<h3 >11.3.5 automatic Initialization and Updating for TIMESTAMP and DATETIME

原文地址:https://dev.MysqL.com/doc/refman/5.6/en/timestamp-initialization.HTML

As of MysqL 5.6.5,  and  columns can be automatically initializated and updated to the current date and time (that is,the current timestamp). Before 5.6.5,this is true only for ,and for at most one  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  or  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 ).

In addition,you can initialize or update any  column to the current date and time by assigning it a value,unless it has been defined with the  attribute to permit  values.

To specify automatic propertIEs,use the  and 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  have the same meaning as. These are , , , , ,and .

Use of  and  is specific to  and. The  clause also can be used to specify a constant (nonautomatic) default value; for example, or .

The following examples use ,a default that can produce warnings or errors depending on whether strict sql mode or the  sql mode is enabled. Be aware that the  sql mode includes strict mode and . See title="5.1.7 Server sql Modes" href="https://dev.MysqL.com/doc/refman/5.6/en/sql-mode.HTML">Section 5.1.7,“Server sql Modes”.

 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  and ,the column has the current timestamp for its default value and is automatically updated to the current timestamp.

With a  clause but no  clause,the column has the given default value and is not automatically updated to the current timestamp.
The default depends on whether the  clause specifIEs  or a constant value. With,the default is the current timestamp.
With a constant,the default is the given value. In this case,the column has no automatic propertIEs at all.
With an  clause and a constant  clause,the column is automatically updated to the current timestamp and has the given constant default value.
With an  clause but no  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.  has a default of 0 unless defined with the  attribute,in which case the default is .
 has a default of  unless defined with the  attribute,in which case the default is 0.
 columns have no automatic propertIEs unless they are specifIEd explicitly,with this exception: By default,the first  column has both  and  if neither is specifIEd explicitly. To suppress automatic propertIEs for the first column,use one of these strategIEs:
Enable the  system variable. If this variable is enabled,the  and  clauses that specify automatic initialization and updating are available,but are not assigned to any  column unless explicitly included in the column deFinition.
Alternatively,if  is Disabled (the default),do either of the following:
define the column with a  clause that specifIEs a constant default value.
Specify the  attribute. This also causes the column to permit  values,which means that you cannot assign the current timestamp by setting the column to . Assigning  sets the column to .
ConsIDer these table deFinitions:
The tables have these propertIEs:
In each table deFinition,the first  column has no automatic initialization or updating.
The tables differ in how the  column handles  values. For ,  is  and assigning it a value of sets it to the current timestamp. For  and ,  permits  and assigning it a value of  sets it to.
 and  differ in the default value for . For ,  is defined to permit ,so the default is also  in the absence of an explicit  clause. For ,  permits  but has an explicit default of 0.
If a  column deFinition includes an explicit fractional seconds precision value anywhere,the same value must be used throughout the column deFinition. This is permitted:
This is not permitted:
name="IDm140428260091232">automatic Timestamp PropertIEs Before MysqL 5.6.5
Before MysqL 5.6.5,support for automatic initialization and updating is more limited:
 and  cannot be used with  columns.
 and  can be used with at most one column per table. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
You can choose whether to use these propertIEs and which  column should have them. It need not be the first one in a table that is automatically initialized or updated to the current timestamp. To specify automatic initialization or updating for a different  column,you must suppress the automatic propertIEs for the first one,as prevIoUsly described. Then,for the other  and  clauses are the same as for the first TIMESTAMP Initialization and the NulL Attribute
By default,and TIMESTAMP Types" href="https://dev.MysqL.com/doc/refman/5.6/en/datetime.HTML"> columns are ,cannot contain  values,and assigning  assigns the current timestamp. To permit a  column to contain ,explicitly declare it with the  attribute. In this case,the default value also becomes  unless overrIDden with a  clause that specifIEs a different default value.  can be used to explicitly specify  as the default value. (For a column not declared with the  attribute,  is invalID.) If a  column permits values,assigning  sets it to ,not to the current timestamp.
The following table contains several  columns that permit  values:
A  column that permits  values does not take on the current timestamp at insert time except under one of the following conditions:
Its default value is defined as  and no value is specifIEd for the column
 or any of its synonyms such as  is explicitly inserted into the column
In other words,a  column defined to permit  values auto-initializes only if its deFinition includes:
If the  column permits  values but its deFinition does not include ,you must explicitly insert a value corresponding to the current date and time. Suppose that tables  and  have these deFinitions:
To set the  column in either table to the current timestamp at insert time,explicitly assign it that value. For example:
          总结       
以上是内存溢出为你收集整理的mysql 表设计时的update_time自动更新全部内容，希望文章能够帮你解决mysql 表设计时的update_time自动更新所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
原文地址:https://54852.com/sjk/1169166.html