定时添加，删除Mysql分区

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 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')
)