MySQL CURRENT_TIMESTAMP field updates on every upd

问题:

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.

回答1:

updated needs to be: TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

So your CREATE TABLE would be:

CREATE TABLE `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

This means that when you perform your UPDATE in the future, you won't need to pass in an update variable because MySQL will automatically update it for you :)

回答2:

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.