Mysql 存储过程+定时任务,完成分区自动维护
2024-10-10 13:58:40
建表:
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();
最新文章
- Docker安装CentOS
- JS高程3.基本概念(4)操作符
- Xcode清除缓存等
- 再见OI
- CSS——4种定位
- left join与on,where 结合一起用的异同
- python的http请求应用--每日签到
- Linux 目录操作和4中文件拷贝效率测试
- 安装cocoaPod 的问题
- ios7开发者必知
- c++ 计算程序运行时间
- 乐在其中设计模式(C#) - 桥接模式(Bridge Pattern)
- CUDA开发时用到的各种Linux命令
- ajax三级联动下拉菜单
- C++ Primer 有感(标准库map类型)
- 搭建Hadoop集群(生产环境)
- [Canvas]Bombman v1.00
- 【Zookeeper】源码分析之Watcher机制(二)之WatchManager
- thinkphp5的mkdir() Permission denied问题
- Python 函数进阶(filter/map/json/zip)