MySQL 触发器使用场景

我刚工作的时候有个需求每次更新用户表都要记录到审计日志表。我傻乎乎地在应用层写逻辑结果 DBA 看到后直接把我骂了一顿“你这是要把数据库搞垮啊用触发器不就完事了”今天咱们就来聊聊 MySQL 触发器的使用场景看完这篇你就能根据业务场景决定要不要用触发器了。触发器是啥触发器Trigger是数据库的一种自动触发的机制——当某张表发生了INSERT、UPDATE、DELETE操作时会自动执行你定义的一段 SQL 逻辑。基本语法DELIMITER$$-- 在 users 表插入后自动执行CREATETRIGGERuser_after_insertAFTERINSERTONusersFOR EACH ROWBEGIN-- 这里写你的逻辑INSERTINTOuser_audit_log(user_id,action,created_at)VALUES(NEW.id,insert,NOW());END$$DELIMITER;关键点触发时机BEFORE操作前或AFTER操作后触发事件INSERT、UPDATE、DELETENEW 和 OLDNEW新值INSERT/UPDATE后的值OLD旧值UPDATE/DELETE前的值触发器的优点1. 保证数据一致性不用应用层操心场景每次更新users表都要同步更新user_profiles表。不用触发器应用层操心// 应用层要写双份逻辑容易漏publicvoidupdateUser(intuserId,Stringname){// 更新 users 表userDao.updateName(userId,name);// 还要同步更新 user_profiles 表容易漏profileDao.updateName(userId,name);}**用触发器**数据库自动同步 sqlDELIMITER$$CREATETRIGGERsync_profile_after_updateAFTERUPDATEONusersFOREACHROWBEGIN--自动同步更新 user_profiles 表UPDATEuser_profilesSETnameNEW.nameWHEREuser_idNEW.id;END$$DELIMITER;好处应用层不用操心数据库自动同步不会漏。2. 自动记录审计日志不用应用层写场景每次INSERT、UPDATE、DELETE用户表都要记录到审计日志表。不用触发器应用层写// 应用层要写双份逻辑容易漏publicvoidcreateUser(Stringname){// 插入用户userDao.insert(name);// 还要记录审计日志容易漏auditDao.insert(userId,insert,newDate());}**用触发器**数据库自动记录 sql--插入后自动记录审计日志CREATETRIGGERuser_after_insertAFTERINSERTONusersFOREACHROWBEGININSERTINTOuser_audit_log(user_id,action,created_at)VALUES(NEW.id,insert,NOW());END$$--更新后自动记录审计日志CREATETRIGGERuser_after_updateAFTERUPDATEONusersFOREACHROWBEGININSERTINTOuser_audit_log(user_id,action,created_at)VALUES(NEW.id,update,NOW());END$$--删除后自动记录审计日志CREATETRIGGERuser_after_deleteAFTERDELETEONusersFOREACHROWBEGININSERTINTOuser_audit_log(user_id,action,created_at)VALUES(OLD.id,delete,NOW());END$$ **好处**应用层不用操心数据库自动记录不会漏。 ###3.实现复杂约束不用应用层校验**场景**users 表的 email 字段要唯一但 user_profiles 表也要保证 email 唯一跨表约束。**不用触发器**应用层校验有并发问题 java// 应用层校验有并发问题publicvoidcreateUser(Stringemail){// 校验 email 是否唯一有并发问题if(userDao.existsByEmail(email)||profileDao.existsByEmail(email)){thrownewException(email 已存在);}// 插入这里可能有并发问题校验完插入前别的事务插了同个 emailuserDao.insert(email);}**用触发器**数据库层校验无并发问题 sqlDELIMITER$$CREATETRIGGERuser_before_insertBEFOREINSERTONusersFOREACHROWBEGIN--校验 email 是否唯一跨表约束IFEXISTS(SELECT1FROMuser_profilesWHEREemailNEW.email)THENSIGNALSQLSTATE45000SETMESSAGE_TEXTemail 已存在于 user_profiles;ENDIF;END$$DELIMITER;好处数据库层校验无并发问题因为触发器是原子的。触发器的缺点重点1. 性能差每次操作都要执行触发器问题触发器是同步执行的每次INSERT/UPDATE/DELETE都要等触发器执行完性能差。-- 插入用户要等触发器执行完INSERTINTOusers(name)VALUES(Alice);-- 1. 插入 users 表-- 2. 执行触发器比如记录审计日志-- 3. 返回结果-- 如果触发器很慢比如插入审计日志表很慢整个 INSERT 就慢了解决方案触发器逻辑尽量简单别写复杂 SQL。2. 隐藏业务逻辑不直观问题触发器是数据库层的逻辑应用层看不到不直观。-- 应用层只看到 INSERT看不到触发器逻辑userDao.insert(Alice);-- 但实际上数据库会自动记录审计日志触发器-- 新人接手代码时可能不知道有触发器调试半天解决方案重要业务逻辑别用触发器用应用层或存储过程触发器只用来做辅助性的、不影响主流程的逻辑比如记录审计日志。3. 可能导致死锁并发问题问题触发器里如果要更新其他表可能和别的事务互相等待导致死锁。-- 触发器更新 users 表后自动更新 orders 表CREATETRIGGERuser_after_updateAFTERUPDATEONusersFOR EACH ROWBEGIN-- 更新 orders 表可能和别的事务互相等待导致死锁UPDATEordersSETuser_nameNEW.nameWHEREuser_idNEW.id;END$$**解决方案**触发器逻辑尽量简单别更新其他表或者保证更新顺序一致。 ### 4. 难以调试出错了很难查 **问题**触发器是**数据库层**的逻辑出错了很难调试不像应用层可以打日志、断点调试。 sql-- 触发器出错了只能看 MySQL 错误日志很难调试SHOWTRIGGERSLIKEuser%;-- 如果触发器里有 SIGNAL主动报错错误信息也不直观解决方案重要业务逻辑别用触发器用应用层或存储过程触发器只用来做辅助性的逻辑。实战触发器使用场景场景 1记录审计日志推荐最合适用触发器的场景记录审计日志不影响主流程逻辑简单。-- 创建审计日志表CREATETABLEuser_audit_log(idINTPRIMARYKEYAUTO_INCREMENT,user_idINT,actionVARCHAR(10),created_atDATETIME);-- 插入后自动记录审计日志DELIMITER$$CREATETRIGGERuser_after_insertAFTERINSERTONusersFOR EACH ROWBEGININSERTINTOuser_audit_log(user_id,action,created_at)VALUES(NEW.id,insert,NOW());END$$DELIMITER;好处不用应用层操心数据库自动记录不会漏应用层可能忘写性能影响小只是插入一条日志场景 2自动计算字段比如更新时间戳合适用触发器的场景自动计算字段比如更新时间戳。-- 更新用户后自动更新 updated_at 字段DELIMITER$$CREATETRIGGERuser_before_update BEFOREUPDATEONusersFOR EACH ROWBEGIN-- 自动设置 updated_at 为当前时间SETNEW.updated_atNOW();END$$DELIMITER;好处不用应用层操心数据库自动更新时间戳。场景 3实现复杂约束比如跨表唯一性校验合适用触发器的场景实现复杂约束比如跨表唯一性校验。-- 保证 users 和 user_profiles 的 email 唯一跨表约束DELIMITER$$CREATETRIGGERuser_before_insert BEFOREINSERTONusersFOR EACH ROWBEGINIFEXISTS(SELECT1FROMuser_profilesWHEREemailNEW.email)THENSIGNAL SQLSTATE45000SETMESSAGE_TEXTemail 已存在于 user_profiles;ENDIF;END$$DELIMITER;好处数据库层校验无并发问题。场景 4同步更新其他表谨慎不太合适用触发器的场景同步更新其他表性能差可能死锁。-- 更新用户后自动更新订单表的用户姓名不合适DELIMITER$$CREATETRIGGERuser_after_updateAFTERUPDATEONusersFOR EACH ROWBEGIN-- 同步更新订单表的用户姓名性能差可能死锁UPDATEordersSETuser_nameNEW.nameWHEREuser_idNEW.id;END$$DELIMITER;问题性能差每次更新用户都要更新订单表可能死锁如果订单表也有触发器可能互相等待解决方案别用触发器用应用层或MQ消息队列异步同步。实战建议1. 记录审计日志 → 可以用触发器这是最合适用触发器的场景不影响主流程逻辑简单。-- 记录审计日志CREATETRIGGERuser_after_insertAFTERINSERTONusersFOR EACH ROWBEGININSERTINTOuser_audit_log(user_id,action,created_at)VALUES(NEW.id,insert,NOW());END$$### 2. 自动计算字段 → 可以用触发器 **比如**自动更新时间戳、自动计算年龄根据生日。 sql-- 自动更新时间戳CREATETRIGGERuser_before_update BEFOREUPDATEONusersFOR EACH ROWBEGINSETNEW.updated_atNOW();END$$### 3. 实现复杂约束 → 可以用触发器 **比如**跨表唯一性校验、复杂业务规则校验。 sql-- 跨表唯一性校验CREATETRIGGERuser_before_insert BEFOREINSERTONusersFOR EACH ROWBEGINIFEXISTS(SELECT1FROMuser_profilesWHEREemailNEW.email)THENSIGNAL SQLSTATE45000SETMESSAGE_TEXTemail 已存在;ENDIF;END$$### 4. 同步更新其他表 → 别用触发器用应用层或 MQ **不合适**用触发器的场景**同步更新其他表**性能差可能死锁。 **用应用层** javapublicvoid updateUser(intuserId,String name){// 更新用户userDao.updateName(userId,name);// 同步更新订单表的用户姓名应用层控制orderDao.updateUserName(userId,name);}**或者用 MQ异步同步**更新用户 → 发 MQ 消息 → 订单服务异步更新订单表的用户姓名### 5. 重要业务逻辑 → 别用触发器用应用层或存储过程 **不合适**用触发器的场景**重要业务逻辑**隐藏业务逻辑难以调试。 **用应用层**直观好调试 java public void createUser(String name) { // 重要业务逻辑应用层写直观好调试 userDao.insert(name); auditDao.insert(userId, insert, new Date()); } ## 总结 - **触发器**是数据库的一种**自动触发**的机制当某张表发生了 INSERT、UPDATE、DELETE 操作时会自动执行你定义的一段 SQL 逻辑 - - **触发器的优点**保证数据一致性、自动记录审计日志、实现复杂约束 - - **触发器的缺点**性能差、隐藏业务逻辑、可能导致死锁、难以调试 - - **触发器使用场景** - 1. **记录审计日志**推荐 - 2. **自动计算字段**比如更新时间戳 - 3. **实现复杂约束**比如跨表唯一性校验 - 4. **同步更新其他表**谨慎性能差可能死锁 - - **实战建议**记录审计日志可以用触发器、自动计算字段可以用触发器、实现复杂约束可以用触发器、同步更新其他表别用触发器用应用层或 MQ、重要业务逻辑别用触发器用应用层或存储过程 如果你能把触发器的优缺点、使用场景讲清楚面试官绝对觉得你有实战经验。 --- **实战代码都在我本地跑过你可以放心复制。** 如果有问题欢迎评论区交流