If you are a senior MySQL developers, you have already known there's at most one column can use CURRENT_TIMESTAMP as the default value.
For example, I tried to add another timestamp column creation with default CURRENT_TIMESTAMP into a table.
mysql> alter table table_name modify column creation timestamp not null default CURRENT_TIMESTAMP;
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
MySQL threw an error to prevent me from adding additional timestamp column with CURRENT_TIMESTAMP. This is true before version 5.6.5, but as of 5.6.5 or after, there is no such limitation.
The official documents that mention about the change can explain the feature.
- 11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME (https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html)
- Changes in MySQL 5.6.5 (2012-04-10, Milestone 8) (https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html)
As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.
...
DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP can be used with at most one TIMESTAMP column per table. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
...
Data Type Notes
Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.
The new feature is welcome among MySQL developers who can design tables more flexibly. But if you are bounded to the old version for some reasons and cannot upgrade to newer versions, you have to face the truth and find a way out after all.
My solution is to use triggers to make you able to create the second timestamp column with the default value CURRENT_TIMESTAMP.
mysql> delimiter //
mysql> drop trigger make_creation_default;
-> create trigger make_creation_default before insert on table_name
-> for each row
-> begin
-> set new.creation = current_timestamp;
-> end;
-> //
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.08 sec)