Mysql创建定时任务执行存储过程。

最新推荐文章于 2023-09-28 13:35:15 发布

csdn_Ty

最新推荐文章于 2023-09-28 13:35:15 发布

阅读量530

点赞数 1

分类专栏： Mysql

本文链接：https://blog.csdn.net/CSDN_Ty/article/details/110078715

版权

mysql sql

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

首先就是需要了解一下Mysql的存储过程，如果不太熟悉的朋友建议先浏览一下https://www.runoob.com/w3cnote/mysql-stored-procedure.html。

例子：

drop procedure if exists workReportAnalysis;
/* reportRecordType 1:分析前一天日报提交情况 2:分析上周周报提交情况 3:分析上月月报提交情况*/
create procedure workReportAnalysis(in reportRecordType int)
begin
    declare b int default 0; /*是否达到记录的末尾控制变量*/
    declare _complete int(2);
    declare _factoryid varchar(64);
    declare cur_1 cursor for
        select f.id  factoryid,
               exists(select 1
                      from biz_report_record r
                      where factory_id = f.id
                        and (
                              (r.report_type = reportRecordType and
                               date_format(f.create_time, '%y-%m-%d') =
                               date_format(date_sub(curdate(), interval 1 day), '%y-%m-%d'))
                              or (r.report_type = reportRecordType and
                                  date_format(f.create_time, '%y') =
                                  date_format(date_sub(curdate(), interval 1 day), '%y') and
                                  WEEK(f.create_time, 1) = WEEK(date_sub(curdate(), interval 1 day), 1) - 1)
                              or (r.report_type = reportRecordType and date_format(f.create_time, '%Y%m') =
                                                                       date_format(date_sub(now(), interval 1 month), '%Y%m'))
                          )
                   ) complete
        from biz_factory f
        where f.status = 1
          and not exists(select 1
                         from biz_report_record_analysis ra
                         where ra.factory_id = f.id
                           and ra.report_record_type = reportRecordType
                           and ra.create_date = date_sub(curdate(), interval 1 day));

    declare continue handler for not found set b = 1;
    open cur_1;
    fetch cur_1 into _factoryid, _complete; /*获取第一条记录*/
    while b <> 1
        do
            insert into biz_report_record_analysis(id, factory_id, create_date, complete, report_record_type)
                value (replace(uuid(), '-', ''), _factoryid, date_sub(curdate(), interval 1 day), _complete,
                       reportRecordType);
            fetch cur_1 into _factoryid, _complete; /*取下一条记录*/
        end while;
    close cur_1;
end;

然后写一个定时任务，定时执行存储过程：

执行以下查询语句查看是否开启
```
SHOW VARIABLES LIKE 'event_scheduler';
```
显示 event_scheduler 为OFF时用一下命令开启
```
set global event_scheduler=1;
```
（注：以上改法会在数据库重启以后恢复为原来状态，要想数据存重启也开启event_scheduler，则需要修改配置文件my.ini。修改如下，修改完成后重启mysql服务即可）
```
[mysqld]event_scheduler=ON //这一行加入mysqld标签下
```
创建定时器

/*每天凌晨一点执行*/
drop event if exists daily_analysis_event;
create event if not exists daily_analysis_event
    on schedule every 1 day starts date_add(date_add(curdate(), interval 1 day), interval 1 hour)
    on completion preserve enable
    do call workreportanalysis(1);

/*每周一凌晨一点执行*/
drop event if exists weekly_analysis_event;
create event if not exists weekly_analysis_event
    on schedule every 1 week starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 week ),interval 1 hour)
    on completion preserve enable
    do call workreportanalysis(2);

/*每月一号凌晨一点执行*/
drop event if exists monthly_analysis_event;
create event if not exists monthly_analysis_event
    on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
    on completion preserve enable
    do call workreportanalysis(3);