Besides having a “Create date” field in a typical table, other databases provide default value options. MySQL also has a default value timestamp, but in MySQL, not only inserts but also updates will refresh the timestamp value!
As a result, it is no longer a creation date and is better suited as an update date!
Therefore, to record a creation date in MySQL, you have to use datetime and then use the NOW() function!
1. TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Refreshes this data column both when creating new records and modifying existing records.
2. TIMESTAMP DEFAULT CURRENT_TIMESTAMP sets this
field to the current time when creating a new record, but no longer refreshes it upon subsequent modifications.
3. TIMESTAMP ON UPDATE CURRENT_TIMESTAMP sets this field to 0 when creating a new record.
Auto 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. Auto INSERT to current time, but no auto 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 with default values of the current time, otherwise an error will occur. However, other configurations are possible.
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 was missing “on update CURRENT_TIMESTAMP” or had an extra “default CURRENT_TIMESTAMP”. This way, the timestamp field is only set when data is inserted, and does not change upon updates. Of course, if that is exactly what you intend, it is fine.
1: If both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses are used in the definition, the column value defaults to using the current timestamp and is automatically updated.
2: If neither the DEFAULT nor the ON UPDATE clause is used, it is equivalent to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
3: If only the DEFAULT CURRENT_TIMESTAMP clause is present without the ON UPDATE clause, the column value defaults to the current timestamp but is not automatically updated.
4: If no DEFAULT clause is 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 DEFAULT value, the column will have that default value and will not be automatically initialized to the current timestamp. If this column also has an ON UPDATE CURRENT_TIMESTAMP clause, the timestamp is automatically updated; otherwise, the column has a default constant but will not be automatically updated.
In other words, you can use the current timestamp for initialization, automatic update, either one, or neither. (For example, you can specify automatic updates in the definition but not initialization.) The following field definitions illustrate these scenarios: