1. 触发器是什么为什么需要触发器1.1 触发器的定义与适用场景先直接说结论MySQL触发器Trigger是一种“挂在表上的自动程序”。你在某张表上执行INSERT、UPDATE、DELETE操作时如果提前给这张表定义了触发器那么这些操作发生的前后MySQL会自动执行一段你预设的SQL逻辑不需要应用程序额外调用。这个机制对很多业务场景特别有用。比如电商系统里你往订单表里插入一条新订单库存表就应该同步扣减数量常规做法是在Java或PHP代码里写两行SQL先插入订单再更新库存。但只要某个接口漏了更新库存或者事务中途报错库存数据就可能不一致。而触发器能把“插入订单后自动更新库存”这件事固化在数据库层面无论哪个入口写入数据只要命中触发条件逻辑就一定会执行。什么场景适合用触发器我简单梳理了一下数据一致性维护订单和库存、主表和明细表之间的同步。审计日志记录谁在什么时候改了哪条数据自动记到日志表。数据校验在数据入库前做一层兜底检查不满足条件就报错。复杂默认值根据某几个字段的组合自动计算新字段的值。防误删把物理删除拦截下来改成写入删除标记。1.2 触发器的类型与触发时机MySQL触发器按触发事件分三类INSERT、UPDATE、DELETE。按触发时机分两类BEFORE操作前和AFTER操作后。组合起来一共有6种触发事件BEFORE操作前AFTER操作后INSERTBEFORE INSERTAFTER INSERTUPDATEBEFORE UPDATEAFTER UPDATEDELETEBEFORE DELETEAFTER DELETE理解BEFORE和AFTER的差别是选型的关键。BEFORE触发器在数据实际写入或修改之前触发此时你可以读取到“即将写入的值”也能直接修改它们。最常见的用法是数据校验和字段预处理。比如年龄字段不允许超过150插入前发现超了就直接报错或者某字段没填值时在BEFORE里补一个默认值。AFTER触发器在数据操作完成之后触发此时数据已经落库。它适合做“基于最终结果的后续动作”比如插入订单成功后再去扣库存、更新用户资料后记录日志。因为只有操作成功AFTER触发器才会执行所以不用担心数据还没写进去就去处理导致的信息错位。1.3 触发器的优缺点与使用边界说实话触发器在业内是个有争议的东西。支持的人认为它能把数据逻辑收敛在数据库内部保证一致性反对的人觉得它会藏逻辑出了问题不好排查。我的态度是触发器是好工具但得用在合适的场合不能滥用。触发器最大的优点是隐式自动执行。不管上游是Web应用、定时任务、数据导入工具还是手动SQL只要操作了表触发逻辑就一定跑不存在“忘记调用”导致数据不一致的问题。但它也有明显的缺点。第一个是排障成本高。如果你接手一个老项目发现更新用户表时莫名其妙多了一条日志翻了半天代码找不到是谁写的最后发现是表上挂了一个没人文档化的触发器这种体验相当痛苦。第二个是性能开销。每次DML操作都会额外执行触发逻辑如果触发逻辑里又查了大表或者写了其他表对写入性能影响很明显。第三个是难以调试。触发器里的报错信息相对简单不像应用程序代码有完整的堆栈日志。所以在设计时我建议把触发器用在“短小、固定、无争议”的逻辑上。例如字段默认值、简洁审计日志、只涉及当前行或少量关联数据的同步。复杂业务逻辑不要硬塞进触发器那个应该交给应用程序处理。2. 触发器语法逐段拆解2.1 CREATE TRIGGER完整语法创建触发器的核心语法是CREATE TRIGGER标准写法如下CREATE TRIGGER 触发器名 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名 FOR EACH ROW 触发器主体;拆开来看触发器名必须唯一建议带业务前缀比如trg_order_after_insert、trg_user_before_update方便后期管理。BEFORE | AFTER触发时机。INSERT | UPDATE | DELETE监听事件。ON 表名触发器挂在哪张表上。FOR EACH ROW行级触发器意思是每一行受影响都会执行一次。MySQL目前只支持行级触发器不支持语句级触发器所以这个子句是固定写法。触发器主体需要执行的SQL逻辑可以是一段简单SQL也可以是BEGIN...END包裹的复杂逻辑块。需要注意一个细节FOR EACH ROW前面和后面没有逗号也不要加括号这是初学者最容易犯的语法错误。2.2 触发器体分隔符与BEGIN...END块如果触发器只需要执行一条SQL直接在CREATE TRIGGER后面写这一条SQL即可。例如CREATE TRIGGER trg_user_after_insert AFTER INSERT ON users FOR EACH ROW INSERT INTO user_logs(user_id, action) VALUES (NEW.id, INSERT);但如果需要执行多条SQL就必须用BEGIN...END把多条语句包起来同时用DELIMITER修改MySQL客户端的语句分隔符。因为BEGIN...END内部有多条语句每条语句以分号结尾如果不临时改掉客户端的分隔符MySQL会在第一个分号处就误认为CREATE TRIGGER语句结束从而报语法错误。典型写法DELIMITER // CREATE TRIGGER trg_user_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO user_logs(user_id, action) VALUES (NEW.id, INSERT); UPDATE user_stats SET total_users total_users 1; END// DELIMITER ;DELIMITER //的意思是告诉mysql客户端从现在开始用//作为一条完整语句的结束标志而不是用分号。然后在写完整个触发器后用//结束最后再DELIMITER ;把分隔符改回来。这个操作强烈建议每次用完就还原不然你后续执行普通SQL时分号不再作为结束符很容易手忙脚乱。2.3 OLD与NEW关键字详解这是触发器语法里最重要的概念。触发器本质上是在监听行数据的变化那么触发器体里如何引用“变化前”和“变化后”的数据呢答案就是OLD和NEW。NEW表示新插入的行或者UPDATE之后的新值。OLD表示UPDATE之前旧的行或者DELETE之前被删除的行。各事件下OLD和NEW的可用情况触发事件OLDNEWINSERT不可用可用表示即将/已经插入的新行UPDATE可用表示修改前的旧行可用表示修改后的新行DELETE可用表示被删除的旧行不可用在BEFORE触发器中NEW.字段名是可以直接赋值的修改后的值会真正写入表中的这一行。这个特性非常有用相当于给了你一个“在数据落库前做手脚”的机会。例如DELIMITER // CREATE TRIGGER trg_user_before_insert BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.age 0 THEN SET NEW.age 0; END IF; END// DELIMITER ;这个触发器会在插入users表前检查age字段如果传入负数就强制改成0。需要理解的是这里修改的是NEW.age而不是直接UPDATE表数据因为此时数据还没插入UPDATE表本身反而会引发新的触发链路逻辑上说不通。OLD在BEFORE和AFTER触发器中都只能读取不能修改。这一点也好理解修改前的行已经是历史状态了你不能指望通过触发器“篡改历史”。2.4 删除与查看触发器的语法触发器管理和表管理类似凡是创建了的东西都要能查、能删。查看当前数据库下所有触发器SHOW TRIGGERS;查看某个表上的触发器SHOW TRIGGERS LIKE users%;查看触发器的创建语句可以精确看到它的原始定义SHOW CREATE TRIGGER trg_user_after_insert;删除触发器DROP TRIGGER trg_user_after_insert;需要注意删除表DROP TABLE时会自动删除该表上的所有触发器但是删除数据库DROP DATABASE时不会自动删除所有触发器——MySQL在这一点上有个历史遗留的坑如果你先删库再重建同名库旧触发器还可能残留。所以我建议每次清理环境时用SHOW TRIGGERS确认一下。2.5 触发器中的变量与流程控制在BEGIN...END块里除了简单的SET赋值和IF判断还可以使用局部变量、CASE分支、循环等。虽然触发器不适合写太重的逻辑但掌握这些语法能应对不少实际需求。声明变量用DECLARE变量赋值可以直接用SET也可以从SELECT结果里取DELIMITER // CREATE TRIGGER trg_order_after_insert AFTER INSERT ON orders FOR EACH ROW BEGIN DECLARE current_stock INT; SELECT stock_count INTO current_stock FROM products WHERE id NEW.product_id; IF current_stock NEW.quantity THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足; END IF; END// DELIMITER ;这里用了SIGNAL SQLSTATE 45000来主动抛错。45000是MySQL约定的通用用户自定义异常状态码配合MESSAGE_TEXT可以给应用层返回一段明确的中文错误提示。这个操作在BEFORE触发器中特别常用相当于在数据库层面拦截非法数据。3. 实用例子从零写一个触发器3.1 例子1订单插入后自动扣减库存这是触发器最经典的应用场景。假设有两张表orders订单表和products产品表。订单表记录下单时买了哪个产品、买了多少件产品表记录库存。我要做的是每次往orders表插入一条订单记录时自动把products表里对应产品的库存扣掉。表结构先准备好CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, stock_count INT NOT NULL DEFAULT 0 ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, quantity INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );对应的AFTER INSERT触发器DELIMITER // CREATE TRIGGER trg_order_after_insert AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE products SET stock_count stock_count - NEW.quantity WHERE id NEW.product_id; END// DELIMITER ;这个触发器执行流程是这样的用户向orders表插入一行MySQL先插入成功然后自动运行UPDATE语句把对应product_id的库存减去订单数量。整个过程对应用层透明应用代码只需要关心INSERT订单这一件事。实际使用时还需要考虑两个细节。第一扣减库存时最好加个判断防止库存变成负数。如果库存充足正常扣减如果不足应该让这次插入失败。这个可以在BEFORE INSERT里做因为此时数据还没写入报错可以让整条INSERT回滚。第二库存扣减发生在AFTER如果UPDATE失败订单本身可能已经提交了这会造成数据不一致。更稳妥的做法是放在BEFORE里先检查并扣减库存再插入订单。这里我用AFTER是为了演示基础写法真实生产环境建议用“BEFORE检查 AFTER动作”的组合或者把库存变更放在同一个事务里通过程序控制。3.2 例子2更新用户表时自动记录修改日志审计日志是触发器用得非常多的地方。比如users表记录了用户信息领导要求任何时候有人改了用户资料都必须留下记录谁改的、哪条记录、改之前什么值、改之后什么值、什么时候改的。表结构CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(200) NOT NULL, updated_at DATETIME DEFAULT NULL ); CREATE TABLE user_change_logs ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, old_email VARCHAR(200), new_email VARCHAR(200), changed_at DATETIME DEFAULT CURRENT_TIMESTAMP );UPDATE触发器会同时拿到OLD和NEW所以非常适合做前后值对比DELIMITER // CREATE TRIGGER trg_user_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO user_change_logs(user_id, old_email, new_email) VALUES (OLD.id, OLD.email, NEW.email); END// DELIMITER ;这个触发器不关心你更新了哪些字段只要UPDATE语句真的改了行它就会记录一条日志。这里我特意只对比了email字段的变化如果你想记录所有字段可以把每个字段的OLD和NEW都写进日志表。一个值得注意的细节在MySQL中如果UPDATE语句的SET值没有实际改变任何数据比如把name字段从张三改成张三触发器默认还是会被执行除非你在WHERE里提前过滤。所以在设计日志表时最好在触发器里加上一句判断IF OLD.email NEW.email OR OLD.name NEW.name THEN INSERT INTO user_change_logs(user_id, old_email, new_email) VALUES (OLD.id, OLD.email, NEW.email); END IF;这样能避免大量无意义的日志写入。3.3 例子3防止误删的“软删除”触发器业务数据通常不建议物理删除。但开发过程中难免有手滑执行DELETE的情况或者某个外部工具直接执行了删除操作绕过了应用层的“软删除”逻辑。触发器可以在数据库层面托底把DELETE转换成标记删除。假设用户表增加一个is_deleted标记ALTER TABLE users ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0;由于DELETE触发器里OLD可读但不可改所以不能直接在BEFORE DELETE里把“DELETE”这个操作本身变成UPDATE。我的做法是彻底禁用DELETE操作一旦有人对users表执行DELETE直接抛错从根上杜绝误删。DELIMITER // CREATE TRIGGER trg_users_before_delete BEFORE DELETE ON users FOR EACH ROW BEGIN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 禁止物理删除用户数据请使用软删除; END// DELIMITER ;如果你希望“表面上执行DELETE实际上改成软删除”那需要结合视图或改程序逻辑纯触发器做不到无缝拦截DELETE并篡改为UPDATE。这里说清楚避免踩坑。生产环境里我更推荐“BEFORE DELETE直接报错”的做法把物理删除这条路彻底堵死配合应用层的软删除接口才最稳妥。3.4 例子4字段合法性校验触发器还有一种很实用的场景对写入数据做合法性校验。比如用户表有个age字段年龄范围应该在0到150之间。应用层可以校验但万一应用层漏了校验数据就直接进库了。用BEFORE INSERT触发器做兜底校验可以保证非法数据无法落库。DELIMITER // CREATE TRIGGER trg_user_before_insert BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.age 0 OR NEW.age 150 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 年龄必须在0到150之间; END IF; IF NEW.email NOT LIKE %% THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 邮箱格式不正确; END IF; END// DELIMITER ;这里的思路是在BEFORE阶段做检查一旦不满足条件就抛异常中止INSERT操作。应用层会收到SQL异常可以根据里面的MESSAGE_TEXT做相应提示。需要注意SIGNAL在BEFORE触发器里使用效果最好因为此时数据还没写入抛错可以直接让整条INSERT回滚。在AFTER触发器里虽然也能抛错但数据已经写进去了回滚会带来更多事务开销排查问题也更复杂所以校验逻辑尽量都放BEFORE。4. 常见问题与排查技巧4.1 触发器不执行的6个常见原因触发器写好了测试时发现没生效这种问题我遇到太多次了。总结下来最常见的原因有这几个1. 触发器事件弄混了。想在UPDATE时记录日志结果触发器创建成了AFTER INSERT自然不执行。排查时先看SHOW TRIGGERS确认触发事件。2. 表名写错了触发器挂在了别的表上。创建触发器时ON后面的表名和实际业务操作的表名不一致。这种情况在复制粘贴修改时特别容易发生。3. 权限不足。当前用户没有触发器对应表的权限尤其是涉及跨表操作时。比如触发器要UPDATE products表但当前账号没有这个权限触发器执行会静默失败。4. 操作被事务回滚了。触发器里的SQL执行失败不会单独回滚而是跟随外层事务一起回滚。如果外层事务最终ROLLBACK你看到的效果就是触发器“白执行了”。5. 变量名或字段名拼写错误。触发器创建时MySQL不会百分百检查内部引用的字段名运行到那一句才报错。所以写完触发器最好先找一个真实匹配的数据测试。6. BEFORE和AFTER选错了。想在插入前改值却建了AFTER触发器此时修改NEW已经来不及了因为数据已经落库。排查不执行问题我最常用的方法是先看SHOW TRIGGERS确认触发器和表确实存在再手动执行一条对应类型的DML最后去检查目标表数据是否变化或者查看MySQL错误日志。如果触发器内部有语法级或运行时错误错误日志里通常会记录。4.2 触发器的性能开销怎么控制触发器的性能问题是很多人担心的重点实际情况也确实需要注意。MySQL触发器每行受影响都会执行一次如果你对一张表做UPDATE ... WHERE id IN (1000个id)触发器就要执行1000次这是行级触发的固有限制。控制性能我总结了几个原则原则一触发器体里不要查大表。比如在AFTER INSERT触发器里写一句SELECT COUNT(*) FROM orders去统计总订单数每次插入都扫全表表一大了性能立刻崩。这种统计逻辑应该用缓存或异步任务去做。原则二尽量只操作当前行相关数据。触发器最擅长的是通过NEW.id、OLD.id这些关联条件去操作少量数据。一次触发里UPDATE一个主键索引命中的行开销很小UPDATE一张全表那就是灾难。原则三不要在触发器里调存储过程或函数。虽然语法上允许但这种方式会把调用链拉长出问题时很难追踪。而且存储过程内部如果再触发其他表的DML会形成隐式嵌套调用执行计划更难把控。原则四对高频写入的表慎用触发器。日志表、流水表这类写入频繁的表可以考虑用应用程序逻辑替代触发器减少每次写入的额外开销。4.3 调试触发器的实用技巧触发器没有图形化调试器也没有单步执行。我的调试方法比较传统但非常有效。第一先小步验证。不要一上来就写一个50行的复杂触发器。先写一个最简单的比如只插入一条日志跑通后再加上判断逻辑一点点加出错时能快速定位是哪一句引入的问题。第二在触发器里“埋点”。触发器本身支持使用SIGNAL抛异常除了抛业务错误也可以临时在AS的消息里打印变量值。比如IF NEW.age 150 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 调试: age CAST(NEW.age AS CHAR); END IF;这样手动执行INSERT时能通过错误信息看到中间变量到底是什么值相当于printf调试。第三检查错误日志。SHOW WARNINGS在触发器执行出错时能显示一部分信息MySQL错误日志文件也会记录更详细的报错。尤其在触发器内部报错时错误日志里的表述比客户端提示更完整。第四确认字符集和排序规则。触发器里做字符串比较或拼接时如果表的字符集不一致可能产生乱码或比较结果异常。这属于隐形坑出现诡异情况时可以往这个方向排查。4.4 关于递归触发与嵌套触发MySQL默认情况下一张表上的触发器操作了另一张表而另一张表上也有触发器就会形成嵌套触发。比如users表的AFTER UPDATE触发器往logs表插入日志logs表上又有AFTER INSERT触发器这个链会继续往下走。MySQL对递归触发触发器自己触发自己有一个开关控制默认是关闭的。简单说你不能在某个表上的触发器中再执行同一张表的DML否则会报错。但A表触发器操作B表、B表触发器操作A表这种情况可能形成循环依赖需要自己通过逻辑判断来避免。经验是不要让触发器之间形成链条。每个触发器最好只操作不挂触发器的普通表或者操作当前表的BEFORE自链路。如果业务逻辑需要多级联动应该放到应用程序里用事务控制比数据库触发器更直观可控。5. 写在最后关于触发器的几点个人体会用了这么多年MySQL触发器算是我又爱又恨的一个功能。爱的是它确实能在关键时刻兜底比如数据校验、防误删、审计日志这些场景靠触发器可以省掉很多应用层的重复代码恨的是它“藏得深”如果团队协作时不做好文档记录后来维护的人很容易被莫名其妙的数据变化搞到崩溃。我个人的实践体会是用触发器时请务必做好两件事。第一命名规范和说明文档一定要跟上触发器名带上业务含义创建后在项目的数据库文档里写明每个触发器的用途、触发时机、涉及的逻辑给人留条活路。第二不要在触发器里写复杂的业务规则复杂逻辑放应用层触发器只干那些“短平快”的事。每次DML都执行的东西写重了就是性能炸弹。最后再分享一个小技巧生产环境改动触发器前一定要先在测试库完整验证并备份旧的触发器定义。因为MySQL的触发器是替换式管理的同一张表同一事件的触发器只能有一个如果多个团队分别建了同类型触发器后建的会覆盖先建的这种坑一旦踩到恢复起来要花不少精力。希望这篇关于MySQL触发器语法与实例的总结能帮你少踩几个坑把触发器这个工具用得顺手又稳妥。