每次更新时，MySQL CURRENT_TIMESTAMP字段都会更新 [英] MySQL CURRENT_TIMESTAMP field updates on every update

问题描述

解释此问题的最佳方法是举一个例子.

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_TIMESTAMP字段都会更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！