mysql：如何在更新字段时自动更新TimeStamp

I have a simple DB-table with few fields for articles database among that is a TimeStamp containing the time of the last update made on the article.

我有一个简单的数据库表,文章数据库的字段很少,其中包含TimeStamp,其中包含对文章进行的最后一次更新的时间。

I use "ON UPDATE CURRENT_TIMESTAMP"

我使用“ON UPDATE CURRENT_TIMESTAMP”

Problem is that I want to update only if some fields of this record have changed, not all.

问题是我只想更新此记录的某些字段,而不是全部。

i.e. For each article, I have a field giving the number of time the article has been read by visitors. must not be updated after each web visit... only if I change the title, authors, etc...

即,对于每篇文章,我都有一个字段,给出访问者阅读文章的时间。每次网络访问后都不得更新...只有我改变标题,作者等...

Ideally with a nice mysql command...

理想情况下,使用一个很好的mysql命令......

---

I have coded the following:

我编码了以下内容:

CREATE TRIGGER `actu_lastupdate_date` BEFORE UPDATE ON `actuality`
 FOR EACH ROW BEGIN
    if NEW.title <> OLD.title OR NEW.chapeau = OLD.chapeau OR NEW.content = OLD.content
    then
        SET NEW.LastUpdate_date = current_timestamp;
    end if;
END

it seems that even if another record field is changing, the Timestamp is updated. There should be something bad somewhere. I'll look into it

似乎即使另一个记录字段正在改变,时间戳也会更新。某处应该有些坏事。我会调查一下

1 个解决方案

#1

You could write a trigger to add the timestamp on updates

您可以编写一个触发器来添加更新的时间戳

delimiter |
CREATE TRIGGER actu_lastupdate_date AFTER UPDATE on actuality
FOR EACH ROW
BEGIN
    if (NEW.title <> OLD.title 
         OR NEW.chapeau <> OLD.chapeau 
         OR NEW.content <> OLD.content)
        AND (NEW.other_column = OLD.other_column 
         OR NEW.other_column2 = OLD.other_column2)
    then
        SET NEW.LastUpdate_date = current_timestamp;
    end if;
END
|
delimiter ;