- --创建、删除分区
- 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
- * ======================================================================= */
- -- 传入变量: 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;
- --Event定时执行
- 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')
- )
- --该片段来自于http://www.codesnippet.cn/detail/1004201512174.html
来源: http://www.codesnippet.cn/detail/1004201512174.html