首页 > 代码库 > Mysql 存储过程+定时任务,完成分区自动维护

Mysql 存储过程+定时任务,完成分区自动维护

建表:

drop table if exists terminal_parameter;
CREATE TABLE `terminal_parameter` (
  `terminal_parameter_id` int(11) NOT NULL AUTO_INCREMENT,
 ....................
  `createtime` datetime NOT NULL,
  `userid` int(8) NOT NULL,
  `terminal_id` int(8) DEFAULT NULL,
  `state` char(1) DEFAULT 0,
  
  PRIMARY KEY (`terminal_parameter_id`,`createtime`),
  KEY `idx_createtime` (`createtime`),
  KEY `idx_terminal_id` (`terminal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p20101115 VALUES LESS THAN (TO_DAYS(2010-11-15)),
PARTITION p20101130 VALUES LESS THAN (TO_DAYS(2010-11-30)),
PARTITION p20101215 VALUES LESS THAN (TO_DAYS(2010-12-15)),
PARTITION p20101231 VALUES LESS THAN (TO_DAYS(2010-12-31)),
PARTITION p20110115 VALUES LESS THAN (TO_DAYS(2011-01-15)),
PARTITION p20110131 VALUES LESS THAN (TO_DAYS(2011-01-31)),
PARTITION p20110215 VALUES LESS THAN (TO_DAYS(2011-02-15)),
PARTITION p20110228 VALUES LESS THAN (TO_DAYS(2011-02-28)),
PARTITION p20110315 VALUES LESS THAN (TO_DAYS(2011-03-15)),
PARTITION p20110331 VALUES LESS THAN (TO_DAYS(2011-03-31)),
PARTITION p20110415 VALUES LESS THAN (TO_DAYS(2011-04-15)),
PARTITION p20110430 VALUES LESS THAN (TO_DAYS(2011-04-30))
); 

维护分区的存储过程:

* 每隔15天执行一次
/* 程序功能:循环使用分区,每半个月一个分区,保留6个月的数据
 时间:2010-11-09 */
 drop procedure if exists Set_Partition;
 create procedure Set_Partition()
 begin
/* 事务回滚,其实放这里没什么作用,ALTER TABLE是隐式提交,回滚不了的。*/
    declare exit handler for sqlexception rollback;
    start TRANSACTION;
 
/* 到系统表查出这个表的最大分区,得到最大分区的日期。在创建分区的时候,名称就以日期格式存放,方便后面维护 */
    select REPLACE(partition_name,p,‘‘) into @P12_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=mydb_1 and table_name=terminal_parameter order by partition_ordinal_position DESC limit 1;

/* 判断最大分区的时间段,如果是前半个月的,那么根据情况需要加13,14,15,16天
   如果是后半个月的,那么直接加15天。 +0 是为了把日期都格式化成YYYYMMDD这样的格式*/
    IF (DAY(@P12_Name)<=15) THEN
       CASE day(LAST_DAY(@P12_name))
          WHEN 31 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 16 DAY))+0 ;
          WHEN 30 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 15 DAY))+0 ;
          WHEN 29 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 14 DAY))+0 ; 
          WHEN 28 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 13 DAY))+0 ; 
       END CASE;
    ELSE
       set @Max_date= date(DATE_ADD(@P12_Name+0, INTERVAL 15 DAY))+0;
    END IF;

/* 修改表,在最大分区的后面增加一个分区,时间范围加半个月 */
    SET @s1=concat(ALTER TABLE terminal_parameter ADD PARTITION (PARTITION p,@Max_date, VALUES LESS THAN (TO_DAYS (‘‘‘,date(@Max_date),‘‘‘))));
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

/* 取出最小的分区的名称,并删除掉 。
    注意:删除分区会同时删除分区内的数据,慎重 */
    select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=mydb_1 and table_name=terminal_parameter order by partition_ordinal_position limit 1;
    SET @s=concat(ALTER TABLE terminal_parameter DROP PARTITION ,@P0_Name);
    PREPARE stmt1 FROM @s; 
    EXECUTE stmt1; 
    DEALLOCATE PREPARE stmt1; 

/* 提交 */
    COMMIT ;
 end;
 

定时任务:

CREATE EVENT e_Set_Partition
          ON SCHEDULE
          EVERY 15 day STARTS 2011-04-30 23:59:59 
          DO
          call Set_Partition();