MySQL timestamp type auto-update problem "Go"

A table was built today with a column that is the timestamp type, and I meant that when the data is updated, the time of this field will be updated automatically. Cruised does not know much about this type of value, causing an error. This field is found to have a value only when the data is set up, but it is not changed in the update.

Find the information, found that I built the statement of the table has a problem:

Here is the code snippet:

CREATE TABLE ' Test ' (
' T1 ' timestamp not NULL default Current_timestamp,
' WW ' varchar (5) Not NULL
) Engine=myisam;

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

Here is the code snippet:

CREATE TABLE ' Test ' (
' T1 ' TIMESTAMP not NULL,
' WW ' VARCHAR (5) Not NULL
) ENGINE = MYISAM

CREATE TABLE ' Test ' (
' T1 ' timestamp not NULL the default current_timestamp on update Current_timestamp,
' WW ' varchar (5) Not NULL
) Engine=myisam;

In comparison, my statement is less "on update Current_timestamp" or more "default Current_timestamp". As a result, this timestamp field is only set at the time of the data insert, and the update will not change. Of course, it doesn't matter if you want to achieve that.

Find the English Manual (http://dev.mysql.com/doc/refman/5.1/en/timestamp.html) for a more detailed explanation, translated as follows:

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

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

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

2: If the default or on UPDATE clause is not used, it is equivalent to default Current_timestamp on update current_timestamp.

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

4: If the default clause is not 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 of 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 updated automatically, otherwise the column has a default constant but does not update automatically.

In other words, you can use the current timestamp to initialize values and Automatic Updates, or either, or none. (For example, you can specify an automatic update when you define it, but not initialize it.) The following field definitions illustrate these conditions:

Here is the code snippet:

Automatic initialization and update:
TS TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp

Automatic initialization only:
TS TIMESTAMP DEFAULT Current_timestamp

Auto Update 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 "1th timestamp column" In red font, because a table with multiple timestamp columns is defined differently.

For example, the second of the 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:

Here is the code snippet:

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 will record the current time, T2 as the default (0000-00-00 00:00:00), equivalent to the following statement:

Here is the code snippet:

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:

Here is the code snippet:

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: #1293-incorrect table definition; There can is only one TIMESTAMP column with Current_timestamp in DEFAULT or on UPDATE clause

I have wanted to design a table, this table has two timestamp columns, one can record the update time, one can record the initial time, but after trying many times, 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 function can use DateTime to realize the time of the initialization of the record, just need to specify when the insert.

MySQL timestamp type auto-update problem "Go"