MySQL ON UPDATE CURRENT_TIMESTAMP 未更新

MySQL ON UPDATE CURRENT_TIMESTAMP not updating

提问by coding_hero

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 ;

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:

在我看来，这意味着任何时候更新一行，“Lastconnection”字段都应该加上当前时间戳。但是，当我运行类似的东西时：

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

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.

Lastconnection 保持 '0000-00-00 00:00:00'。我没有看到数据库问题，或者我完全误解了“ON UPDATE CURRENT_TIMESTAMP”子句。

回答by barranquero

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

您是否尝试在更新时对该字段使用 null？

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

您也可以尝试将默认值设置为CURRENT_TIMESTAMP，而不是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(),
....

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

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

The trick with CREATEDfield is to remember to use null on both fields for INSERTstatements, for UPDATEstatements it is not required:

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

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

回答by Gas Welder

It might be the case that the update statement doesn't change anything. If the row with Tid = 'SITE001'already has Backupstatusset to 'FAIL'and Backupmsgset to 'Connection timed out'(maybe, set by some previous backup attempt), then MySQL will skip this row and therefore won't change the Lastconnectiontimestamp.

更新语句可能不会改变任何内容。如果行Tid = 'SITE001'已经Backupstatus设置为'FAIL'并Backupmsg设置为'Connection timed out'（可能，由之前的一些备份尝试设置），那么 MySQL 将跳过该行，因此不会更改Lastconnection时间戳。

Also, I see ON UPDATE CURRENT_TIMESTAMPmore like an administrative feature to keep track of data changes. As a programmer, I would add the timestamp update explicitly:

此外，我认为ON UPDATE CURRENT_TIMESTAMP更像是一种管理功能来跟踪数据更改。作为程序员，我会明确添加时间戳更新：

update Hosts
set Backupstatus = 'FAIL', Backupmsg = 'Connection timed out', Lastconnection = NOW() where Tid = 'SITE001'.

update Hosts
set Backupstatus = 'FAIL', Backupmsg = 'Connection timed out', Lastconnection = NOW() where Tid = 'SITE001'.

回答by Pini Cheyni

You must remember that if no value was changedon update it won't set the current time stamp,

您必须记住，如果更新时未更改任何值，则不会设置当前时间戳，

You have to set the value in query NOW()to set for the current time stamp !!!

您必须在查询NOW()中设置值以设置当前时间戳！！！

update Hosts set Backupstatus = 'FAIL',Lastconnection = NOW() , Backupmsg = 'Connection timed out' where Tid = 'SITE001'

update Hosts set Backupstatus = 'FAIL',Lastconnection = NOW() , Backupmsg = 'Connection timed out' where Tid = 'SITE001'

Remember the value must change in order for the current time stamp to change.

请记住，该值必须更改才能更改当前时间戳。

回答by Nag Hammadi

To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMPand ON UPDATE CURRENT_TIMESTAMPclauses. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMPhave the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

要指定自动属性，请使用DEFAULT CURRENT_TIMESTAMPandON UPDATE CURRENT_TIMESTAMP子句。子句的顺序无关紧要。如果两者都出现在列定义中，则任一者都可以先出现。的任何同义词CURRENT_TIMESTAMP都与 具有相同的含义CURRENT_TIMESTAMP。这是CURRENT_TIMESTAMP()，NOW()，LOCALTIME，LOCALTIME()，LOCALTIMESTAMP，和LOCALTIMESTAMP()。

Use of DEFAULT CURRENT_TIMESTAMPand ON UPDATE CURRENT_TIMESTAMPis specific to TIMESTAMP. The DEFAULTclause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0or DEFAULT '2000-01-01 00:00:00'.

使用的DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP特定于TIMESTAMP。该DEFAULT子句还可用于指定常量（非自动）默认值；例如，DEFAULT 0或DEFAULT '2000-01-01 00:00:00'。

DEFAULT 0do not work if the NO_ZERO_DATESQL mode is enabled because that mode causes “zero” date values (specified, for example, as 0 '0000-00-00 00:00:00') to be rejected. Be aware that the TRADITIONALSQL mode includes NO_ZERO_DATE.

DEFAULT 0如果NO_ZERO_DATE启用了SQL 模式，则不起作用，因为该模式会导致“零”日期值（例如，指定为 0 '0000-00-00 00:00:00'）被拒绝。请注意，TRADITIONALSQL 模式包括NO_ZERO_DATE.

In addition, you can initialize or update any TIMESTAMPcolumn to the current date and time by assigning it a NULLvalue, unless it has been defined with the NULLattribute to permit NULLvalues.

此外，您可以TIMESTAMP通过为其分配一个NULL值来将任何列初始化或更新为当前日期和时间，除非它已使用NULL属性定义为允许NULL值。

回答by psterling

If you want the record to automatically update the timestamp whenever the record is changed, here's the four simple steps you need to accomplish (could be all in one step, depending on if you're using command line or GUI to administer):

如果您希望记录在记录更改时自动更新时间戳，这里是您需要完成的四个简单步骤（可以一步完成，具体取决于您是使用命令行还是 GUI 进行管理）：

1. Create field to hold auto-updated timestamp (I typically call mine 'modified').
2. Specify field Type as 'TIMESTAMP'
3. Specify field Default as 'CURRENT_TIMESTAMP'
4. Specify field Extra as 'ON UPDATE CURRENT_TIMESTAMP'

1. 创建字段以保存自动更新的时间戳（我通常称我的为“已修改”）。
2. 将字段类型指定为“TIMESTAMP”
3. 指定字段默认为“CURRENT_TIMESTAMP”
4. 将字段 Extra 指定为“ON UPDATE CURRENT_TIMESTAMP”

Now the field that contains your timestamp will always be updated to the current timestamp anytime the record is updated.

现在，无论何时更新记录，包含您的时间戳的字段都将始终更新为当前时间戳。