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

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 ;

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 ;