Can MySQL Tables Have Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP?

Only One TIMESTAMP Column Allowed with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE Clause

MySQL's historical code limitations restricted tables to having only one TIMESTAMP column with CURRENT_TIMESTAMP in either the DEFAULT or ON UPDATE clause. However, this limitation has since been lifted in recent versions of MySQL.

Legacy Error:

Consider the following table definition:

CREATE TABLE `foo` (
  `ProductID` INT(10) UNSIGNED NOT NULL,
  `AddedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Attempting to create this table would result in the following error:

Error Code : 1293
Incorrect table definition; there can
be only one TIMESTAMP column with
CURRENT_TIMESTAMP in DEFAULT or ON
UPDATE clause

Resolution in MySQL 5.6.5 and Later:

Starting with MySQL 5.6.5, this restriction has been removed. Any TIMESTAMP or DATETIME column can now have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses.

As per the MySQL 5.6.5 release 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.

Therefore, the table definition provided in the legacy error can now be created successfully:

CREATE TABLE `foo` (
  `ProductID` INT(10) UNSIGNED NOT NULL,
  `AddedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The above is the detailed content of Can MySQL Tables Have Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP?. For more information, please follow other related articles on the PHP Chinese website!