在mysql中添加两个自动更新的timestamp字段

时间：2009-07-01 来源：sss0213

在MySql 5.0 的表里同时添加两个自动更新的timestamp字段
有时我们需要在一张表里实现两种timestamp属性的字段，一种是在insert的时候自动赋值，另一种是在update时候赋值，但是在，mysql5.0里面这两种字段十户无法共存，比如你在创建者张表时：
java 代码

CREATE TABLE blog_entries (
`published` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated` TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`title` VARCHAR(128)
);

数据库会报错： ERROR 1293 (HY000): Incorrect table definition; there can be only one
TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

解决办法是使用trigger，首先将上面新建表格的语句改成以下内容：
java 代码

CREATE TABLE blog_entries (
`published` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
`updated` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
`title` VARCHAR(128)
);

然后写两个trigger：
java 代码

CREATE TRIGGER blog_entries_publish BEFORE INSERT ON `blog_entries`
FOR EACH ROW SET NEW.published = NOW(), NEW.updated = '0000-00-00 00:00:00';

java 代码

CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries`
FOR EACH ROW SET NEW.updated = NOW(), NEW.published = OLD.published;

这样子似乎可以了，但是问题却随之而来了，如果你想手动更改updated的值怎么办？没错你改不了，为了解决这个问题我们在此作如下改动：
java 代码

CREATE TABLE blog_entries (
`published` TIMESTAMP NULL DEFAULT NULL,
`updated` TIMESTAMP NULL DEFAULT NULL,
`title` VARCHAR(128)
);
CREATE TRIGGER blog_entries_publish BEFORE INSERT ON `blog_entries` FOR EACH ROW SET
NEW.published = IFNULL(NEW.published, NOW()),
NEW.updated = IFNULL(NEW.updated, '0000-00-00 00:00:00');
CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries` FOR EACH ROW SET
NEW.updated = IF(NEW.updated = OLD.updated OR NEW.updated IS NULL, NOW(), NEW.updated),
NEW.published = IFNULL(NEW.published, OLD.published);

好了，现在你可以随意改动update，但是如果你没有传值给update会怎么样呢？这时NEW.updated 默认等于OLD.update值，如果你传NULL给update无效，所以你必须再做一点改动，判断这个条件：
java 代码

CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries` FOR EACH ROW SET
NEW.updated = CASE
WHEN NEW.updated IS NULL THEN OLD.updated
WHEN NEW.updated = OLD.updated THEN NOW()
ELSE NEW.updated
END,
NEW.published = IFNULL(NEW.published, OLD.published);