首页 > 代码库 > 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();
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。