为什么在 DEFAULT 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列?

2021-11-20 00:00:00 timestamp mysql mysql-error-1293

为什么在 DEFAULT 或 ON UPDATE 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列?

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause?

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;

导致的错误:

错误代码:1293

表定义不正确；有可以只有一个 TIMESTAMP 列DEFAULT 或 ON 中的 CURRENT_TIMESTAMP更新子句

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

推荐答案

这个限制，这只是由于历史的，代码遗留的原因，在最近的 MySQL 版本中被取消了:

This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:

MySQL 5.6.5 的变化(2012-04-10，里程碑 8)

Changes in MySQL 5.6.5 (2012-04-10, Milestone 8)

以前，每个表最多可以有一个 TIMESTAMP 列自动初始化或更新为当前日期和时间.此限制已取消.任何 TIMESTAMP 列定义都可以具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE 的任意组合CURRENT_TIMESTAMP 子句.此外，现在可以使用这些子句带有 DATETIME 列定义.有关更多信息，请参阅自动TIMESTAMP 和 DATETIME 的初始化和更新.

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.

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

相关文章