MySQL timestamp type auto update

Problem description

Two fields of type timestamp are defined in a table,

Create_time TIMESTAMP NOT NULL COMMENT 'create time', update_time TIMESTAMP NOT NULL COMMENT 'update time'

When the new record is inserted, the current time value is given to both create_time and update_time without any problems. When updating the record, only update_time is updated in the code, and the result create_time is automatically updated to the current time.

Get to the bottom of

In the CREATE TABLE statement, the definition of the first occurrence of a timestamp type field will have the following conditions:
1. DEFAULT CURRENT_TIMESTAMP column value for the current timestamp but not automatically update;
2. CURRENT_TIMESTAMP ON UPDATE DEFAULT CURRENT_TIMESTAMP, said the column value for the current timestamp and is automatically updated every update the record will automatically update the column value for the current time; 3.
without the use of DEFAULT CURRENT_TIMESTAMP or DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP CURRENT_TIMESTAMP ON, it defaults to the DEFAULT
4. UPDATE CURRENT_TIMESTAMP; do not use DEFAULT CURRENT_TIMESTAMP, and the use of ON UPDATE CURRENT_TIMESTAMP, the column value is 0 by default and automatically update;

Second the timestamp type field, if you do not use DEFAULT CURRENT_TIMESTAMP or DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE which use the default CURRENT_TIMESTAMP.

For the use of DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP defines the column, it should be noted that if the field value is not changed, will not be updated and defined for multiple use DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, MySQL only updates the first use of the definition of the column.