mysql on update cu,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 ;

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'

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.

解决方案

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

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

....

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.

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