1. 修改表结构

alter table exchange_transaction_data_bibox modify column id bigint;
alter table exchange_transaction_data_bibox drop PRIMARY key;
alter table exchange_transaction_data_bibox add PRIMARY key(id, batch_no);
alter table exchange_transaction_data_bibox modify column id bigint auto_increment;
show create table exchange_transaction_data_bibox;

2. 添加分区

ALTER TABLE exchange_transaction_data_bibox PARTITION BY RANGE(batch_no)(
  partition p20190624 values less than(201906240000),
  PARTITION p20190924 values less than(201909240000)
);

3. 创建存储过程。删除90天前的分区，创建两天后的分区

drop PROCEDURE if EXISTS UPDATE_EXCHANGE_TABLE_PARTITION;
DELIMITER $$
CREATE PROCEDURE UPDATE_EXCHANGE_TABLE_PARTITION(in table_name VARCHAR(50))
BEGIN
 #1. 两天之后的long
 declare BATCH_NO BIGINT;
 declare BATCH_NO_NITY_BEFORE BIGINT;
 declare partition_name VARCHAR(20);
 declare partition_name90 VARCHAR(20);
    
 SET BATCH_NO = CAST(DATE_FORMAT(DATE_ADD(now(), INTERVAL 2 DAY), '%Y%m%d') as signed);
 SET BATCH_NO_NITY_BEFORE = CAST(DATE_FORMAT(DATE_SUB(now(), INTERVAL 91 DAY), '%Y%m%d') as signed);
 set partition_name = CONCAT('p', BATCH_NO);
 set partition_name90 = CONCAT('p', BATCH_NO_NITY_BEFORE);
    
 SET BATCH_NO = BATCH_NO * 10000;
 set @drop_part = CONCAT('alter table ', table_name, ' drop partition ', partition_name90);
 set @create_part = CONCAT('alter table ', table_name, ' add PARTITION (partition ', partition_name ,' values less than(', BATCH_NO ,'))');
    
 SELECT @drop_part,@create_part;
    
 PREPARE stmt FROM @create_part;
  EXECUTE stmt;
    
 PREPARE stmt FROM @drop_part;
  EXECUTE stmt;
END $$
DELIMITER ;

4. 创建定时任务

调用上面的存储过程，删除分区，创建分区

CREATE EVENT INSERT_PARTITION_EVENT ON SCHEDULE 
EVERY 1 DAY STARTS '2019-09-24 00:01:00'
DO
    CALL UPDATE_EXCHANGE_TABLE_PARTITION('exchange_transaction_data_bibox');