[大数据]触发器和事件

一.触发器

1.触发器的基本概念

触发器是和表相关联的一种数据库对象，可以将它看作一种特殊的存储过程，可以理解为不需要人为调
用的存储过程。
①触发器关键字：trigger
②基本作用：通过对表进行数据的插入、更新或删除等操作来触发，用于执行某些特定的操作。

2.触发器的创建

可以在mysql命令界面通过？ create trigger命令查看创建触发器的格式。
创建触发器的基本格式

CREATE TRIGGER trigger_name #触发器名 
	trigger_time 	#触发时机：before|after 
	trigger_event 	#触发条件：insert|update|delete 
	ON tbl_name 	#表名 
	FOR EACH ROW 	#对每一行 
	trigger_body 	#执行的操作

总的来说就是对表中的某一行进行插入、删除、更新时触发某一触发器来执行某些操作。

参数解析：

① 触发条件
insert：在插入表的时候触发
update：在更新表的时候触发
delete：在删除表的时候触发
② 触发时机
before：在…之前
after：在…之后

举例1：利用触发器实现检查约束
对表格创建一个触发器，规定年龄属性的取值范围在0~100之间，年龄不能低于0岁，不能高于10岁

-- 创建测试表格
create table trigger_test_tb( 
    id int primary key auto_increment, 
    test_name varchar(20), 
    test_age int 
);

-- 创建触发器实现检查约束
delimiter // 
create trigger tri_test_insert 
before insert on trigger_test_tb for each row 
begin
	if new.test_age < 0 then set new.test_age = 0; -- 年龄低于0岁的用0代替 
	elseif new.test_age > 100 then set new.test_age = 100; -- 年龄高于100岁的用 100代替end if; 
end// 
delimiter ;

-- 测试触发器
select * from trigger_test_tb; 
insert into trigger_test_tb values(null,'张三',18); 
insert into trigger_test_tb values(null,'李四',20), (null,'王五',-10),(null,'王五',101);

3.new对象和old对象

数据库提供了两个数据对象new和old分别记录新值和旧值。
例如：update更新数据时会把新的数据覆盖之前的数据，那new对象就是保存新数据，而old对象是保存被覆盖之前的数据。
那么相对于insert而言是没有old的，因为插入之前是没有旧数据，相对于delete而言是没有new的，因为只删除原来存在的数据，不添加新数据。
insert 插入数据时，new 表示插入的数据
update 更新数据时，new 表示新的数据,old表示的是原数据
delete 删除数据时，old 表示的是删除的数据

4.查看数据库中的触发器

查看触发器的基本格式：

show triggers from 数据库名;

5.查看所有触发器

mysql触发器都是保存在information_shema.triggers表中的，所以查看触发器需要查询information_shema数据库中的trigger表。

use information_shema; 
select * from triggers; # 查看所有触发器 
select * from triggers where trigger_name='触发器名';

6.触发器的使用限制

6.1触发器中不支持使用select、create和call等动态SQL语句，如：

delimiter // 
create trigger tri_test_select 
after insert on trigger_test_tb for each row 
	select * from trigger_test_tb where id = new.id; 
-- 错误：触发器中不支持使用 select语句 -- end// delimiter ;

6.2 利用用户变量突破触发器的使用限制，如：

create trigger tri_test_select 
after insert on trigger_test_tb for each row 
	select id from trigger_test_tb where id = new.id into @id; 
	-- 用查询结果给用户变量@id赋值，就可以使用用户变量@id将数据带回 
insert into trigger_test_tb values(null,'赵六',-1); 
select * from trigger_test_tb; select @id; 
select * from trigger_test_tb where id = @id;

7.删除触发器

drop trigger 触发器名;

二.事件

1.事件的基本概念

与触发器类似，都是在特定的条件执行相应的操作，但是不同的是，触发器是触发时执行某些任务，而事件是定时执行某些任务。
①事件的关键字： event
②事件的基本作用：让数据库定时执行某些操作。

2.事件的创建

创建事件的基本格式
可以通过？ create event命令查看创建事件的格式。如：

CREATE EVENT [IF NOT EXISTS] event_name #不存在就创建 
ON SCHEDULE schedule #计划任务 
(定时操作：AT timestamp 时间点，EVERY interval 间隔多久) 
	[ON COMPLETION [NOT] PRESERVE] # 事件到期处理 
	(事件执行完之后默认保留，加一个NOT为删除) 
	[ENABLE | DISABLE | DISABLE ON SLAVE] #事件的状态 
	(开启 | 关闭) #相当于你设置的闹钟是打开还是关闭的 
	[COMMENT 'string'] 
	DO event_body; # 事件所执行的操作

参数解析

schedule: # 任务计划参数解析 
AT timestamp [+ INTERVAL interval] ... 
	# AT 时间点 [+ 时间间隔] 
	（如：AT 2021-6-6 8:30:10，2021年6月6号8点30分10秒） 
	| EVERY interval 
	[STARTS timestamp [+ INTERVAL interval] ...] 
	[ENDS timestamp [+ INTERVAL interval] ...] 
	# EVERY 间隔多久 
	[开始时间 [+ 时间间隔]] 
	[结束时间 [+ 时间间隔]]

举例2：用事件实现每6秒钟向表中插入数据

-- 创建事件测试表：
create table event_test_tb( 
    id int primary key auto_increment, 
    insert_time time 
);

--  创建事件实现每6秒钟向表中插入数据：
create event insert_event 
on schedule every 6 second 
do 
	insert into event_test_tb values(null,second(now()));
	
-- 查看事件执行的效果：
select * from event_test_tb; 
-- 查看表中是否每6秒会自动增加一条数据 

如果事件没有执行效果，则是事件被关闭或者事件调度器未打开。

3.事件的打开与关闭

-- 设置事件的状态为disable关闭某个事件
alter event 事件名 disable;

-- 设置事件的状态为enable开启某个事件
alter event 事件名 enable;

如果将事件打开后事件仍然没有执行，那一般是事件调度器没有打开。

4.事件调度器的打开与关闭

-- 查看事件调度器的状态
show variables like '%SCHEDULE%';

-- 关闭事件调度器
set global event_scheduler = off; 
-- 或 
set global event_scheduler = 0;

-- 打开事件调度器
set global event_scheduler = on; 
-- 或 
set global event_scheduler = 1;

-- 查看进程列表
-- 事件调度器开启后是有由一个进程来执行的。
show processlist;

5.事件小练习

创建一个清空表事件，5天之后开启事件，每小时清空表数据，一个月后停止事件并不保留事件。

create event delete3_event 
on schedule every 1 hour 
starts current_timestamp() + interval 5 day 
-- current_timestamp()获取当前事件，和now()类似 
ends current_timestamp() + interval 1 month 
on completion not preserve -- 事件停止后不保留，删除事件 
do
	truncate table event_test_tb; -- 清空表数据

6.查看所有事件的状态

show events;

7.删除事件

drop event 事件名;

8.查看事件的具体执行语句

show create event 事件名;

9.数据库中时间单位

-- 数据库中常用的时间单位有：
year、month、 day、 week、 hour、 minute、 second、 microsecond