MySQL CURRENT_TIMESTAMP 字段在每次更新时更新

解释这个问题的最好方法是举个例子.

我有一张桌子:

CREATE TABLE `example` (`id` int(11) NOT NULL AUTO_INCREMENT,`data` varchar(255) 默认为空，`created` 时间戳 NOT NULL DEFAULT CURRENT_TIMESTAMP,`updated` 日期时间 DEFAULT NULL,主键(`id`)) 引擎=MyISAM 默认字符集=utf8

结果:

<上一页>编号 |数据 |创建 |更新(空)|(NULL) |(NULL) |(空值)

然后我插入一些数据:

INSERT INTO 示例 (`数据`) 价值观 ('abc123')

结果:

<上一页>编号 |数据 |创建 |更新1 |abc123 |2013-01-16 13:12:16 |(空值)

然后我更新

UPDATE 示例 SET`数据` = 'def456',`更新`=现在()哪里 id = 1

结果:

<上一页>编号 |数据 |创建 |更新1 |def456 |2013-01-16 13:16:24 |2013-01-16 13:14:26

问题:注意 created 字段如何更新，并且正确保存更新字段的时间略有不同.我已经在同一个数据库上设置了这个示例表和其他类似的表，没有这个问题，所以我完全被它弄糊涂了.

解决方案

这很可能是问题表表是如何被意外创建的:

CREATE TABLE `example` (`id` INT(11) NOT NULL AUTO_INCREMENT,`data` VARCHAR(255) 默认为空，`created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`更新的`日期时间默认为空，主键(`id`)) 引擎=MYISAM 默认字符集=utf8；

也许有人使用 3rd 方软件创建它?

ON UPDATE CURRENT_TIMESTAMP 将破坏创建日期.所以要解决问题像这样使用ALTER TABLE:

ALTER TABLE 示例 CHANGE created created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

这将消除每次更新时对 created 字段的不必要覆盖.

The best way to explain this problem is with an example.

I have a table:

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

Result:

   id  |  data  |       created       |       updated
 (NULL)| (NULL) |       (NULL)        |       (NULL)

Then I insert some data:

INSERT INTO example (
  `data`
) VALUES (
  'abc123'
)

Result:

  id  |  data  |       created       |       updated
   1  | abc123 | 2013-01-16 13:12:16 |       (NULL)

And then I update

UPDATE example SET 
  `data` = 'def456',
  `updated` = NOW()
WHERE id = 1

Result:

  id  |  data  |       created       |       updated
   1  | def456 | 2013-01-16 13:16:24 | 2013-01-16 13:14:26

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?

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;

This will get rid of the unwanted overwrite of the created field on every update.