mysql表分区:Mysql实现定时建立删除分区

最近需要把个oralce数据库向mysql数据库移植oracle库用到了job和存储过程mysql中没有job所有要用新功能event代替下这个是5.1以后才有新功能所以稍微研究了下

原来需要实现功能是每间隔段时间就会建立几个分区并删除几个分区以保持数据库只存段时间内数据以下是实现步骤



1)首先我们建立表这个时候就遇到了点问题mysql PARTITION BY RANGE 时候只支持按数字类型进行分区所以不支持直接用date类型分区而且有很多不能在PARTITION BY RANGE 中使用所以我使用了到公元元年描述作为划分分区依据

CREATE TABLE `data_table` (
`dtime` datetime NOT NULL,
`alarm_no` (8) NOT NULL,
`alarm_level` (1) NOT NULL,
`color_index` (1) NOT NULL,
`server_name` varchar(64) NOT NULL,
`process_name` varchar(64) NOT NULL,
`desciption` varchar(600) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 /*!50100 PARTITION BY RANGE (TO_DAYS(dtime)*24*60*60+TIME_TO_SEC(dtime)) (PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM) */;

2)然后我们建立存储过程

这里是添加表部分实现第2个存储过程第个存储过程control表中存是些参数值得注意时有可能插入重复分区所有我们进行了异常处理如果出现重复分区异常我们会跳过这次循环继续

CREATE PROCEDURE `gen_partion_script_proc`(spec_date DATETIME, table_name VARCHAR(64), table_space VARCHAR(64), prefix VARCHAR(64), key_type INTEGER(10), qty_day INTEGER(10))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
declare repeated default 0;
declare result varchar(256);
declare par_name varchar(128);
declare verrinfo varchar(200);
declare fdbk (10);
declare v_sqlcursor (10);
declare v_num (10);
declare i eger(10);
declare continue handler for 1493 repeated=1;
declare continue handler for 1517 repeated=1;
i = 1;
dayloop :loop
i > qty_day then leave dayloop;
end ;
par_name = concat(prefix,
date_format(spec_date, '%Y%m%d'),
when char_length(i) < 2 then concat('0', i) i end);
key_type = 1 then
@par_value = round((to_days(spec_date) + i / qty_day) * 24 * 60 * 60);
end ;
result = concat('alter table ',
table_name,
' add partition (partition ',
par_name,
' values less than (',
@par_value,
'))');
@v_sql = result;
prepare stmt from @v_sql;
execute stmt;
i = i + 1;
end loop;
end;



CREATE PROCEDURE `procaddpart`(p_datefrom DATETIME, p_days INTEGER(10))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
declare done default 0;
declare v_table varchar(64);
declare v_tablespace varchar(64);
declare v_prefix varchar(64);
declare v_keytype eger(10);
declare v_qty_day eger(10);
declare verrinfo varchar(200);
declare i eger(10);
declare partion_control cursor for
select table_name, tablesapace, prefix, qty_day, key_type
from control;
declare continue handler for not found done = 1;
i=1;
open partion_control;
repeat
fetch partion_control o v_table, v_tablespace, v_prefix, v_qty_day, v_keytype;
until done
end repeat;
close partion_control;
dayloop:loop
i>p_days then
leave dayloop;
end ;
call gen_partion_script_proc (adddate(adddate(p_datefrom,i), -1),
v_table,
v_tablespace,
v_prefix,
v_keytype,
v_qty_day
);
i=i+1;
end loop;
end;

下面是删除分区过程

CREATE PROCEDURE `remove_partition_script_proc`(spec_date DATETIME, table_name VARCHAR(64), prefix VARCHAR(64), qty_day INTEGER(10))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
declare par_name varchar(128);
declare result varchar(256);
declare i eger(10);
declare repeated default 0;
declare continue handler for 1507 repeated=1;
i=1;
dayloop:loop
i>qty_day then
leave dayloop;
end ;
par_name=concat(prefix,date_format(spec_date,'%Y%m%d'), when char_length(i)<2 then concat('0',i) i end);
result=concat('alter table ',table_name,' drop partition ',par_name);
@v_sql=result;
prepare stmt from @v_sql;
execute stmt;
i=i+1;
end loop;
end;



CREATE PROCEDURE `procdelpart`(p_date DATETIME)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
declare done default 0;
declare v_table varchar(64);
declare v_prefix varchar(64);
declare v_qty_day eger(10);
declare partion_control cursor for
select table_name, prefix, qty_day
from partition_control_tab;
declare continue handler for not found done = 1;
@i=0;
open partion_control;
repeat


fetch control o v_table,v_prefix,v_qty_day;
not done then
@i=@i+1;
call remove_partition_script_proc(p_date,v_table,v_prefix,v_qty_day);
end ;
until done
end repeat;
close partion_control;
commit;
end;CREATE PROCEDURE `report_job`(pdate DATETIME)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
commit;
select data_keeped_days
o @keeped_days
from config;
@keeped_days > 0
then
call procdelpart (adddate(pdate,-@keeped_days));
end ;
commit;
end;

3)最后我们建立event跟 oraclejob作用样定时执行任务在建立event以后我们最好把mysql服务器上my.cnf或my.ini里面[mysqld]里面加上event_scheduler=1这样就默认开启event功能了

CREATE EVENT event1
ON SCHEDULE EVERY 1 day
STARTS ADDDATE(CURDATE+INTERVAL 1 DAY,INTERVAL 90 minute )
DO call procaddpart(now,3);

CREATE EVENT event2
ON SCHEDULE EVERY 1 day
STARTS ADDDATE(CURDATE+INTERVAL 1 DAY,INTERVAL 150 minute )
DO call report_job(ADDDATE(now,-1));


这样我们就完成了这个功能每天早上就会自动执行增加删除分区操作了！

感觉mysql越来越强大了功能也越来越接近企业级数据库希望大家也能更加重视mysql数据库研究他并发掘他潜力

延伸阅读

• 2010-12-28-- mysql分区,MySQL Partition（分区）扫盲
• 2010-12-9-- mysql分区,深入了解MySQL 5.5分区功能增强
• 2011-3-12-- mysql删除,新增 修改 删除 查询 (PHP+MySQL)
• 2008-12-4-- mysql数据库:MySQL数据库的用户帐号管理基础知识
• 2009-2-12-- mysql数据库:MySQL中文参考手册-- 创造并使用一个数据库
• 2009-2-12-- mysql数据库:MYSQL数据库初学者使用指南
• 2009-2-12-- mysql数据库:在Linux高负载下mysql数据库彻底优化
• 2009-2-12-- mysql数据库:数据库中MySQL各种字段的取值范围
• 2008-12-4-- mysql数据库:Linux应用集成MySQL数据库访问技巧
• 2009-2-12-- mysql数据库:如何向mysql数据库的表中录入数据