在MySQL 4.0中同时创建了创建时间戳记列和最后更新时间戳列

Question

我有以下表格架构;

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; 

它失败，出现以下错误：

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

我的问题是，我可以同时拥有这些领域的？还是必须在每次交易过程中手动设置LastUpdated字段？

Answer 1

From the MySQL 5.5 documentation：

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Changes in MySQL 5.6.5：

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.

Answer 2

你可以拥有它们，只需在创建的字段上取下“CURRENT_TIMESTAMP”标志即可。每当您在表格中创建新记录时，只需使用“NOW（）”作为值。

或者。

相反，删除'ON UPDATE CURRENT_TIMESTAMP'标志并发送该字段的NOW（）。这种方式实际上更有意义。

Answer 3

There is a trick兼得时间戳，但有一点限制。

您只能在一张表中使用其中一个定义。在insert

create table test_table( 
    id integer not null auto_increment primary key, 
    stamp_created timestamp default '0000-00-00 00:00:00', 
    stamp_updated timestamp default now() on update now() 
); 

注意，有必要进入null成两列：像这样创建两个时间戳列

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec) 

mysql> select * from test_table; 
+----+---------------------+---------------------+ 
| id | stamp_created  | stamp_updated  | 
+----+---------------------+---------------------+ 
| 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec) 

mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 

mysql> select * from test_table; 
+----+---------------------+---------------------+ 
| id | stamp_created  | stamp_updated  | 
+----+---------------------+---------------------+ 
| 3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec) 

Answer 4

如果你决定有MySQL的处理时间戳的更新，你可以设置一个触发器来更新插入时的字段。

CREATE TRIGGER <trigger_name> BEFORE INSERT ON <table_name> FOR EACH ROW SET NEW.<timestamp_field> = CURRENT_TIMESTAMP; 

MySQL参考：http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Answer 5

这就是你怎么能有自动&灵活CREATEDATE /上次更改领域使用触发器：

首先定义它们是这样的：

CREATE TABLE `entity` (
    `entityid` int(11) NOT NULL AUTO_INCREMENT, 
    `createDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
    `lastModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
    `name` varchar(255) DEFAULT NULL, 
    `comment` text, 
    PRIMARY KEY (`entityid`), 
) 

然后加入这些触发器：

DELIMITER ;; 
CREATE trigger entityinsert BEFORE INSERT ON entity FOR EACH ROW BEGIN SET NEW.createDate=IF(ISNULL(NEW.createDate) OR NEW.createDate='0000-00-00 00:00:00', CURRENT_TIMESTAMP, IF(NEW.createDate<CURRENT_TIMESTAMP, NEW.createDate, CURRENT_TIMESTAMP));SET NEW.lastModified=NEW.createDate; END;; 
DELIMITER ; 
CREATE trigger entityupdate BEFORE UPDATE ON entity FOR EACH ROW SET NEW.lastModified=IF(NEW.lastModified<OLD.lastModified, OLD.lastModified, CURRENT_TIMESTAMP); 

• 如果插入没有指定CREATEDATE或上次更改时间，他们将是平等的，设置为当前的时间戳。
• 如果你更新他们没有指定createDate或lastModified，lastModified将被设置为当前时间戳。

但这里有一个很好的部分：

• 如果插入，你可以指定一个CREATEDATE比当前时间戳老，允许从旧时代的进口运行良好（上次更改时间将等于创建日期）。
• 如果你更新，你可以指定一个lastModified早于前值（'0000-00-00 00:00:00'效果很好），允许更新条目，如果你正在做整容修改（修复评论中的拼写错误），并且想要保留旧的lastModified日期。这不会修改lastmodified日期。

Answer 6

这个问题似乎已经在MySQL 5.6中解决了。我已经注意到，直到MySQL 5.5;这里有一个例子代码：

DROP TABLE IF EXISTS `provider_org_group` ; 
CREATE TABLE IF NOT EXISTS `provider_org_group` (
    `id` INT NOT NULL, 
    `name` VARCHAR(100) NOT NULL, 
    `type` VARCHAR(100) NULL, 
    `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `insert_src_ver_id` INT NULL, 
    `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, 
    `update_src_ver_id` INT NULL, 
    `version` INT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE INDEX `id_UNIQUE` (`id` ASC), 
    UNIQUE INDEX `name_UNIQUE` (`name` ASC)) 
ENGINE = InnoDB; 

在MySQL 5.5中运行这给：

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

运行此在MySQL 5.6

0 row(s) affected 0.093 sec 

Answer 7

自MySQL 5.6的易peasy ...试试看：

create table tweet ( 
    id integer not null auto_increment primary key, 
    stamp_created timestamp default now(), 
    stamp_updated timestamp default now() on update now(), 
    message varchar(163) 
) 

Answer 8

create table test_table( 
id integer not null auto_increment primary key, 
stamp_created timestamp default '0000-00-00 00:00:00', 
stamp_updated timestamp default now() on update now() 
); 

source: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/