|
<h3 class="title">11.3.5Automatic 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,andcolumns 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 onecolumn 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 anyorcolumn 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 anycolumn to the current date and time by assigning it avalue,unless it has been defined with theattribute to permitvalues.
To specify automatic properties,use theandclauses 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 forhave the same meaning as. These are,,,,,and.
Use ofandis specific toand. Theclause 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 theSQL mode is enabled. Be aware that theSQL 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 bothand,the column has the current timestamp for its default value and is automatically updated to the current timestamp.
With aclause but noclause,the column has the given default value and is not automatically updated to the current timestamp.
The default depends on whether theclause specifiesor 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 anclause and a constantclause,the column is automatically updated to the current timestamp and has the given constant default value.
With anclause but noclause,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 theattribute,in which case the default is.
has a default ofunless defined with theattribute,in which case the default is 0.
columns have no automatic properties unless they are specified explicitly,with this exception: By default,thefirstcolumn has bothandif neither is specified explicitly. To suppress automatic properties for the firstcolumn,use one of these strategies:
Enable thesystem variable. If this variable is enabled,theandclauses that specify automatic initialization and updating are available,but are not assigned to anycolumn unless explicitly included in the column definition.
Alternatively,ifis disabled (the default),do either of the following:
Define the column with aclause that specifies a constant default value.
Specify theattribute. This also causes the column to permitvalues,which means that you cannot assign the current timestamp by setting the column to. Assigningsets the column to.
Consider these table definitions:
The tables have these properties:
In each table definition,the firstcolumn has no automatic initialization or updating.
The tables differ in how thecolumn handlesvalues. For,isand assigning it a value ofsets it to the current timestamp. Forand,permitsand assigning it a value ofsets it to.
（编辑：安卓应用网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
