因为数据表的数据量太大，每天定时执行分表的存储过程，时间长了分表数量过多，需要定期删除分表，先创建存储过程，之后设置定时事件去执行存储过程

CREATE  PROCEDURE `sp_droptables`()
begin
    declare t_name varchar(64) ;
            declare isFinished int default false;
            declare log_table_list cursor for (select table_name from INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA='库名' and TABLE_NAME like 'table_%') ;
            declare continue handler for not found set isFinished=true; 
            open log_table_list;
            repeat 
                fetch log_table_list into t_name; 
                if isFinished = false then
                    if datediff(now(),DATE_FORMAT(replace(t_name,'table_',''),'%Y-%m-%d') )>30                                         then
                        set @sqltext=concat('drop table ',t_name,';');
                        PREPARE drop_table from @sqltext;
                        execute drop_table ; 
                    end if;
                end if;
                until isFinished 
            end repeat; 
            close log_table_list;
end

存储过程创建成功之后，设置定时事件

DROP EVENT IF EXISTS `E_DROPTABLES`;
CREATE  EVENT `E_DROPTABLES`
ON SCHEDULE
EVERY '1' DAY STARTS '2019-12-26 03:00:00'
ON COMPLETION PRESERVE
DO BEGIN
CALL sp_droptables;
END

 

版权声明：本文为lplcc原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/lplcc/p/13044862.html