批量清除mysql分区表

本例子以天为单位创建分区，创建的分区格式为：*pdyyyymmdd，比如：monitor_cpupd20190128 、monitor_cpupd20190129

-- 创建根据表名称和时间清除分区的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS delete_partitions$$
CREATE PROCEDURE delete_partitions(IN tabName VARCHAR(500),IN YearMonth VARCHAR(20) )
BEGIN
declare parName VARCHAR(50);
DECLARE sql_oper_revcord VARCHAR(100);
-- 查询表下的所有分区
declare curl CURSOR FOR SELECT partition_name FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = tabName;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE curl;
-- 遍历分区名称，根据时间规则匹配分区
OPEN curl;
FETCH curl INTO parName; 
WHILE ( parName is not null) DO
 if locate(YearMonth,parName)>0 THEN
    select parName;
    set @sql_oper_revcord=CONCAT("alter table ",tabName,' drop partition ',parName);
    PREPARE stmt FROM @sql_oper_revcord;
    EXECUTE stmt;
 END IF;
 -- 游标向下走一步 
 FETCH curl INTO parName;
END WHILE;
CLOSE curl; 
END $$

-- 创建根据 数据库名称和时间 删除该数据库下 所有表 分区的存储过程，调用上面存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS delete_database_partitions$$
CREATE PROCEDURE delete_database_partitions(IN baseName VARCHAR(500),IN YearMonth VARCHAR(20) )
BEGIN
declare parName VARCHAR(50);
DECLARE sql_oper_revcord VARCHAR(100);
-- 查询当前数据库下的所有表名
declare curl CURSOR FOR select table_name from information_schema.tables where table_schema=baseName and table_type='base table';
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE curl;
-- select tabName as cc;
OPEN curl;
FETCH curl INTO parName; 
WHILE ( parName is not null) DO
    call delete_partitions(parName,YearMonth);
FETCH curl INTO parName;
END WHILE;
CLOSE curl; 
END $$

-- 调用：
call delete_database_partitions('wutongyu_monitordb','2018');
call delete_database_partitions('wutongyu_monitordb','201810');

-- 定时清除
DELIMITER $$
DROP EVENT IF EXISTS event_clear_database_partition$$
CREATE EVENT event_clear_database_partition ON SCHEDULE EVERY 7 day STARTS NOW() ON COMPLETION PRESERVE ENABLE DO
BEGIN
     DECLARE clearTime TIMESTAMP;
     declare yearStr VARCHAR(50);
     declare monthStr VARCHAR(50);
     SET clearTime=now()- INTERVAL 60 day;
     SET yearStr=substring(clearTime,1,4);
     SET monthStr=substring(clearTime,6,2);
     call delete_database_partitions( "imp_monitordb",CONCAT(yearStr,monthStr) );
END $$