How to automatically update time with timestamp in MySQL database

The timestamp data type in MySQL is a special data type. It can automatically update the time as long as you update the record timestamp without using a program to update.
Usually there will be a Create date field in the table. Other databases have default value options. MySQL also has a default value timestamp, but in MySQL, not only insertion but also modification will update the timestamp value!
In this case, it is no longer the creation date. It is better to use it as the update date!
So to record the creation date in MySQL, you have to use datetime and then use the NOW() function to complete!
1, TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Refresh this data column when creating a new record and modifying an existing record
2, TIMESTAMP DEFAULT CURRENT_TIMESTAMP Set this
field to the current time when creating a new record, but in the future When modifying, it will no longer be refreshed
3, TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Set this field to 0 when creating a new record
, automatically 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. Automatically INSERT to the current time, but does not automatically 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. There cannot be two fields in a table whose default value is the current time, otherwise it will Something went wrong. But others are fine.
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 lacks "on update CURRENT_TIMESTAMP" or adds "default CURRENT_TIMESTAMP". In this way, the timestamp field only establishes the time when the data is inserted, and there will be no change when it is updated. Of course, it doesn't matter if you just want to achieve this goal. 1: If both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses are defined when defining, the column value will use the current timestamp by default and be automatically updated.
2: If 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 the DEFAULT CURRENT_TIMESTAMP clause but 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 present, 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, this 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 the value and auto-update, or one of them, or neither. (For example, you can specify automatic update when defining, but not initialize.) The following field definition illustrates these situations:

The above is the content of the method of automatically updating the timestamp time in the MySQL database. Please pay attention to more related content. PHP Chinese website (www.php.cn)!