MySQL timestamp type auto-update problem

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 for default current_timestamp,  ' ww ' varchar (5) NOT null) Engine=innodb D Efault charset=utf8mb4;

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

CREATE TABLE ' test ' (    ' t1 ' TIMESTAMP not null,    ' WW ' VARCHAR (5) is not null) Engine=innodb DEFAULT charset=utf8mb4; CREATE TABLE ' test ' (      ' t1 ' timestamp not NULL default current_timestamp on update current_timestamp,       ' ww ' varchar ( 5) not NULL) Engine=innodb DEFAULT charset=utf8mb4;

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.

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

• If the default Current_timestamp and on UPDATE CURRENT_TIMESTAMP clauses are defined, the value of the column is to use the current timestamp, and is automatically updated;
• If you do not use the default or ON UPDATE clause, it is equivalent to default current_timestamp on update current_timestamp;
• 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;
• 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;
• If there is a constant value of default, the column has a default value and is not 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:

Automatic initialization and update: ' ts ' timestamp default current_timestamp on UPDATE current_timestamp only auto-initialize: ' ts ' timestamp default Current_time Stamp only automatically updates ' ts ' TIMESTAMP DEFAULT 0 on update current_timestamp just gives a constant (note: 0000-00-00 00:00:00) ' ts ' Timestampdefault 0

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

Example:

CREATE TABLE ' test ' (  ' ww ' VARCHAR (9) Not NULL,  ' t1 ' TIMESTAMP not null DEFAULT current_timestamp,  ' T2 ' TI Mestamp 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:

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

According to the above considerations, the complete statement of the example's creation statement should be:

CREATE TABLE ' test ' (  ' ww ' VARCHAR (9) Not NULL,  ' t1 ' TIMESTAMP not null DEFAULT current_timestamp,  ' T2 ' T Imestamp on UPDATE current_timestamp not NULL DEFAULT current_timestamp) ENGINE = MYISAM

Once 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