在 MySQL 4.0 中同时具有 Created 和 Last Updated 时间戳列

Question

提问by Xenph Yan

I have the following table schema;

我有以下表格架构；

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.

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

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

Answer 1

回答by Robert Gamble

From the MySQL 5.5documentation:

来自MySQL 5.5文档：

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.

表中的一个 TIMESTAMP 列可以将当前时间戳作为初始化列的默认值，作为自动更新值，或两者兼而有之。不可能将当前时间戳作为一列的默认值和另一列的自动更新值。

Changes in MySQL 5.6.5:

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.

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

Answer 2

回答by Bogdan Gusiev

There is a trickto have both timestamps, but with a little limitation.

有一个技巧可以同时拥有两个时间戳，但有一点限制。

You can use only one of the definitions in one table. Create both timestamp columns like so:

您只能使用一张表中的定义之一。像这样创建两个时间戳列：

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() 
);

Note that it is necessary to enter nullinto both columns during insert:

请注意，null在以下期间必须同时输入两列insert：

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 3

回答by Stephen Walcher

You can have them both, just take off the "CURRENT_TIMESTAMP" flag on the created field. Whenever you create a new record in the table, just use "NOW()" for a value.

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

Or.

或者。

On the contrary, remove the 'ON UPDATE CURRENT_TIMESTAMP' flag and send the NOW() for that field. That way actually makes more sense.

相反，删除“ON UPDATE CURRENT_TIMESTAMP”标志并为该字段发送 NOW()。这种方式实际上更有意义。

Answer 4

回答by webkraller

If you do decide to have MySQL handle the update of timestamps, you can set up a trigger to update the field on insert.

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

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

MySQL Reference: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

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

Answer 5

回答by alien

This is how can you have automatic & flexible createDate/lastModified fields using triggers:

这就是您如何使用触发器拥有自动且灵活的 createDate/lastModified 字段：

First define them like this:

首先像这样定义它们：

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`),
)

Then add these triggers:

然后添加这些触发器：

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);

If you insertwithout specifying createDate or lastModified, they will be equal and set to the current timestamp.
If you updatethem without specifying createDate or lastModified, the lastModified will be set to the current timestamp.

如果您在不指定 createDate 或 lastModified 的情况下插入，则它们将相等并设置为当前时间戳。
如果您在未指定 createDate 或 lastModified 的情况下更新它们，则 lastModified 将设置为当前时间戳。

But here's the nice part:

但这是不错的部分：

If you insert, you can specify a createDate older than the current timestamp, allowing imports from older times to work well (lastModified will be equal to createDate).
If you update, you can specify a lastModified older than the previous value('0000-00-00 00:00:00' works well), allowing to update an entry if you're doing cosmetic changes (fixing a typo in a comment) and you want to keep the old lastModifieddate. This will not modify the lastModified date.

如果你插入，你可以指定一个比当前时间戳更旧的createDate，允许从更旧的时间导入（lastModified 将等于 createDate）。
如果你更新，你可以指定一个比前一个值更旧的lastModified（'0000-00-00 00:00:00' 效果很好），如果你正在做外观更改，则允许更新条目（修复注释中的错字) 并且您想保留旧的 lastModified日期。这不会修改 lastModified 日期。

Answer 6

回答by Shaheen Ghiassy

As of MySQL 5.6 its easy-peasy... give it a try:

从 MySQL 5.6 开始，它很简单……试一试：

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 7

回答by user5903005

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/

来源：http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

Answer 8

回答by Kingz

This issue seemed to have been resolved in MySQL 5.6. I have noticed this until MySQL 5.5; here is an example code:

这个问题似乎已经在 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;

Running this on MySQL 5.5 gives:

在 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

Running this on MySQL 5.6

在 MySQL 5.6 上运行

0 row(s) affected   0.093 sec

Answer 9

回答by Ioannis Chrysochos

For mysql 5.7.21 I use the following and works fine:

对于 mysql 5.7.21，我使用以下内容并且工作正常：

CREATE TABLE Posts( modified_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP )

创建表Posts（ modified_at时间戳非空 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP， created_at时间戳非空 DEFAULT CURRENT_TIMESTAMP）

Answer 10

回答by Anonim-

i think this is the better query for stamp_created and stamp_updated

我认为这是对stamp_created 和stamp_updated 更好的查询

CREATE TABLE test_table( 
    id integer not null auto_increment primary key, 
    stamp_created TIMESTAMP DEFAULT now(), 
    stamp_updated TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE now() 
);

because when the record created, stamp_createdshould be filled by now()and stamp_updatedshould be filled by '0000-00-00 00:00:00'

因为当记录创建时，stamp_created应该填写now()并且stamp_updated应该由'0000-00-00 00:00:00'

在 MySQL 4.0 中同时具有 Created 和 Last Updated 时间戳列

提问by Xenph Yan

回答by Robert Gamble

回答by Bogdan Gusiev

回答by Stephen Walcher

回答by webkraller

回答by alien

回答by Shaheen Ghiassy

回答by user5903005

回答by Kingz

回答by Ioannis Chrysochos

回答by Anonim-

相关推荐

最近更新

标签

在 MySQL 4.0 中同时具有 Created 和 Last Updated 时间戳列

提问by Xenph Yan

回答by Robert Gamble

回答by Bogdan Gusiev

回答by Stephen Walcher

回答by webkraller

回答by alien

回答by Shaheen Ghiassy

回答by user5903005

回答by Kingz

回答by Ioannis Chrysochos

回答by Anonim-

相关推荐

MySQL 如何使用许多连接优化慢查询

MySQL 创建新表时如何添加外键？

MySQL 更改表以提供外键约束

MySQL 你如何 OR 两个 LIKE 语句？

相关推荐

最近更新

标签