mysql创建sql语句过程，并使用时间EVENT定时执行

下面是操作步骤：

1.检查event事件是否开启

show variables like '%sche%';

如果Value为OFF，需要执行以下语句，注意，请使用超级权限用户：

 set global event_scheduler=1; 

2.创建存储过程

create or replace procedure sync_duty as
begin
DELETE  FROM duty_temp;
INSERT INTO duty_temp SELECT DISTINCT
`pm`.`POST_ID` AS `POST_ID`,
`pm`.`DEPT_ID` AS `DEPT_ID`,
`dl`.`PARENT_ID` AS `PARENT_ID`,
( SELECT `duty_library`.`DUTY_NAME` FROM `duty_library` WHERE ( `duty_library`.`LIBRARY_ID` = `dl`.`PARENT_ID` ) ) AS `PARENT_NAME`,
`dl`.`LIBRARY_ID` AS `DUTY_ID`,
`dl`.`DUTY_NAME` AS `DUTY_NAME`,
`dl`.`DESC_ID` AS `DESC_ID`,
`dl`.`DUTY_TYPE` AS `DUTY_TYPE`,
`dl`.`TASK_NATURE` AS `TASK_NATURE`,
`dl`.`HARD_DEGREE` AS `HARD_DEGREE`,
`dl`.`DEL_FLAG` AS `DEL_FLAG`,
`dl`.`ENABLE_YN` AS `ENABLE_YN`,
'new' AS DSP_STATE 
FROM
 ( `post_manage` `pm` LEFT JOIN `duty_library` `dl` ON ( find_in_set( `dl`.`LIBRARY_ID`, `pm`.`DUTY_IDS` ) ) ) 
WHERE
 ( ( `dl`.`DEL_FLAG` = 0 ) AND ( `pm`.`DEL_FLAG` = 0 ) );
commit;
end sync_duty;

3.创建定时任务

 create event if not exists sync_duty_event
    on schedule every 30 MINUTE   ##设置30分钟执行一次，周期参数second /day/week/month
    on completion preserve

    do call sync_duty();  #执行sync_duty()存储过程

4.关闭事件任务

    alter event sync_duty_eventON

    COMPLETION PRESERVE DISABLE;

5.开启事件任务

    SET GLOBAL event_scheduler = ON;　　            #开启定时器

    COMPLETION PRESERVE ENABLE;

6.查看事件运行日志

SELECT * FROM information_schema.EVENTS;