MySQL 触发器：功能、实例与注意事项全解析

欢迎大家来到IT世界,在知识的湖畔探索吧!

MySQL 触发器就是存放在数据库目录里的一组 SQL 语句，要是跟表有关的事儿发生了，像插入、更新或者删除，它就自己执行。可以把它当成是一种特别的存储过程，不过跟存储过程不一样，触发器不用特意去叫它，在特定的事儿发生的时候自己就触发了。

触发器有下面这些特点：

自动执行：触发器是数据库里头自动干活的工具，特定的数据库操作一有，它就自己动起来，不用人去手动叫它。

马上反应：触发器在事儿发生的时候马上就执行，适合那种得马上处理数据变化的情况。

保证数据一致：触发器能在数据操作的时候自己检查和修好数据，不让不一致或者错的数据插进去。

跟表操作绑一块：触发器跟特定的表操作关系可紧密了，常常用来处理复杂的业务逻辑和数据变更的跟踪。

（一）啥是MySQL触发器

触发器（Trigger）是一种特别的存储过程，它跟表有关系，当表上特定的事儿（insert，update，delete）来了，触发器就自己动起来。能拿触发器来弄数据约束、数据验证、数据复制这些功能。

比如说：能弄一个触发器，往表里头插一条记录的时候，自动往另一个表里头也插一条记录，这么着就能实现数据复制的功能。触发器还能用来实现数据验证，比如说在插或者更新数据的时候，查查记录的某些字段合不合要求，要是不合，就不让插或者更新，这么着就能保证数据完整和一致。

（二）为啥要用触发器

能实现数据约束、数据验证、数据复制这些功能。

比如说：

能弄一个触发器，往表里头插一条记录的时候，自动往另一个表里头也插一条记录，这么着就能实现数据复制的功能。

触发器还能用来弄数据验证，比如说在插或者更新数据的时候，查查记录的某些字段合不合要求，要是不合，就不让插或者更新，这么着就能保证数据的完整和一致。

（三）创建触发器

语法：createtrigger 触发器名称{before |after } {insert|update|delete} — 触发器类型和事件 on 表名称 for each row– 触发器的作用范围 begin– 触发器执行的操作 end;

触发器名称：能自己定，得是唯一的，让人一看名字就知道是啥意思。

before / after ：这表示触发器的类型，分别是在发生前/发生后执行。

insert / update / delete ：这表示触发器的事件类型，分别是插入/更新/删除操作。

on 表名称：这是触发器所在的表的名字。

for each row ：这表示触发器起作用的范围，就是每一行记录都会让这个触发器启动。

begin 和 end 中间：是触发器要执行的操作，可以是一条或者好几条 SQL 语句。

触发器是自己就执行的，不用人去手动叫它，当表上特定的事儿（insert, update, delete ) 来了，触发器就自己动起来。在创建触发器的时候，能定触发器的类型和事件，这样就能控制触发器启动的时间和条件。

案例：

• 创建两张表：

createtable table1(id int,name varchar(20)characterset utf8)charset= utf8; createtable table2(id int,name varchar(20)characterset utf8)charset= utf8;

欢迎大家来到IT世界,在知识的湖畔探索吧!

• 添加事件：

欢迎大家来到IT世界,在知识的湖畔探索吧!-- 创建一个触发器，当向表中插入一条记录时，自动向另一个表中插入一条记录 createtrigger insert_trigger_1afterinserton table1for each rowbegin -- 触发器的具体事件 insertinto table2(id,name)values(NEW.id,New.name); end;

添加数据：向 table1 表中添加数据时，会触发 insert_trigger_1 触发器，自动向 table2 表中添加数据。

insertinto table1(id,name)values(1,'kobe'),(2,'lebron'),(3,'curry'),(4,'durant'),(5,'paul'),(6,'westbrook');

查询 table1 表：

欢迎大家来到IT世界,在知识的湖畔探索吧!select*from table1;

查询 table2 表：

select*from table2;

• 删除事件：

