MySQL触发器在字段值变为NULL时不生效的问题咨询

需要跟踪results表中processed_at字段的所有变更，并将变更记录存入processed_changes表。当前触发器在该字段更新为非NULL值时正常工作，但当旧值为非NULL、新值为NULL时，预期NEW.processed_at != OLD.processed_at条件成立，却没有向processed_changes表插入记录。当前触发器代码如下：

create definer = xxx@`%` trigger processed_change_on_update
    after update
    on results
    for each row
begin
    if NEW.processed_at != OLD.processed_at then
        insert into processed_changes (result_id, operation, old_processed_at, new_processed_at)
        values (NEW.id, 'update', OLD.processed_at, NEW.processed_at);
    end if;
end;

核心问题是SQL中NULL的比较逻辑特殊：任何值与NULL使用普通比较运算符（=/!=/<>等）比较时，结果都是UNKNOWN，而非TRUE或FALSE，这会导致你的if条件不成立，从而无法执行插入操作。

解决方案1：使用空值安全比较运算符 #

MySQL提供了<=>运算符（空值安全等于），它能正确处理NULL的比较：当两边值相等（包括都是NULL）时返回1，不等时返回0。我们可以用它的否定形式来判断字段值是否发生变化：

create definer = xxx@`%` trigger processed_change_on_update
    after update
    on results
    for each row
begin
    if NOT (NEW.processed_at <=> OLD.processed_at) then
        insert into processed_changes (result_id, operation, old_processed_at, new_processed_at)
        values (NEW.id, 'update', OLD.processed_at, NEW.processed_at);
    end if;
end;

解决方案2：显式判断NULL场景 #

如果不想使用<=>，可以通过显式判断NULL的组合条件来覆盖所有变更场景：

create definer = xxx@`%` trigger processed_change_on_update
    after update
    on results
    for each row
begin
    if (NEW.processed_at IS NULL AND OLD.processed_at IS NOT NULL) 
       OR (NEW.processed_at IS NOT NULL AND OLD.processed_at IS NULL)
       OR (NEW.processed_at != OLD.processed_at) then
        insert into processed_changes (result_id, operation, old_processed_at, new_processed_at)
        values (NEW.id, 'update', OLD.processed_at, NEW.processed_at);
    end if;
end;

两种方案都能正确捕获processed_at字段的所有变更，包括NULL与非NULL之间的切换。

内容的提问来源于stack exchange，提问作者Chris