如果列的默认NULL值更改为某个值，则MySQL Trigger不会执行

Here is the MYSQL Trigger that I have written:

这是我写的MYSQL触发器：

DELIMITER //
CREATE TRIGGER updtrigger BEFORE UPDATE ON myTable
    FOR EACH ROW
    BEGIN
    IF (OLD.column IS NOT NULL AND NEW.column IS NOT NULL AND NEW.column != OLD.column) THEN 
        SET NEW.col_updated_on = NOW();
    END IF;
END //
DELIMITER ;

If column has a value (say, "movie"), and I update it to get new value, "movie,music", the trigger gets executed and col_updated_on should have the current timestamp.

如果列具有值（例如，“movie”），并且我更新它以获得新值“movie，music”，则触发器被执行并且col_updated_on应该具有当前时间戳。

However, when column is NULL and I update it to get the new value, "movie", the col_updated_on column will still show me the old timestamp.

但是，当列为NULL并且我更新它以获取新值“movie”时，col_updated_on列仍将显示旧的时间戳。

Please let me know what change I must do to check for this condition as well.

请让我知道我必须做些什么改变来检查这个条件。

Thanks in advance for your replies.

提前感谢您的回复。

I have another question. Is the below pseudo possible??

我有另一个问题。下面的伪可能吗？

Here are my two tables -
myTable(id, someId, col_updated_on); myOtherTable(id, col1, col2);

这是我的两个表 - myTable（id，someId，col_updated_on）; myOtherTable（id，col1，col2）;

myTable.someId has a 1-on-1 relation with myOtherTable.id

myTable.someId与myOtherTable.id具有1对1的关系

I want to update myTable.col_updated_on whenever myOtherTable.col1 and myOtherTable.col2 are updated. How do i do this? and should i use "BEFORE UPDATE" or "AFTER UPDATE"?

每当myOtherTable.col1和myOtherTable.col2更新时，我想更新myTable.col_updated_on。我该怎么做呢？我应该使用“BEFORE UPDATE”还是“After UPDATE”？

1 个解决方案

#1

Use the NULL-safe equality operator:

使用NULL安全等于运算符：

IF NOT OLD.column <=> NEW.column THEN

However, note that MySQL's TIMESTAMP data type can (and, by default, will) automatically update whenever a record is updated, making such a trigger unnecessary.

但是，请注意，MySQL的TIMESTAMP数据类型可以（并且默认情况下）在更新记录时自动更新，从而不需要这样的触发器。

But also, you REALLY shouldn't store multiple items in a delimited string in a relational database like MySQL. Read up on database design, especially one-to-many relationships: one would fare much better by having a separate table, in which each record links an identifier (key) from your existing (foreign) table to a single item; one would then join the tables together as required in your queries.

但是，你真的不应该在像MySQL这样的关系数据库中的分隔字符串中存储多个项目。阅读数据库设计，尤其是一对多关系：通过使用单独的表可以更好地实现，其中每个记录将现有（外部）表中的标识符（键）链接到单个项;然后，您可以根据查询中的要求将表连接在一起。

#1