英文:
mysql trigger does not work if value is null
问题
我想跟踪特定列的所有更改并将它们存储到另一张表中。如果processed_at更新为一个值，除非它是null，这将起作用。然而，我期望在旧值不为null而新值为null的情况下，以下条件
if NEW.processed_at != OLD.processed_at then
也为真，但在这种情况下不会向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;
英文:
I want to track all changes of a specific column and store them to another table. This works if processed_at is updated to a value except when it is null. However I would expect that this condition
> if NEW.processed_at != OLD.processed_at then
is also true if the old value is not null and the new is null, but it does not insert a new row to the processed_changes table in that case.
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;
</details>
# 答案1
**得分**: 0
使用空安全比较解决了问题。
来自Akinas的评论：
> 当操作数为NULL时，结果也是NULL。NULL被视为FALSE。您必须使用空安全比较。如果NOT NEW.processed_at <=> OLD.processed_at，则...
<details>
<summary>英文:</summary>
using a null safe comparison saved the problem.
From Akinas comment:
> When operand is NULL then the result is NULL too. NULL is treated as
> FALSE. You must use NULL-safe compare. if NOT NEW.processed_at <=>
> OLD.processed_at then
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论