事件(定时器)
MySQL事件调度器event_scheduler默认是关闭的。要创建事件,必须打开调度器。
- 查看事件调度器是否打开
show variables like '%event_scheduler%';
- 开启事件调度器
-- 可通过如下任何一个命令行,开启事件调度器
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
-- 可以通过如下任何一个命令行修改配置文件my.ini,开启事件调度器
event_scheduler = 1
event_scheduler = ON
- 关闭事件调度器
--可通过如下任何一个命令行,关闭事件调度器
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
--可以通过如下任何一个命令行修改配置文件my.ini,关闭事件调度器
event_scheduler = 0
event_scheduler = OFF
- 查看调度器执行情况
show processlist;
创建事件语法
create event event_name on schedule schedule do event_body
- 立即执行一次
create event event_now
on schedule at now()
do insert into p_test (name,num) values ('javakc' ,'123456' );
- 指定时间点执行一次
create event event_now
on schedule at '2021-10-23 11:23:00'
do insert into p_test (name,num) values ('javakc' ,'123456' );
- 每分钟执行一次
create event e_minute
on schedule every 1 minute
do call myproc();
- 指定时间后开始执行
create event e_minute
on schedule
every 1 minute
#从当前时间加上10分组后开始执行,每一分钟一次。
starts date_add(now(),interval 10 minute)
do call myproc();
- 指定时间后结束
create event e_minute
on schedule
every 1 minute
#从当前时间开始,十分钟后停止。
ends date_add(now(),interval 10 minute)
do call myproc();
- 查看正在执行的事件
show events
- 修改事件
alter event event_name on schedule schedule do event_body
- 删除事件
drop event if exists event_name;
触发器
- 创建触发器
create trigger 触发器名称
before|after
insert|update|delete
on 表
for each row
begin
sql语句块
end;
- insert触发器
-- 不能在非工作日为user表插入数据:
create trigger tri_insert
before insert on user
for each row
begin
if weekday(now()) in (5,6) then
signal sqlstate 'HY000' set message_text = '不能在非工作日插入数据';
end if;
end;
- update触发器
-- 在修改user表的同时,将备份表user2的数据也修改了
create table user2 as(select * from user);
create trigger tri_update
before update on user
for each row
begin
update user2
set name = new.name,
ename = new.ename,
personId = new.personId,
sex = new.sex,
credit = new.credit,
city = new.city
where userId = new.userId;
end;
-- 修改信用积分时,要求更新后的信用积分不能比更新前小:
create trigger tri_update
before update on user
for each row
begin
if new.credit<old.credit then
signal sqlstate 'HY000' set message_text = '更新后的信用积分比更新前小';
end if;
end;
- delete触发器
-- 在删除数据时,将被删除的数据备份到日志表
create table log_user as (select * from user where 1=2)
create trigger tri_delete
after delete on user
for each row
begin
insert into log_user (userId,name,ename,personId,sex,credit,city)
values (old.userId,old.name,old.ename,old.personId,old.sex,old.credit,old.city);
end;
- 查询触发器
SELECT * FROM information_schema.`TRIGGERS`
- 删除触发器
drop trigger trigger_name;