时间:2021-07-01 10:21:17 帮助过:21人阅读
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.添加、删除分区存储过程脚本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 PRESERVE ENABLE DO BEGIN 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') )