MySQL ON UPDATE CURRENT_TIMESTAMP 未更新 [英] MySQL ON UPDATE CURRENT_TIMESTAMP not updating

问题描述

我有一个如下所示的表格:

I've got a table that looks like this:

CREATE TABLE IF NOT EXISTS `Hosts` (
`id` int(128) NOT NULL AUTO_INCREMENT,
`IP` varchar(15) NOT NULL DEFAULT '',
`Port` varchar(5) NOT NULL DEFAULT '',
`Password` varchar(32) NOT NULL DEFAULT '',
`Username` varchar(32) NOT NULL DEFAULT '',
`Tid` varchar(32) NOT NULL DEFAULT '',
`EquipType` varchar(64) NOT NULL DEFAULT '',
`Version` varchar(128) DEFAULT NULL,
`Status` varchar(10) NOT NULL DEFAULT '',
`Location` varchar(128) NOT NULL DEFAULT '',
`Lastconnection` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`Lastbackup` date NOT NULL DEFAULT '0000-00-00',
`Backupstatus` varchar(64) NOT NULL DEFAULT '',
`Backupmsg` text,
`Backupfile` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `IP` (`IP`),
KEY `Tid` (`Tid`),
KEY `EquipType` (`EquipType`),
KEY `Status` (`Status`),
KEY `Lastbackup` (`Lastbackup`),
KEY `Backupstatus` (`Backupstatus`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=716 ;

在我看来，这意味着每当更新一行时，Lastconnection"字段都应该带有当前时间戳.但是，当我运行类似:

In my mind, this means that any time a row is updated, the field 'Lastconnection' should be stamped with the current timestamp. However, when I run something like:

update Hosts set Backupstatus = 'FAIL',  Backupmsg = 'Connection timed out' where Tid = 'SITE001'

上次连接保持0000-00-00 00:00:00".我没有看到数据库问题，或者我完全误解了ON UPDATE CURRENT_TIMESTAMP"子句.

Lastconnection stays '0000-00-00 00:00:00'. There's either a database issue I'm not seeing, or I'm completely misunderstanding the 'ON UPDATE CURRENT_TIMESTAMP' clause.

推荐答案

你有没有尝试在更新时为那个字段使用null?

Have you tried to use null for that field when updating?

您也可以尝试将默认值设置为 CURRENT_TIMESTAMP，而不是 0000-00-00 00:00:00.

You could also try setting default value to CURRENT_TIMESTAMP, rather than 0000-00-00 00:00:00.

尽管如此，每当我想要创建和更新时间时，我总是使用以下内容:

Nevertheless, whenever I want to have creation and update time I always use the following:

...
CREATED timestamp NOT NULL default '0000-00-00 00:00:00',
UPDATED timestamp NOT NULL default now() on update now(),
....

我使用 now()，因为它是 CURRENT_TIMESTAMP 的别名，而且它更短.最后，表结构得到CURRENT_TIMESTAMP，不用担心.

I use now(), because is an alias for CURRENT_TIMESTAMP and it is shorter. At the end, table structure gets CURRENT_TIMESTAMP, so don't worry.

CREATED 字段的诀窍是记住在 INSERT 语句的两个字段上都使用 null，对于 UPDATE 语句，它不是必需的:

The trick with CREATED field is to remember to use null on both fields for INSERT statements, for UPDATE statements it is not required:

INSERT INTO mytable (field1, field2, created, updated)
VALUES ('foo', 'bar', null, null);

这篇关于MySQL ON UPDATE CURRENT_TIMESTAMP 未更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！