MYSQL定时增加partition的存储过程

-- --------------------------------------------------------
-- 主机:                          127.0.0.1
-- 服务器版本:                        5.7.19-log - MySQL Community Server (GPL)
-- 服务器操作系统:                      linux-glibc2.12
-- HeidiSQL 版本:                  9.5.0.5284
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- 导出  事件 taxi_log.add_table_partition 结构
DELIMITER //
CREATE DEFINER=`root`@`%` EVENT `add_table_partition` ON SCHEDULE EVERY 1 MONTH STARTS '2018-08-01 01:00:00' ON COMPLETION NOT PRESERVE DISABLE COMMENT '自动添加分区到分区表' DO BEGIN
call proc_add_table_partition_all('taxi_order','t_order',5,8);
call proc_add_table_partition_all('taxi_order','t_order_ext_rent',5,8);
call proc_add_table_partition_all('taxi_order','t_order_ext_taxi',5,8);
call proc_add_table_partition_all('taxi_order','t_order_ext_take',5,8);
call proc_add_table_partition_all('taxi_order','t_order_ext_take',5,8);
call proc_add_table_partition_all('taxi_order','t_order_ext_pay',5,8);
call proc_add_table_partition_all('taxi_order','t_order_ext_route',5,8);
#########################################################################
call proc_add_table_partition_all('taxi_verification','t_obj_user',2,-1);
call proc_add_table_partition_all('taxi_verification','t_obj_user_token',2,-1);
call proc_add_table_partition_all('taxi_verification','t_ycer',2,-1);
#########################################################################
call proc_add_table_partition_all('taxi_account','t_draw_log',2,-1);
call proc_add_table_partition_all('taxi_account','t_pay_no',2,-1);
call proc_add_table_partition_all('taxi_account','t_tcer_account_flow',2,-1);
call proc_add_table_partition_all('taxi_account','t_ycer_account',2,-1);
call proc_add_table_partition_all('taxi_account','t_ycer_account_flow',2,-1);
call proc_add_table_partition_all('taxi_account','t_liq_bill_company',2,-1);
call proc_add_table_partition_all('taxi_account','t_liq_bill_system',2,-1);
call proc_add_table_partition_all('taxi_account','t_liq_bill_tcer',2,-1);
call proc_add_table_partition_all('taxi_account','t_liq_period_company',2,-1);
call proc_add_table_partition_all('taxi_account','t_liq_period_system',2,-1);
call proc_add_table_partition_all('taxi_account','t_liq_period_tcer',2,-1);
##############################################################################
call proc_add_table_partition_all('taxi_activity','t_regist_log',2,-1);
call proc_add_table_partition_all('taxi_activity','t_reward_log',2,-1);
END//
DELIMITER ;

-- 导出  过程 taxi_log.proc_add_table_partition_all 结构
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `proc_add_table_partition_all`(
    IN `db_name` VARCHAR(64),
    IN `tab_name` VARCHAR(64),
    IN `days` INT,
    IN `intervals` INT

)
    COMMENT 'intervals表示：-1为月'
label:BEGIN
declare maxpar varchar(64) default '';
declare maxptime varchar(32) default '';
declare maxpar_dday_t varchar(20) default '';
declare maxpar_num varchar(20) default '';
declare incr_day int default 0;
declare sql_t varchar(1000) default '';
declare parcount int default 0;

if days>1000 or days<0 then
    select 'days more than 1000 or days<0' as error;
    leave label;
end if;
if intervals = -1 then
select '按月增加分区';
elseif intervals>31 or intervals<0 then 
    select 'intervals more than 31 or days<0' as error;
    leave label;
end if; 
set sql_t=concat('select count(1) into @parcount from information_schema.PARTITIONS where table_name = ''',
tab_name,''' and table_schema=''',db_name,'''');

set @sql_t=sql_t;
PREPARE stmt1 FROM @sql_t;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

set parcount=@parcount;
if parcount>5000 then
    select 'partition count more than 5000' as error;
    leave label;
end if;

set sql_t=concat('select partition_description into @maxptime from information_schema.PARTITIONS where table_name = ''',
tab_name,''' and table_schema= ''',db_name,''' order by partition_ordinal_position desc limit 1');

set @sql_t=sql_t;
PREPARE stmt1 FROM @sql_t;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

set maxptime=@maxptime;
if maxptime is not null or maxptime <> '' then
    set maxptime = replace(maxptime,"'",'');
    set maxpar_dday_t = maxptime;
else-- maxpar,partition_name max length is 64
    select 'table no partition is not allow' as error;
    leave label;-- table no partition is not allow
end if;
if days = 0 then
    set days=72;
    set maxpar_dday_t = date_format(now(),'%Y-%m-%d');
end if;
while incr_day < days DO
   if intervals = -1 then
       select date_add(date(maxpar_dday_t)-day(date(maxpar_dday_t))+1,interval 1 month) into maxpar_dday_t;
   else
        set maxpar_dday_t = date_format(adddate(maxpar_dday_t, INTERVAL intervals DAY),'%Y-%m-%d');
    end if;
    set maxpar_num = date_format(maxpar_dday_t,'%Y%m%d');

    set sql_t = concat('alter table ',db_name,'.',tab_name,' add partition (PARTITION p',maxpar_num,' values less than (''',maxpar_dday_t,''') )');
    set @sql_t1 = sql_t;
    PREPARE stmt1 FROM @sql_t1;
        EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    set incr_day = incr_day + 1;
end WHILE;
END//
DELIMITER ;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;