MySQL ON UPDATE CURRENT_TIMESTAMP 未更新
我有一个如下所示的表格:
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);
相关文章