Rules for timestamp fields in MySQL

Some people say that the rules of the timestamp field are "weird ". The Manual describes the complex rules of timestamp fields in MySQL.

Divided into two parts

Define rules:

1. The timestamp field has three attributes:

A) whether to allow NULL. The default value is not null.

B) default value. It can be set to default CURRENT_TIMESTAMP or a constant of default. If it is defined as the default value, the default value of the first timestamp field is CURRENT_TIMESTAMP. For other timestamp fields, the default value is '2017-00-00 00:00:00 '. Default can be specified as null, provided that this field allows null.

For example, declare field2 timestamp null default null.

C) On update CURRENT_TIMESTAMP. Whether to automatically change the timestamp field to the current timestamp when updating other fields. The default value of the first timestamp field is On update CURRENT_TIMESTAMP.

2. default CURRENT_TIMESTAMP and On update CURRENT_TIMESTAMP can simultaneously modify a column. Any modified column of these two attributes is defined as timestamps_with_niladic. A table can only have one column defined as timestamps_with_niladic. If more than one table is created, table creation fails.

The preceding rules are used to define table creation behaviors. After creating a table, you can use show create table xxx to verify the preceding rules. With the table definition, let's look at the rules for using timestamp.

Rules

1. When the timestamp field is set to null in the insert or update statement

A) if the column is not null, it is set to the current timestamp.

B) If this column permits null, it is set to null.

2. If the default value is used in insert, (the value of this column is not specified in insert statements), it is equivalent to the default value. The specific value is determined by the default part.

3. If a column has the On update CURRENT_TIMESTAMP attribute, the value of this timestamp column is changed to the current timestamp when other columns of the row are modified.

After learning about the above rules, let's analyze your situation and see if it's not that "weird ".