MySQL自动更新特定的列，而不是整个记录 [英] MySQL automatic update for specific column instead of whole record

问题描述

我有一张表APPLEVARIETY. 它包含以下列:

I have a table APPLEVARIETY. It has the following columns:

• id
• 家庭
• 颜色
• 说明(文字)
• description_last_update_time(TIMESTAMP)

对于后一列，我想使用"ON UPDATE CURRENT_TIMESTAMP".但是，如果至少其他一列(id，family，color)被更新，这将导致时间戳被更新.

For the latter column I would like to use 'ON UPDATE CURRENT_TIMESTAMP'. However, this will cause the timestamp to be updated if at least one of the other columns are updated (id, family, color).

相反，我想指定的是，如果仅描述列被更新，description_last_update_time将获得CURRENT_TIMESTAMP.它应该忽略其余列的更新.

Instead I would like to specify that the description_last_update_time will get the CURRENT_TIMESTAMP if only the description column is updated. It should ignore updates on the rest of the columns.

我浏览了MySQL手册，关于stackoverflow的其他问题，当然也广泛使用了google，但没有发现任何问题.

I have looked through the MySQL manual, other questions on stackoverflow and of course used google extensively but found nothing.

换句话说:是否可以通过ON UPDATE 指定某个列?甚至有可能还是我朝错误的方向前进?

So in other words: is there some way to specify a certain column with ON UPDATE? Is it even possible or am I moving in the wrong direction?

推荐答案

使用ON UPDATE CURRENT_TIMESTAMP不可能，这将在更新任何列时得到更新. 但是，您可以使用触发器来实现相同的目的.为此，您首先需要从表定义中删除ON UPDATE CURRENT_TIMESTAMP. 触发器看起来像

No thats not possible using the ON UPDATE CURRENT_TIMESTAMP this will get updated when any column is updated. You may however achieve the same using a trigger. For that you will first need to remove ON UPDATE CURRENT_TIMESTAMP from the table definition. The trigger will look like

delimiter //

create trigger APPLEVARIETY_update before update on APPLEVARIETY
for each row 
begin
  if new.description <> old.description then
    set new.description_last_update_time = now();
  end if;
end;//

delimiter ;

这篇关于MySQL自动更新特定的列，而不是整个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！