MySQL

图片库
https://icomoon.io/app/#/select/image


#SHOW VARIABLES LIKE '%sche%';
SET GLOBAL event_scheduler = 1;

drop procedure if exists table;
delimiter //
create procedure XXX()
begin
declare flag int default 0;
declare name varchar(500);
declare name_2 varchar(500);
declare name_3 varchar(500);
declare type tinyint;
declare v_deletesql varchar(20000) default '';
declare i int ;

declare find_cursor cursor for
select distinct XXX from table1 where type !=0;
declare continue handler for not found set flag = 1;

open find_cursor ;
find_cursor : loop
set name_2= '';
fetch find_cursor into name,name_3,type;
if( flag = 1) then leave find_cursor ;
end if;
set i =-1 ;
while (i<3 ) do
set name_2 = name;
call table_name_proc( i,type,name_3,' ',name_2 );
set v_sql ='';
if(i=-1)then
set v_sql =concat('delete from ',name_2,' where collect_time < date_sub(now(),interval 7 day )');
elseif( i = 1 ) then
set v_sql =concat('delete from ',name_2,' where collect_time < date_sub(now(),interval 3 month )');
elseif( i= 2) then
set v_sql =concat('delete from ',name_2,' where collect_time < date_sub(now(),interval 1 year )');
end if;
if( v_sql != '' ) then
set @sql = v_sql ;
prepare statement from @sql;
start transaction;
execute statement;
commit;
deallocate prepare statement;
end if;
set i=i+1;
end while;
end loop find_cursor ;
close find_cursor ;
end
//
delimiter ;

drop event if exists eventJob ;
create event if not exists eventJob
on schedule every 1 day starts date_add(curdate(),interval 24 hour)
on completion PRESERVE ENABLE
do call XXX();

alter event eventJob ON COMPLETION PRESERVE DISABLE;

alter event eventJob ON COMPLETION PRESERVE ENABLE;