-- 创建一个触发器，当从表中删除一条记录时，自动从另一个表中删除一条记录 createtrigger delete_trigger_1afterdeleteon table1for each rowbegindeletefrom table2 where id = OLD.id and name = OLD.name;end;

删除数据：删除 table1 表中数据时，会触发 delete_trigger_1 触发器，自动删除 table2 表中对应的数据。

deletefrom table1 where id =5and name ='paul';

查看两张表中的数据：

select*from table1; select*from table2;

• 修改事件：

-- 创建一个触发器，当向表中更新一条记录时，自动更新另一个表中的记录 createtrigger update_trigger_1 after update on table1 for each row begin update table2 set id = NEW.id, name = NEW.name where id = OLD.id and name = OLD.name; end;

修改数据：修改 table1 表中数据时，会触发 update_trigger_1 触发器，自动更新 table2 表中对应的数据。

update table1 set id =10,name ='paul'where id =6and name ='westbrook';

select*from table1; select*from table2;

（四）删除触发器

语法：drop trigger 触发器名称；

（五）触发器的应用场景

触发器能用到的地方包括但不限于下面这几种：

强制推行业务规则：在触发器里写好逻辑，能在特定的表上自动执行那些业务规则，比如说查查输进去的数据合不合要求，或者限制某些操作去执行。

记录日志的变更：在触发器里写好逻辑，能在特定的表上自动把数据的变更情况记下来，比如说记下数据修改的时间、修改的人这些信息。

复杂的默认值计算：在触发器里写好逻辑，能在特定的表上自动算出默认值，比如说按照其他字段的值算出一个新字段的值。

数据同步：在触发器里写好逻辑，能在好几个表之间自动让数据同步，比如说在一个表里头插一条数据的时候，自动在另一个表里头也插相应的数据。

数据校验：在触发器里写好逻辑，能在特定的表上自动检查数据对不对，比如说查查数据是不是唯一的、完不完整这些。

（六）生成列

在 MySQL 5.7 以前，要是想在表上特定的事儿（insert，update，delete）发生的时候，自动去操作对应的逻辑，只能靠触发器来弄。在 MySQL 5.7 以后能用生成列来弄，生成列（也叫计算列或者虚拟列）。

假设，现在有这样的需求，现在有一列叫 date_time 的数据，在我插或者更新这列数据的时候，会自动把 date 那部分的数据截出来，放到 date 列里。比如说，date_time 列插进去的数据是 2024 – 07 – 18T13:40:30 ，date 列就会自动生成 2024 – 07 – 18 。

1. Trigger 做法：

• 首先创建表：

create table date_trigger(id int,date_time DATETIME,dateDATE)charset= utf8;

• 插入 Trigger 脚本：

-- 创建 BEFORE INSERT 触发器 DELIMITER// CREATETRIGGER before_insert_date_triggerBEFORE INSERTON date_triggerFOR EACH ROWBEGIN SET NEW.date= CAST(NEW.date_time ASDATE); END;// DELIMITER; -- 创建 BEFORE UPDATE 触发器 DELIMITER// CREATETRIGGER before_update_date_triggerBEFORE UPDATEON date_triggerFOR EACH ROWBEGIN SET NEW.date= CAST(NEW.date_time ASDATE); END;// DELIMITER;

二、MySQL 触发器的优点

自动化：触发器自己就执行，不用特意去叫，能少点手动操作。

MySQL 触发器在特定的事儿发生的时候自己就触发执行，不用开发的人去手动叫相应的代码。比如说，往一个表里插数据的时候，触发器能自己去执行一系列的操作，像更新其他表里的数据或者记日志啥的，大大减少了手动操作的工作量，把开发的效率提上去了。

数据一致性：能靠触发器来弄一些跨表的数据同步、更新和删除，保证数据是一致的。

在实际用的时候，可能有好几个表之间有数据关联。通过触发器，在对一个表操作的时候，能自动把相关表的数据同步了，保证数据是一致的。比如说，在订单表里头插一条新订单的时候，能通过触发器自动把库存表里头的库存数量更新了，保证库存数据跟订单数据是一致的。

