mysql存过 + mysql定时任务
mysql存过
创建存过
CREATE PROCEDURE test1()
begin
# begin与end中间为存过要执行的操作
SELECT * from t_user;
SELECT * FROM t_class;
END;
变量申明
# 申明变量
create procedure test2()
begin
# 申明变量
declare username varchar(32) default '';
declare age int(32) default 0;
# 给变量赋值
set age=22;
select name into username from t_user where id=1;
select username,age;
end;
传参
# 传入参数
# in类型的参数,表示该参数的值必须在调用存储过程事指定,如果不显示指定为in,那么默认就是in类型。
# in类型参数一般只用于传入,在调用过程中一般不作为修改和返回
create procedure test3(in userid int)
begin
SELECT name from t_user where id = userid;
end;
# 传出参数:在调用存储过程中,可以改变其值,并可返回;
# 如果调用存储过程中需要修改和返回值,可以使用out类型参数
create procedure test4(in userid int,out username varchar(32))
begin
select name into username from t_user where id = userid;
SELECT username;
end;
判断
create procedure test5(in userid int)
begin
declare username varchar(32) default '';
if(userid%2=0)
then
select name into username from t_user where id = userid;
select username;
else
select userid;
end if;
end;
循环
create procedure test6(in userid int)
begin
declare i int default 0;
while(i < 10) do
select name from t_user where id=userid;
set i = i+1;
end while;
end;
调用存过
# 无参的存过
call test1();
# 有参的存过
call test3(1)
查看存过
# 查看所有存过
show procedure status;
# 查看某个存过的创建
show create procedure event_03;
删除存过
drop procedure if EXISTS test1;
mysql定时任务
查看定时策略
SHOW VARIABLES LIKE 'event_scheduler';
开启事件调度器
SET GLOBAL event_scheduler = ON;
创建定时任务
周期执行(every)
on schedule every 可以为second、minute、hour、day、week(周)、quarter(季度)、month、year
CREATE EVENT IF NOT EXISTS event_01
on schedule every 1 second
# 创建后立即执行
on COMPLETION PRESERVE enable
do call event_03();
在某个时间点执行(at)
CREATE EVENT IF NOT EXISTS event_02
on schedule at 1 '2021-05-14 00:00:00'
on COMPLETION PRESERVE disable
do call event_03();
某个时间段执行(starts、ends)
CREATE EVENT IF NOT EXISTS event_03
on schedule every 1 second ends CURRENT_TIMESTAMP() + INTERVAL 10 second
on COMPLETION PRESERVE enable
do call event_03();
CREATE EVENT IF NOT EXISTS event_04
on schedule every 1 second starts CURRENT_TIMESTAMP() + INTERVAL 5 second ends CURRENT_TIMESTAMP() + INTERVAL 20 second
on COMPLETION PRESERVE enable
do call event_03();
开启、关闭定时任务
开启定时任务
当on COMPLETION PRESERVE disable时,需要手动开启定时任务
alter event 定时时间名 on completion preserve enable;
关闭定时任务
alter event 定时时间名 on completion preserve disable;
查看定时任务
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
show events;
删除定时任务
drop event if exists 定时任务名;