1.添加、删除分区存储过程脚本 2.event定时执行 3.创建分区 mysql create definer = `mc`@`10.%.%.%` procedure `newproc`(in v_tablename varchar(50),v_drop_interval int,v_add_interval int)begin/* ===================================================
1.添加、删除分区存储过程脚本
2.event定时执行
3.创建分区 mysql create definer = `mc`@`10.%.%.%` procedure `newproc`(in v_tablename varchar(50),v_drop_interval int,v_add_interval int)begin/* ======================================================================= * 系统名 : **系统 * 子系统名 : 创建,删除分区 * proc名 : proc_mc_addjust_partition * 概要 : 每天规定时间内创建和删除分区 * 例：加分区： alter table mc_stat_trends add partition (partition p20150417 values less than('2015-04-17')) * 删分区: alter table mc_stat_trends drop partition p20150310 * 改版履历 : * 版本 日期 作者名 备注 * 1.0.1 2015.03.01 ### 初次作成 * ======================================================================= */ -- 传入变量: v_drop_interval 删除日 v_add_interval 添加日 declare v_add_interval_1 int; set v_add_interval_1=v_add_interval+1; start transaction; set @s=concat('alter table ',v_tablename,' drop partition ', concat('p',date_format(date_sub(now(),interval v_drop_interval day),'%y%m%d'))); set @t=concat('alter table ',v_tablename,' add partition ','(','partition ', concat('p',date_format(date_add(now(),interval v_add_interval day),'%y%m%d')), ' values less than','(\'',date(date_add(now(),interval v_add_interval_1 day)),'\')',')'); select @s; prepare stmt from @s; execute stmt; deallocate prepare stmt; select @t; prepare stmt from @t; execute stmt; deallocate prepare stmt; commit;end;
create definer=`root`@`localhost` event `newevent`on schedule every 1 day starts '2014-02-24 01:10:00'on completion preserveenabledobegin call proc_mc_adjust_partition('mc_stat_trends',30,7);end;
alter table t_syslog partition by range (startdate)( partition p20150401 values less than ('2015-04-02'), partition p20150402 values less than ('2015-04-03'), partition p20150403 values less than ('2015-04-04'), partition p20150404 values less than ('2015-04-05'), partition p20150405 values less than ('2015-04-06'), partition p20150406 values less than ('2015-04-07'), partition p20150407 values less than ('2015-04-08'), partition p20150408 values less than ('2015-04-09'), partition p20150409 values less than ('2015-04-10'))