【MySQL数据库】触发器与事件

MySQL触发器

trigger，在表的插入insert、更新update、删除delete操作发生时自动执行MySQL语句。

学过Qt的都知道信号槽，一旦发出某个信号，那么就会触发关联的信号槽函数。触发器就类似于这个操作。

创建触发器时需要给出一些信息：

触发器名

在操作前还是操作后触发（before/after）

被什么操作触发（insert/update/delete）

被哪个表的操作触发

在MySQL中，触发器采用类似存储过程的语法进行定义，包括触发条件、触发事件、以及触发后执行的SQL语句等:

sql">create trigger myTrigger 
[after|before] [insert|update|delete]
on table_name
begin-- coding
end;

每当表table_name进行操作时，在这之前(之后)执行器会自动执行myTrigger触发器内部的coding。不同的操作也有一定的差异：

insert：可通过new访问被插入的行，before insert可以更新new中的值（即允许更改被插入的值）。

delete：可通过old访问被删除的行，old中的值是只读的，因为他即将被删除/已经被删除，现在只是临时保存到old中。

update：old可以访问更新前的值，new访问更新后的值。

Tips：总结得出insert没法访问old、delete没法访问new、update全能访问。

其它操作：

sql">-- 查看触发器
show triggers;
-- 查看指定表的触发器
show triggers like 'table_name';--查看触发器的创建语句
show create trigger trigger_name;-- 删除触发器
drop trigger [if exists] 触发器名;

练习：需求-更新部门，对应部门的员工涨100元工资

sql">-- 第一步：创建触发器
create trigger up_sal  after update on dept
beginupdate emp set sal=sal+100 where emp.deptno=old.deptno;
end;-- 第二步：更新部门
update dept
set dname="newDept" where deptno=20;-- 第三步：查询员工信息，发现涨工资了
select * from emp;

作业：需求-给部门表添加员工数量字段，员工表添加员工时对应部门的员工数量自动加1

第一步：dept表添加一个字段：员工数量

sql">alter table dept
add enum int default 0;

第二步：部门表的员工数量更新为正确值

sql">update dept
set enum = (select count(*) from emp where emp.deptno=dept.deptno);

第三步：创建触发器，员工表添加员工时，对应部门员工数量加一

sql">create trigger refresh_enum after insert on emp
beginupdate deptset enum=enum+1 where deptno=new.deptno;
end;

第四步：员工表添加员工测试一下。（自己来吧）

总结：通过使用触发器，可以在数据库层面实现一些复杂的业务逻辑，确保数据的一致性和完整性。然而，过多的触发器可能会影响数据库性能，因此在设计时需谨慎考虑。

MySQL事件

MySQL事件是一种在数据库中创建、调度和执行计划任务的机制。通过使用事件，用户可以定期执行特定的SQL语句或存储过程，从而自动化数据库管理和维护任务。

MySQL事件机制的主要组成部分包括以下内容：

1. 事件调度器：事件调度器是MySQL中用于管理和执行事件的组件。用户可以通过事件调度器创建新的事件、修改已有的事件、启用或禁用事件，并查看事件执行的状态和日志信息。

2. 事件对象：事件对象是用户定义的一个具体任务或操作，可以是一个SQL语句、存储过程或函数。用户可以在创建事件时指定要执行的具体操作，并设置事件的调度时间和频率。

3. 事件时间表：事件时间表是与事件相关的调度时间和频率的设置。用户可以在创建事件时指定事件的执行时间表，包括事件的开始时间、结束时间、执行间隔等。

通过使用MySQL事件机制，用户可以实现诸如定时备份数据库、定期清理过期数据、定时生成报表等自动化任务，从而提高数据库管理的效率和可靠性。

创建语句：

sql">create event event_name
on schedule _schedule_
do
event_body;

event_name:事件名称；_schedule_:执行计划；event_body:自动执行的语句。

需求：创建一个定时事件，每1小时执行一次任务，任务为：删除用户表中注册时间早于30天前的用户数据

sql">create event event_clean_expired_data
on schedule every 1 hour
do
delete from users where register_time < now() - interval 30 day;

特别注意：在MySQL中，事件必须处于激活态才能执行。默认情况下，新建的事件不会自动启动事件调度器，需要通过命令开始事件调度器：

sql">set global event_scheduler = on;

触发器与事件的区别

触发器和事件给我的第一感觉就很像，但他们确实是MySQL的两种不同的机制。经过查阅相关资料，总结出了以下几点：

相似之处：

事件和触发器都是MySQL中的一种数据对象

它们都是作为一组SQL语句，可以定时或触发执行

它们都可以自动完成一些常规的事务操作。

不同之处：

语法结构不同：这点看上面的创建语句就可以知道了

触发方式不同：

事件按照预定的时间表自动触发执行

触发器在特定的数据表上进行增删改等操作时触发执行

作用范围不同：

事件是针对整个数据库的

触发器通常只作用于单个表（或者服务器全局、schema级）

功能上的差异：

事件可以完成归档、日志记录、调度管理、定期的数据清理、备份、统计等任务；

触发器没有这些高级功能，触发器通常用来进行验证、约束、自动化处理。

感谢大家！