MySQL设置定时事件

需求描述
每月初更新一次用户表中的分数

• 编写存储过程

DELIMITER // 
create procedure rush_mark_every_mon()
begin
drop table if exists temp;
create temporary table temp(
select user_id,count(distinct problem_id) from solution  WHERE date_format(in_date,'%Y-%m') = date_format(now(),'%Y-%m') and result = 4 group by user_id order by count(distinct problem_id) desc);
update users set score=score+100 where user_id in (select t.user_id from (select temp.user_id from temp limit 0,3) as t);
update users set score=score+60 where user_id in (select t.user_id from (select temp.user_id from temp limit 3,5) as t);
update users set score=score+30 where user_id in (select t.user_id from (select temp.user_id from temp limit 8,7) as t);
END;
//
DELIMITER ;

• 设置时区

set time_zone = '+8:00';

• 开启事件调度器（服务器重启事件调度器会自己关闭，如果要设置重启无影响请更改my.ini中的配置）

set GLOBAL event_scheduler = 1;

• 创建定时任务

CREATE  EVENT `update_every_score_Month` ON SCHEDULE EVERY 1 MONTH STARTS '2020-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL rush_mark_every_mon();

• 查看定时任务

SELECT * FROM information_schema.`EVENTS`;

• 删除定时任务

DROP EVENT update_every_score_Month;

如有问题请及时指出