MySQL如何实现modify_on字段排除指定列后随变更自动更新

MySQL原生的ON UPDATE CURRENT_TIMESTAMP列属性没有提供排除指定字段的配置能力，只要行内任意字段值发生变更，就会自动刷新时间戳。要实现「仅指定字段变更才更新时间戳、排除部分字段触发逻辑」的需求，需要先移除列自带的自动更新属性，再通过表触发器自定义判断规则实现。

操作步骤 #

1. 先调整modifyon字段属性，移除自带的自动更新规则，保留插入时默认填充当前时间的逻辑：

ALTER TABLE app_user
MODIFY COLUMN modifyon datetime DEFAULT CURRENT_TIMESTAMP;

2. 创建更新前触发的行级触发器，自定义时间戳更新的判断条件。
   以举例的a、b、c、d四字段场景为例（仅a、b变更时刷新modifyon，c、d变更不触发），触发器写法如下：

-- 临时修改语句分隔符，避免触发器内的分号和SQL语句分隔符冲突
DELIMITER //
CREATE TRIGGER trg_app_user_modifyon_refresh
BEFORE UPDATE ON app_user
FOR EACH ROW
BEGIN
    -- 用NULL安全等值判断，识别a或b字段是否发生值变更
    IF NOT (OLD.a <=> NEW.a AND OLD.b <=> NEW.b) THEN
        SET NEW.modifyon = CURRENT_TIMESTAMP;
    END IF;
END //
-- 恢复原来的语句分隔符
DELIMITER ;

适配实际业务场景的写法 #

需要排除lastlogin、InvalidTryCount字段的变更触发逻辑，只要调整触发器内的判断条件即可，有两种写法可以选：

• 写法1：明确列出所有需要触发更新的字段，只要其中任意一个字段值变化，就刷新时间，适合需要触发的字段少、后续变动不大的场景
• 写法2：明确列出所有不需要触发更新的字段，只要不是「仅这些排除字段发生变化、其余字段完全没动」的场景，就刷新时间，适合需要触发的字段多、排除字段少的场景，更贴合当前需求，对应代码如下：

DELIMITER //
CREATE TRIGGER trg_app_user_modifyon_refresh
BEFORE UPDATE ON app_user
FOR EACH ROW
BEGIN
    -- 判定规则：不是只有排除字段变更的情况，就更新modifyon
    IF NOT (
        -- 下面列所有需要排除的字段，做NULL安全等值判断
        OLD.lastlogin <=> NEW.lastlogin
        AND OLD.InvalidTryCount <=> NEW.InvalidTryCount
        -- 如果后续要加新的排除字段，按上面的格式继续往下加即可
    ) THEN
        SET NEW.modifyon = CURRENT_TIMESTAMP;
    END IF;
END //
DELIMITER ;

注意事项 #

• 判断条件里用的<=>是MySQL的NULL安全等值运算符，就算字段值为NULL也能正确判断是否发生变更，避免普通=判断NULL值时结果未知导致的误触发、漏触发问题。
• 如果后续要调整触发规则，先执行DROP TRIGGER IF EXISTS trg_app_user_modifyon_refresh;删掉旧触发器，再重新创建新的即可。
• 该触发器是行级触发，仅做简单的字段值比对，性能开销极低，不会对正常的表更新操作造成可感知的影响。

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