|
<h3 class="title">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?.
?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:
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:
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
