创建存储过程、mysql自身定时执行计划

作者官方网站:http://www.wxl568.cn
 创建存储过程、mysql自身定时执行计划

下面简单删除定时删除过期数据场景

/*删除存储过程*/

drop procedure if exists p_dele_mp_addret;

/*删除原有的定时任务*/

drop event if exists p_dele_mp_addret;

delimiter //

CREATE  EVENT `p_dele_mp_addret`

            ON SCHEDULE

                        EVERY 1 DAY STARTS '2018-12-26 07:10:28' ENDS '2021-03-26 07:17:43'

            ON COMPLETION NOT PRESERVE

            ENABLE

            COMMENT ''

            DO BEGIN

 CALL p_dele_mp_addret();

END;//

-- 创建存储过程

delimiter //

CREATE  PROCEDURE p_dele_mp_addret() ## 创建存储过程

BEGIN

  delete  from  ts_clock_emp_address where INVALID_DATE  < DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY),'%Y-%m-%d');

  delete  from ts_clock_emp_grant  where INVALID_DATE  < DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY),'%Y-%m-%d');

  delete from tt_ecp_addres where id in(select ecpAddresId from  tt_ecp_addres_detailed where INVALID_DATE < DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY),'%Y-%m-%d'));

  delete  from tt_ecp_addres_detailed where INVALID_DATE < DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY),'%Y-%m-%d');

END;//

 

作者官方网站:http://www.wxl568.cn