mysql定时新增分区_定时添加，删除Mysql分区

1.添加、删除分区存储过程脚本

2.Event定时执行

3.创建分区

--创建、删除分区

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

)