数据审计：能用触发器把数据修改的历史记下来，方便审计。

触发器能记下数据修改的历史，包括修改前的值、修改后的值、修改的时间、修改的人这些信息。这对数据审计可有用了，能追查到数据变化的过程，保证数据的安全和合规。比如说，在用户表里头，能通过触发器记下用户信息的修改历史，方便管理员去审计。

业务逻辑封装：有些业务逻辑能封在数据库这层，少点应用层的重复代码。

一些复杂的业务逻辑能通过触发器封在数据库这层，别在应用层反复写代码。这样不光能让代码更好维护，还能少点应用层的负担，把系统的性能提上去。比如说，在电商系统里，能通过触发器实现订单状态的自动更新，订单状态一有变化，就自动更新相关表里的数据，少点应用层的代码复杂程度。

三、MySQL 触发器的实例

假设你有个叫 orders 的表，每次插进一条新订单的时候，得在 log 表里头记一条日志。能这么用下面这个触发器：创建触发器：CREATE TRIGGER after_order_insert AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO log (message, created_at) VALUES (‘New order added’,NOW ()); END; 这个触发器在 orders 表有新订单插进去的时候自己就触发了，往 log 表里头插进一条记录，记下新订单的添加信息，方便后面去跟踪订单和查日志。

在更新 users 表的 name 的时候，同时也更新 comment 表：创建触发器：CREATE TRIGGER updatename AFTER UPDATE ON users FOR EACH ROW BEGIN IF new.name!=old.name THEN UPDATE comment set comment.name=new.name where comment.u_id=old.id; END IF; END; 这个触发器能保证在 users 表的 name 字段变了的时候，comment 表里头跟它有关联的记录也能同步把 name 字段更新了。比如说，当用户改了自己的用户名的时候，这个用户在 comment 表里头的所有评论的用户名也会自己更新，保持数据是一致的。

当用户被删掉的时候，把 comment 表里头有关这个用户的数据删掉：创建触发器：CREATE TRIGGER deletecomment BEFORE DELETE ON users FOR EACH ROW BEGIN DELETE FROM comment WHERE comment.u_id=old.id; END; 这个触发器在 users 表里头用户的记录被删掉的时候自己就触发了，把 comment 表里头跟这个用户有关的评论数据清理掉。这在实际用的时候可有用啦，比如说当用户把账号注销的时候，就得把这个用户在系统里留下的所有有关的数据都清理掉，让数据库更干净，数据更准。

四、MySQL 触发器的使用场景

数据完整性的维护：在插进新记录之前用触发器来验证数据，要是不符合定好的条件，就不让插。比如说，在一个电商系统里，当用户下单的时候，能使触发器查查库存数量够不够。要是库存不够，触发器就能不让插订单记录，这么着就能保证数据是完整的。

数据同步与备份：主数据库更新的时候，靠触发器自动把数据同步到备份数据库里。就像在一个企业级的应用里，主数据库里的客户信息有变化的时候，触发器能自动把更新完的数据同步到备份数据库里，免得数据丢了。

数据变更的记录：在触发器触发的时候能插进一行记录到变更历史表里头，记下数据变更的情况。比如说，在一个员工管理系统里，能创建一个触发器，员工信息表一更新，就把更新前和更新后的信息记到变更历史表里头，方便后面审计和追查。

自动更新相关数据：在更新一张表的某个字段的时候，能使触发器自动更新另一张表里头跟它有关的字段。比如说，在一个商品管理系统里，商品的价格一有变化，触发器就能自动更新订单表里头这个商品的总价字段。

五、MySQL 触发器的使用方法

创建触发器：

创建触发器的语法是 CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_body 。这里头，trigger_name 是触发器的名儿，得是唯一的，还得让人一看就明白啥意思；trigger_time 是触发器执行的时间，能是 BEFORE 或者 AFTER ；trigger_event 是触发器的事儿，能是 INSERT 、UPDATE 或者 DELETE ；table_name 是触发器在的那个表的名儿；trigger_body 是触发器的 SQL 语句。比如说，能创建一个触发器，往一个表里插一条记录的时候，自动往另一个表里也插一条记录，这么着就能实现数据复制的功能。

