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

时间：2023-03-08 15:56:19
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 '',

  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();

相关文章