如何在UPDATE/INSERT语句中仅当其他列变更时更新last_dt列？

需要用last_dt列记录数据的最后修改日期，与记录插入日期的create_dt列分开管理，要求仅当除last_dt外的其他列发生变更时，才更新last_dt的日期值。GPT提供了如下SQL代码，但测试时出现错误：

UPDATE your_table
SET column1 = %s, column2 = %s, column3 = %s, update_date = IF(
    column1 <> %s OR column2 <> %s OR column3 <> %s,
    NOW(), update_date
)
WHERE id = %s

请问该方案是否正确？有没有更合适的解决方法？

原方案的问题 #

原SQL存在几个关键缺陷，可能是导致测试错误的原因：

1. NULL值处理失效：使用<> 比较时，若列值为NULL，比较结果会返回UNKNOWN，无法正确识别新旧值的变更（比如旧值是NULL、新值非NULL的情况）。
2. 参数冗余且易出错：需要重复传递新旧值，参数顺序如果混淆会直接导致逻辑错误。
3. 语法兼容性有限：NOW()是MySQL特定函数，换用其他数据库（如PostgreSQL）时需要修改，且IF函数的语法也存在数据库差异。

推荐解决方法 #

方法1：使用数据库触发器（最优方案） #

通过创建BEFORE UPDATE触发器，在数据库层面自动处理last_dt的更新逻辑，无需在业务代码中重复编写判断逻辑，避免人为错误。

以MySQL为例，触发器代码如下：

DELIMITER //
CREATE TRIGGER update_last_dt_trigger
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    -- 检查指定列是否发生变更，包含NULL值的特殊判断
    IF (NEW.column1 <> OLD.column1 OR (NEW.column1 IS NULL XOR OLD.column1 IS NULL))
       OR (NEW.column2 <> OLD.column2 OR (NEW.column2 IS NULL XOR OLD.column2 IS NULL))
       OR (NEW.column3 <> OLD.column3 OR (NEW.column3 IS NULL XOR OLD.column3 IS NULL)) THEN
        SET NEW.last_dt = NOW();
    END IF;
END //
DELIMITER ;

使用触发器的优势：

• 业务代码无需关心last_dt的更新逻辑，统一由数据库维护。
• 自动处理所有UPDATE操作，避免遗漏。
• 可以灵活扩展监控的列，只需修改触发器中的判断条件。

方法2：改进UPDATE语句（无触发器场景） #

如果无法使用触发器，可以修改原SQL，使用NULL安全比较运算符（MySQL中的<=>）来处理NULL值问题，同时优化逻辑结构：

UPDATE your_table
SET column1 = %s, column2 = %s, column3 = %s,
    last_dt = CASE
        WHEN NOT (column1 <=> %s AND column2 <=> %s AND column3 <=> %s)
        THEN NOW()
        ELSE last_dt
    END
WHERE id = %s;

说明：

• <=>运算符会在新旧值均为NULL时返回TRUE，一方为NULL时返回FALSE，完美解决NULL值比较的问题。
• CASE语句比IF函数的可读性更强，跨数据库兼容性更好。
• 注意参数顺序：前三个%s是新值，中间三个%s是旧值，最后一个%s是主键ID。

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