MySQL每天自动增加分区的实现 - 主机乐

ALTER TABLE tb_3a_huandan_detail PARTITION BY RANGE (TO_DAYS(ServiceStartTime))
(
PARTITION p20160523 VALUES LESS THAN (TO_DAYS(‘2016-05-23’)),
PARTITION p20160524 VALUES LESS THAN (TO_DAYS(‘2016-05-24’)),
PARTITION p20160525 VALUES LESS THAN (TO_DAYS(‘2016-05-25’)),
PARTITION p20160526 VALUES LESS THAN (TO_DAYS(‘2016-05-26’)),
PARTITION p20160527 VALUES LESS THAN (TO_DAYS(‘2016-05-27’))
)

2、分区存过如下：

DELIMITER $$

USE `nres`$$

DROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$

CREATE DEFINER=`nres`@`%` PROCEDURE `create_Partition_3Ahuadan`()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;

SELECT REPLACE(partition_name,’p’,”) INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name=’tb_3a_huandan_detail’ ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;

SET @s1=CONCAT(‘ALTER TABLE tb_3a_huandan_detail ADD PARTITION (PARTITION p’,@Max_date,’ VALUES LESS THAN (TO_DAYS (”’,DATE(@Max_date),”’)))’);

SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

COMMIT ;
END$$

DELIMITER ;

3、增加定时事件

DELIMITER ||
CREATE EVENT Partition_3Ahuadan_event
ON SCHEDULE
EVERY 1 day STARTS ‘2016-05-27 23:59:59’
DO
BEGIN

CALL nres.`create_Partition_3Ahuadan`;

END ||
DELIMITER ;

参考：

http://blog.csdn.net/m582445672/article/details/7670743

http://blog.chinaunix.net/uid-24086995-id-127389.html

到此这篇关于MySQL每天自动增加分区的实现的文章就介绍到这了,更多相关MySQL 自动增加分区内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！