触发器的执行时间：

触发器能在 INSERT 、UPDATE 或者 DELETE 这些事儿之前或者之后执行。BEFORE 触发器在操作执行之前干活，可以做数据的提前处理和验证。比如说，在员工表上弄一个 BEFORE INSERT 的触发器，有新员工进来的时候，自动把入职时间设成现在的时间。AFTER 触发器在操作执行之后干活，可以做数据的后面处理和计算。比如说，在订单表上弄一个 AFTER INSERT 的触发器，有新订单进来的时候，自动把产品的库存量更新了。

触发器的事件：

触发器能在 INSERT 、UPDATE 或者 DELETE 这些事儿上执行，也能在好几个事儿上执行。比如说，能弄一个触发器，在员工表的 INSERT 或者 UPDATE 这些事儿上执行，员工工资要是大于 10000 ，就把工资设成 10000 。

触发器的执行条件：

能使 IF 语句来管着触发器的执行条件。比如说，在员工表上弄一个 BEFORE INSERT 的触发器，员工工资要是小于 5000 ，就把入职时间设成现在的时间。

触发器的流程控制：

能使流程控制语句来管着程序执行的流程。比如说，能使 IF 语句、CASE 语句、WHILE 语句、LOOP 语句这些。比如说，在员工表上弄一个 BEFORE INSERT 的触发器，员工工资要是小于 5000 ，就把入职时间设成现在的时间，要不就设成一个月前的时间。trigger_name 是触发器的名儿；trigger_time 是触发器的执行时间，能是 BEFORE 或者 AFTER ；trigger_event 是触发器的事儿，能是 INSERT 、UPDATE 或者 DELETE ；table_name 是触发器在的表的名儿；trigger_body 是触发器的 SQL 语句。BEFORE 触发器在执行操作之前干活，可以做数据的提前处理和验证。AFTER 触发器在执行操作之后干活，可以做数据的后面处理和计算。触发器能在好几个事儿上执行。

六、MySQL 触发器的注意事项

性能影响：老是触发的那种复杂的触发器可能会影响数据库的性能，特别是在高并发的环境里。比如说，写作素材里讲“mysql 触发器对性能影响 MySQL 触发器可能会对性能有点影响，主要看触发器的设计和使用的情况。触发器执行的时候会给数据库增加负担，所以得小心设计和使用，别让性能受不好的影响。一些可能让性能受影响的情况包括：触发器执行的频率：要是触发器老是被触发，会让数据库老是做额外的操作，就影响性能了。触发器的复杂程度：要是触发器包含复杂的逻辑和操作，执行的时候会多用不少资源，就让性能下降了。触发器设计得不好：要是触发器设计得不合理，可能会导致锁表、死锁这些问题，接着就影响性能了”。

避免循环触发：设计触发器的时候，得小心点别让触发器之间循环调用，弄出死循环。能通过用标识符来标记触发器是不是已经执行过，这么着就能避免触发器的递归调用。就像写作素材里说的“mysql 触发器如何避免循环 在 MySQL 触发器里避免循环的一个常见办法是用一个标识符来标记触发器是不是已经执行过了，这样就能避免触发器的递归调用。比如说，能在触发器里设一个变量来表示触发器是不是已经执行了，要是已经执行了就不再触发”。

资源限制：触发器执行的时候受同一个事务里资源的限制，别执行大量操作数据或者长时间运行的任务。写作素材里说“资源限制：触发器执行的时候受同一个事务里资源的限制，别执行大量数据操作或者长时间运行的任务。

事务管理：触发器在跟触发它的操作一样的事务里运行，所以事务是提交还是回滚会影响到触发器的执行结果。

测试与调试：因为触发器是隐式执行的，测试和调试挺难的，建议在测试环境里好好验证。

文档记录：把触发器的目的、触发的时机还有执行的逻辑详细地记下来，方便后面维护。