MySQL's timestamp type automatic update

Today I built a table with a column of timestamp type, and I meant that when the data was updated, the time of the field was automatically updated. Cruised is not very familiar with the value of this type, resulting in an error. Find that this field is only available when this line of data is set up, and the update is unchanged.

Looking for information, I found that the statement of the table I created a problem:

The following is a code fragment:

The code is as follows	Copy Code
CREATE TABLE ' Test ' ( ' T1 ' timestamp not NULL default Current_timestamp, ' WW ' varchar (5) Not NULL ) Engine=myisam;

In fact, the following two table statements have the same effect:

The following is a code fragment:

The code is as follows	Copy Code
CREATE TABLE ' Test ' ( ' T1 ' TIMESTAMP not NULL, ' WW ' VARCHAR (5) Not NULL ) ENGINE = MYISAM CREATE TABLE ' Test ' ( ' T1 ' timestamp not NULL default current_timestamp on Update Current_timestamp, ' WW ' varchar (5) Not NULL ) Engine=myisam;

By comparison, my statement is less "on update Current_timestamp" or more "default Current_timestamp". In this case, the timestamp field is only created when the data is insert, and the update is not changed. Of course, it doesn't matter if you just want to achieve this goal.

Find English Handbook (http://dev.mysql.com/doc/refman/5.1/en/timestamp.html), get more detailed explanation, translate as follows:

-----------------------------------Translation begins--------------------------------

In the CREATE TABLE statement, the 1th timestamp column can be declared in any of the following ways:

1: If the default Current_timestamp and on UPDATE CURRENT_TIMESTAMP clauses are defined, the column value defaults to the current timestamp and is updated automatically.

2: If you do not use the default or ON UPDATE clause, it is equivalent to the default current_timestamp on update current_timestamp.

3: If there is only a default CURRENT_TIMESTAMP clause and no on UPDATE clause, the column value defaults to the current timestamp but does not update automatically.

4: If no default clause is used, but there is an on Update CURRENT_TIMESTAMP clause, the column defaults to 0 and is automatically updated.

5: If there is a constant value default, the column will have a default value and will not be automatically initialized to the current timestamp. If the column also has an ON Update CURRENT_TIMESTAMP clause, the timestamp is automatically updated, otherwise the column has a default constant but is not automatically updated.

In other words, you can use the current timestamp to initialize the value and update automatically, or either or none of them. (For example, you can specify automatic updates when you define them, but not initialize them.) The following field definitions illustrate these situations:

The following is a code fragment:

The code is as follows	Copy Code
To initialize and update automatically: TS TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp Automatically initialize only: TS TIMESTAMP DEFAULT Current_timestamp Automatic Updates only TS TIMESTAMP DEFAULT 0 on UPDATE current_timestamp Just give a constant (note: 0000-00-00 00:00:00) TS TIMESTAMP DEFAULT 0

End of-----------------------------------translation--------------------------------

Above I marked the "1th timestamp column" With a red font, because a table with multiple timestamp columns is defined differently.

For example, the second article above: "If you do not use the default or ON UPDATE clause, it is equivalent to the default current_timestamp on update current_timestamp." "If the second timestamp column is different, see:

The following is a code fragment:

The code is as follows	Copy Code
CREATE TABLE ' Test ' ( ' WW ' VARCHAR (9) Not NULL, ' T1 ' TIMESTAMP not NULL DEFAULT Current_timestamp, ' T2 ' TIMESTAMP not NULL ) ENGINE = MYISAM

In this case, when the data is inserted, T1 records the current time, T2 as the default (0000-00-00 00:00:00), equivalent to the following statement:

The following is a code fragment:

The code is as follows	Copy Code
CREATE TABLE ' Test ' ( ' WW ' VARCHAR (9) Not NULL, ' T1 ' TIMESTAMP not NULL DEFAULT Current_timestamp, ' T2 ' TIMESTAMP not NULL DEFAULT ' 0000-00-00 00:00:00 ' ) ENGINE = MYISAM

And according to the second article above, it should be:

The following is a code fragment:

The code is as follows	Copy Code
CREATE TABLE ' Test ' ( ' WW ' VARCHAR (9) Not NULL, ' T1 ' TIMESTAMP not NULL DEFAULT Current_timestamp, ' T2 ' TIMESTAMP on UPDATE current_timestamp not NULL DEFAULT current_timestamp ) ENGINE = MYISAM

But in fact, the above statement is a syntax error, MySQL will return:

The following is a reference fragment:

The code is as follows	Copy Code
#1293-incorrect table definition; There can be only one TIMESTAMP column with Current_timestamp in DEFAULT or on UPDATE clause

I used to want to design a table, this table has two timestamp columns, one can record the update time, one can record the initial time, but after many attempts, I found that MySQL does not seem to be able to do this, do not know whether this is a MySQL defect or self optimization, because, This feature can be used to record initialization time using datetime, just specify when inserts are required.