MySQL中如何在UPDATE后触发器中高效检测字段变更？

问题场景 #

我在一个仅含4列的表上创建了AFTER UPDATE触发器，当前通过多IF条件实现审计日志插入，代码如下：

IF (NEW.courseStartDate <> OLD.courseStartDate )
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'courseStartDate',OLD.courseStartDate,NEW.courseStartDate,NOW());
    END IF;
    
    IF (NEW.courseEndDate <> OLD.courseEndDate)
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'courseEndDate',OLD.courseEndDate,NEW.courseEndDate,NOW());
    END IF;
    
    IF (NEW.course_id <> OLD.course_id)
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'course_id',OLD.course_id,NEW.course_id,NOW());
    END IF;
    
    IF (NEW.status <> OLD.status)
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'status',OLD.status,NEW.status,NOW());
    END IF; 

目前触发器运行正常，但当主表包含大量列时，多IF条件的方式效率较低，想了解是否有更优方法识别最后一次更新中被修改的字段，以及如何实现？

优化方案 #

1. 使用UNION ALL合并插入操作 #

将多个独立的IF+INSERT合并为单条INSERT...SELECT...UNION ALL语句，减少数据库的语句解析和执行开销，同时简化代码结构。

基础实现（字段不允许NULL）

INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`, `new_value`, `created`)
SELECT OLD.id, OLD.orgId, 'courseStartDate', OLD.courseStartDate, NEW.courseStartDate, NOW()
WHERE NEW.courseStartDate <> OLD.courseStartDate
UNION ALL
SELECT OLD.id, OLD.orgId, 'courseEndDate', OLD.courseEndDate, NEW.courseEndDate, NOW()
WHERE NEW.courseEndDate <> OLD.courseEndDate
UNION ALL
SELECT OLD.id, OLD.orgId, 'course_id', OLD.course_id, NEW.course_id, NOW()
WHERE NEW.course_id <> OLD.course_id
UNION ALL
SELECT OLD.id, OLD.orgId, 'status', OLD.status, NEW.status, NOW()
WHERE NEW.status <> OLD.status;

兼容NULL值的实现

如果字段允许NULL，<> NULL的判断逻辑不生效，需要使用MySQL的<=>运算符（安全等于），结合NOT来判断值是否变化：

INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`, `new_value`, `created`)
SELECT OLD.id, OLD.orgId, 'courseStartDate', OLD.courseStartDate, NEW.courseStartDate, NOW()
WHERE NOT(NEW.courseStartDate <=> OLD.courseStartDate)
UNION ALL
SELECT OLD.id, OLD.orgId, 'courseEndDate', OLD.courseEndDate, NEW.courseEndDate, NOW()
WHERE NOT(NEW.courseEndDate <=> OLD.courseEndDate)
UNION ALL
SELECT OLD.id, OLD.orgId, 'course_id', OLD.course_id, NEW.course_id, NOW()
WHERE NOT(NEW.course_id <=> OLD.course_id)
UNION ALL
SELECT OLD.id, OLD.orgId, 'status', OLD.status, NEW.status, NOW()
WHERE NOT(NEW.status <=> OLD.status);

2. 方案优势 #

• 效率提升：单条INSERT语句（带UNION ALL）比多次独立INSERT减少了数据库的执行开销，尤其当列数较多时效果更明显。
• 维护简便：新增字段时仅需追加对应的SELECT分支，无需重复编写INSERT框架代码。
• 逻辑清晰：所有字段的变更判断和日志插入逻辑集中在一处，便于阅读和修改。

关于自动化识别字段的说明 #

如果想要完全自动化（无需手动逐个添加字段），需要结合动态SQL，但MySQL触发器中无法直接使用动态SQL，需配合存储过程实现。不过这种方式复杂度较高，会增加维护成本，对于绝大多数场景，上述UNION ALL的方案已经足够简洁高效。

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