MySQL timestamp automatically updates time sharing

Typically there will be one Create date creation date field in the table, and all other databases have the option of default values. MySQL also has a default value of timestamp, but in MySQL, the value of timestamp is updated as well as inserted and even modified!

Instead of creating the date, use 1 as the update date!

So in MySQL to record the creation date you have to use datetime and then use the NOW() function to do that!

1, TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
This data column is refreshed both when a new record is created and when an existing record is modified

2, TIMESTAMP DEFAULT CURRENT_TIMESTAMP when creating a new record
The field is set to the current time, but it is not refreshed when modified later

3, TIMESTAMP ON UPDATE CURRENT_TIMESTAMP sets 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 2007-10-08 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 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. You cannot have two fields in a table whose default value is the current time; otherwise, an error will occur. But the others will do.
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 '0000-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 2007-10-08 12:00:37 0000-00-00 00:00:00
3 2007-10-08 12:00:37 0000-00-00 00:00:00
4 2007-10-08 12:05:19 0000-00-00 00:00:00

In comparison, my statement is less "on update CURRENT_TIMESTAMP" or more "default CURRENT_TIMESTAMP". In this way, the timestamp field is only established at the time of the data insert, while the update field is not changed. Of course, it doesn't matter if that's what you want

1: if both the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses are present at the time of definition, the column value will default to the current timestamp and will be updated automatically.

2: if the DEFAULT or ON UPDATE clause is not used, then it is equivalent to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

3: if there is only DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column value defaults to the current timestamp but is not automatically updated.

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

5: if there is a constant value 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 automatically updated, otherwise the column has a default constant but is not automatically updated.

In other words, you can use the current timestamp to initialize and automatically update, or one of them, or neither. (for example, you can specify automatic updates at definition time, but do not initialize them.) The following field definitions illustrate these situations: