解释此问题的最佳方法是举一个例子.
The best way to explain this problem is with an example.
CREATE TABLE `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` varchar(255) DEFAULT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8结果:
id | data | created | updated (NULL)| (NULL) | (NULL) | (NULL)然后我插入一些数据:
INSERT INTO example ( `data` ) VALUES ( 'abc123' )结果:
id | data | created | updated 1 | abc123 | 2013-01-16 13:12:16 | (NULL)然后我更新
UPDATE example SET `data` = 'def456', `updated` = NOW() WHERE id = 1结果:
id | data | created | updated 1 | def456 | 2013-01-16 13:16:24 | 2013-01-16 13:14:26问题:请注意created字段也是如何更新的,并且正确保存更新后的字段的时间略有不同.我已经在同一数据库上设置了这个示例表和其他示例表,而没有出现此问题,所以我对此完全感到困惑.
The problem: Notice how the created field also updates and has a slightly different time to correctly saved updated field. I have set up this example table and others similarly on the same database without this problem, so I'm completely baffled by it.
推荐答案这很可能是偶然创建问题表表的方式:
This is likely how the problem table table was accidentally created:
CREATE TABLE `example` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) DEFAULT NULL, `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `updated` DATETIME DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8;也许有人使用了第三方软件来创建它?
Perhaps someone used 3rd party software to create it?
ON UPDATE CURRENT_TIMESTAMP将破坏创建日期.因此,要解决问题,请像这样使用ALTER TABLE:
An ON UPDATE CURRENT_TIMESTAMP is going to destroy the create date. So to solve the problem use ALTER TABLE like so:
ALTER TABLE example CHANGE created created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;这将消除每次更新时对created字段的不必要覆盖.
This will get rid of the unwanted overwrite of the created field on every update.
更多推荐
每次更新时,MySQL CURRENT
发布评论