MySQL timestamp automatic update Time Sharing

Generally, the table has a Create date Field, and other databases have default options. MySQL also has the default value timestamp. However, in MySQL, the value of timestamp is updated even if it is inserted or modified!

In this way, it is not the creation date. It is better to use it as an update date!

Therefore, to record the creation date in MySQL, you must use datetime and then use the NOW () function!

1, timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
This data column is refreshed when a new record is created and an existing record is modified.

2. timestamp default CURRENT_TIMESTAMP
The field is set to the current time, but will not be refreshed later

3. timestamp on update CURRENT_TIMESTAMP: set this field to 0 when creating a new record.

, Automatic UPDATE, and INSERT to the current time:
Table:
---------------------------------
Table Create Table
--------------------------------

Create table 't1' ('P _ C' int (11) not null, 'P _ time' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) ENGINE = InnoDB default charset = gb2312

Data:

1 11:53:35
2 2007-10-08 11:54:00

Insert into t1 (p_c) select 3; update t1 set p_c = 2 where p_c = 2;

Data:

1 2007-10-08 11:53:35
2 2007-10-08 12:00:37
3 2007-10-08 12:00:37

2. Automatic INSERT to the current time, but not automatic UPDATE.

Table:
---------------------------------
Table Create Table
---------------------------------

Create table 't1' ('P _ C' int (11) not null, 'P _ time' timestamp not null default CURRENT_TIMESTAMP) ENGINE = InnoDB default charset = gb2312

Data:

Insert into t1 (p_c) select 4; update t1 set p_c = 3 where p_c = 3;

1 2007-10-08 11:53:35
2 2007-10-08 12:00:37
3 2007-10-08 12:00:37
4 2007-10-08 12:05:19

3. A table cannot have two fields. The default value is the current time. Otherwise, an error occurs. But others can.
Table:
---------------------------------
Table Create Table
--------------------------------

Create table 't1' ('P _ C' int (11) not null, 'P _ time' timestamp not null default CURRENT_TIMESTAMP, 'P _ timew2 'timestamp not null default '2017-00-00 00:00:00') ENGINE = InnoDB default charset = gb2312

Data:
1 2007-10-08 11:53:35 0000-00-00 00:00:00
2 12:00:37 0000-00-00 00:00:00
3 2007-10-08 12:00:37 0000-00-00 00:00:00
4 12:05:19 0000-00-00 00:00:00

In comparison, my statement is missing "on update CURRENT_TIMESTAMP" or "default CURRENT_TIMESTAMP ". In this way, the timestamp field is only created at the time of data insert, and will not change during update. Of course, it doesn't matter if you want to achieve this goal.

1: if the DEFAULT CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP clauses are available at the time of definition, the column value is the current timestamp by DEFAULT 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 only the DEFAULT CURRENT_TIMESTAMP clause is used and the on update clause is not used, the column value is the current timestamp by DEFAULT, but is not automatically updated.

4: if the DEFAULT clause is not used, but the on update CURRENT_TIMESTAMP clause exists, the column defaults to 0 and is automatically updated.

5: if there is a constant value DEFAULT, this column will have a DEFAULT value and will not be automatically initialized as the current timestamp. If the column has an on update CURRENT_TIMESTAMP clause, the timestamp is automatically updated. Otherwise, the column has a default constant but will not be updated automatically.

In other words, you can use the current timestamp to initialize values and automatically update values, or either of them. (For example, you can specify automatic update during definition, but it is not initialized .) The following field definitions illustrate these situations: