SQL仅更新值发生变更的字段时语句不生效问题咨询

你写的SQL存在两个核心逻辑错误：

• WHERE子句用AND拼接所有字段的不等判断，实际逻辑是只有当Id匹配，且Name、Phone、Email、Status四个字段全部和传入参数不一致时，才会执行整行更新。只要任意一个字段和传入值相同，整个WHERE条件就不成立，更新不会执行，和你预期的「只要有字段变更就更新」逻辑完全相反。
• 没有处理NULL值兼容问题：SQL中NULL和任意值做=/!=判断的结果都是UNKNOWN（会被判定为逻辑假），如果字段值或者传入参数为NULL，不等判断会直接失效。

除此之外，就算WHERE条件命中，你当前的写法是不管字段有没有实际变化都给四个字段重新赋值，也没做到仅更新变更内容的要求，还会产生无意义的字段修改日志、触发不必要的关联触发器逻辑。

如果你的数据库支持IS DISTINCT FROM语法（比如PostgreSQL、MySQL 8.0+、SQL Server 2022+），推荐用下面的写法，天然兼容NULL值判断，写法也更简洁：

UPDATE Person 
SET 
    Name = CASE WHEN Name IS DISTINCT FROM @Name THEN @Name ELSE Name END,
    Phone = CASE WHEN Phone IS DISTINCT FROM @Phone THEN @Phone ELSE Phone END,
    Email = CASE WHEN Email IS DISTINCT FROM @Email THEN @Email ELSE Email END,
    Status = CASE WHEN Status IS DISTINCT FROM @Status THEN @Status ELSE Status END
WHERE Id = @Id 
AND (
    Name IS DISTINCT FROM @Name 
    OR Phone IS DISTINCT FROM @Phone 
    OR Email IS DISTINCT FROM @Email 
    OR Status IS DISTINCT FROM @Status
);

如果是不支持IS DISTINCT FROM的低版本数据库，把判断替换为兼容NULL的写法即可，完整代码如下：

UPDATE Person 
SET 
    Name = CASE WHEN (Name != @Name OR Name IS NULL OR @Name IS NULL) AND NOT (Name IS NULL AND @Name IS NULL) THEN @Name ELSE Name END,
    Phone = CASE WHEN (Phone != @Phone OR Phone IS NULL OR @Phone IS NULL) AND NOT (Phone IS NULL AND @Phone IS NULL) THEN @Phone ELSE Phone END,
    Email = CASE WHEN (Email != @Email OR Email IS NULL OR @Email IS NULL) AND NOT (Email IS NULL AND @Email IS NULL) THEN @Email ELSE Email END,
    Status = CASE WHEN (Status != @Status OR Status IS NULL OR @Status IS NULL) AND NOT (Status IS NULL AND @Status IS NULL) THEN @Status ELSE Status END
WHERE Id = @Id 
AND (
    (Name != @Name OR Name IS NULL OR @Name IS NULL) AND NOT (Name IS NULL AND @Name IS NULL)
    OR (Phone != @Phone OR Phone IS NULL OR @Phone IS NULL) AND NOT (Phone IS NULL AND @Phone IS NULL)
    OR (Email != @Email OR Email IS NULL OR @Email IS NULL) AND NOT (Email IS NULL AND @Email IS NULL)
    OR (Status != @Status OR Status IS NULL OR @Status IS NULL) AND NOT (Status IS NULL AND @Status IS NULL)
);

• WHERE子句用OR拼接字段差异判断，保证只要任意一个字段和传入值有差异，就命中要更新的行；没有任何字段变化的行直接跳过，不执行更新
• SET子句用CASE判断每个字段是否真的有变化，有变化才赋值为新值，没变化就保留原字段值，真正实现只更新变更内容
• 兼容NULL值判断场景，避免字段或参数为NULL时普通不等号判断失效的问题

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