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
-- 传入变量: 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 p20150401VALUES LESS THAN ('2015-04-02'),
PARTITION p20150402VALUES LESS THAN ('2015-04-03'),
PARTITION p20150403VALUES LESS THAN ('2015-04-04'),
PARTITION p20150404VALUES LESS THAN ('2015-04-05'),
PARTITION p20150405VALUES LESS THAN ('2015-04-06'),
PARTITION p20150406VALUES LESS THAN ('2015-04-07'),
PARTITION p20150407VALUES LESS THAN ('2015-04-08'),
PARTITION p20150408VALUES LESS THAN ('2015-04-09'),
PARTITION p20150409VALUES LESS THAN ('2015-04-10')
)
本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源：php中文网