在MySQL 4.0中同时具有Created和Last Updated timestamp列 [英] Having both a Created and Last Updated timestamp columns in MySQL 4.0

问题描述

我有以下表格模式；

CREATE TABLE `db1`.`sms_queue` (
  `Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Message` VARCHAR(160) NOT NULL DEFAULT 'Unknown Message Error',
  `CurrentState` VARCHAR(10) NOT NULL DEFAULT 'None',
  `Phone` VARCHAR(14) DEFAULT NULL,
  `Created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `TriesLeft` tinyint NOT NULL DEFAULT 3,
  PRIMARY KEY (`Id`)
)
ENGINE = InnoDB;

它失败并出现以下错误:

It fails with the following error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

我的问题是，我可以同时拥有这两个字段吗?还是我必须在每次交易期间手动设置LastUpdated字段?

My question is, can I have both of those fields? or do I have to manually set a LastUpdated field during each transaction?

推荐答案

表中的一个TIMESTAMP列可以将当前时间戳记作为初始化该列的默认值，作为自动更新值或同时作为两者. 不可能将当前时间戳记用作一列的默认值，而将其作为另一列的自动更新值.

MySQL中的更改5.6.5 :

以前，每个表最多可以自动将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.

这篇关于在MySQL 4.0中同时具有Created和Last Updated timestamp列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！