Can MySQL Tables Now Have Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP?

One TIMESTAMP Column With CURRENT_TIMESTAMP Limitation Lifted

In previous versions of MySQL, a table could only have one TIMESTAMP column with the CURRENT_TIMESTAMP value in either the DEFAULT or ON UPDATE clause. Attempting to create a table with multiple such columns resulted in an error.

The Restrictive Clause

The statement below illustrates the error-prone structure:

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
) ENGINE=INNODB;

The error returned:

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

Historical Reason

This limitation was historically rooted in code legacy reasons. However, it was lifted in recent MySQL versions.

Removal of the Restriction

The restriction was removed in MySQL 5.6.5 on April 10, 2012. The release notes state:

"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."

Implications

This change in MySQL allows for greater flexibility in table design. Developers can now use multiple TIMESTAMP columns with CURRENT_TIMESTAMP values to track changes in different aspects of a single entity.

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