MySQL Timestamp Automatic Update time

Usually there is a field in the table that creates date, and other databases have the option of default values. MySQL also has a default value of timestamp, but in MySQL, not only is the insertion of the modification will also update the value of Timestamp!

Thus, it is not the creation date, as the update date to use better!

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 the data column when creating new records and modifying existing records

2,timestamp DEFAULT Current_timestamp When you create a new record, take this

field is set to the current time, but when you modify it later, it is no longer refreshed

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 (one) not null, ' p_time ' timestamp not null DEFAULT current_timestamp on UPDATE current_timest AMP) 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 the current time, but not automatic update.

Table:

---------------------------------

Table Create Table

------ ---------------------------

CREATE TABLE ' t1 ' (' p_c ' int (one) 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 can not have two fields the default value is the current time, otherwise there will be an error. But the others can.

Table:

---------------------------------

Table Create Table

------ --------------------------

CREATE TABLE ' t1 ' (' p_c ' int (one) not null, ' p_time ' timestamp not null DEFAULT current_timestamp, ' p_timew2 ' timestamp NO T 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

By comparison, my statement is less "on update Current_timestamp" or more "default Current_timestamp". In this case, the timestamp field is only created when the data is insert, and the update is not changed. Of course, if that's what you're trying to accomplish, it doesn't matter.

1: If the default Current_timestamp and on UPDATE CURRENT_TIMESTAMP clauses are defined, the column value defaults to the current timestamp and is updated automatically.

2: If you do not use the default or ON UPDATE clause, it is equivalent to the default current_timestamp on update current_timestamp.

3: If there is only a default CURRENT_TIMESTAMP clause and no on UPDATE clause, the column value defaults to the current timestamp but does not update automatically.

4: If no default clause is used, but there is an on Update CURRENT_TIMESTAMP clause, 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 the value and update automatically, or either or none of them. (For example, you can specify automatic updates when you define them, but not initialize them.) The following field definitions illustrate these situations: