Home > Database > Mysql Tutorial > Can MySQL Tables Have Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP?

Can MySQL Tables Have Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP?

Patricia Arquette
Release: 2024-11-30 08:35:11
Original
194 people have browsed it

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
);
Copy after login
Copy after login

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
Copy after login

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.
Copy after login

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
);
Copy after login
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template