从来没有仔细写个Mysql的存储过程，正好花时间研究了一下。写任何程序最基本的是先要有详细注释，有log记录，有异常异常等等，当然Mysql的异常处理机制很差，搞了半天都没有满意的结果。功能很简单，自动修改merge表的定义，并清理过期表的数据。DELIMIT
从来没有仔细写个Mysql的存储过程，正好花时间研究了一下。
写任何程序最基本的是先要有详细注释，有log记录，有异常异常等等，当然Mysql的异常处理机制很差，搞了半天都没有满意的结果。功能很简单，自动修改merge表的定义，并清理过期表的数据。
DELIMITER $$DROP PROCEDURE IF EXISTS sp_rename_mergetable $$
create procedure sp_rename_mergetable(in p_tablename varchar(60),in p_begincount int,in p_endcount int)
BEGIN
/*
Created by dingyuan
Dated:2009-03-27
用途：用来更改merge表的定义，并清理过期表的数据。
p_tablename指的是你要merge的基础表,如sns_whovisitme。
p_begincount指的是你需要merge的表区间，比如sns_whovisitme分了64个表,库1分表从0，到31，那p_begincount设置为0，p_endcount设置为31.
本程序只支持子表为3个，后缀为_01,_02,_03的情况，每次只会merge两张表。
create table tmp_debug(spname varchar(50),msg varchar(1000),vcount int); --错误记录表
create table tmp_time(vsql varchar(200),gmt_create datetime,vcount int);--log表
记录哪些表需要重新merge，这次需要merge哪个子表（thismerge）
如thismerge=1表示需要清理_01开头的表数据，并merge _02，_03开头的子表
create table tmp_merge_status(tablename varchar(100),thismerge int,status int,gmt_modified datetime);
insert into tmp_merge_status values('activity_message',1,1,now());
调用
call sp_rename_mergetable('activity_message',0,7);
用于debug
insert into tmp_time(gmt_create,vcount) values(now(),1);
*/
declare v_tablename varchar(60);
declare v_begincount int;
declare v_endcount int;
declare i int ;
declare v_trunmonth varchar(5);
declare v_addmonth1 varchar(5);
declare v_addmonth2 varchar(5);
declare v_thismerge int;
declare v_count int default 0;
declare v_error int DEFAULT 0;
declare continue handler for not found,sqlexception set v_error=1;
-- 需要重新merge的表名
set v_tablename=p_tablename;
-- 指定你需要merge的表区间
set v_begincount =p_begincount; set v_endcount =p_endcount;
-- 判断基础表是否有数据select count(*) into v_count from tmp_merge_status where tablename=v_tablename and status=1;
if v_count=1 then-- 取这次需要清除的子表，并merge其他的两张表
select thismerge into v_thismerge from tmp_merge_status where tablename=v_tablename and status=1;
-- v_trunmonth表示需要删除的表，v_addmonth表示需要重新merge的子表if v_thismerge=1 THEN
SET v_trunmonth='_01'; SET v_addmonth1='_02'; SET v_addmonth2='_03';
elseif v_thismerge=2 THEN
SET v_trunmonth='_02'; SET v_addmonth1='_03'; SET v_addmonth2='_01';
elseif v_thismerge=3 THEN
SET v_trunmonth='_03'; SET v_addmonth1='_01'; SET v_addmonth2='_02';
end if;
-- status置为-1表示正在处理merge中，防止并发，thismerge更新为下次需要清理的子表。
update tmp_merge_status set status=-1,gmt_modified=now() where tablename=v_tablename and status=1;
-- 开始merge，从begincount表开始merge，一直到endcount为止
set i=v_begincount;
-- 记录log表
insert into tmp_time(vsql,gmt_create,vcount) values(concat('------------',v_tablename,' begin merge---'),now(),i);
error_label:
WHILE i <= v_endcount DO
-- 如sns_whovisitme_0000
set @vtable=concat(v_tablename,'_',lpad(i,4,'0')) ;
-- 如alter table sns_whovisitme_0000 union(sns_whovisitme_0000_02,sns_whovisitme_0000_03)
set @mergesql =concat('alter table ',@vtable,' union(',@vtable,v_addmonth1,',',@vtable,v_addmonth2,')');
-- 如truncate table sns_whovisitme_0001_01
set @trunsql =concat('truncate table ',@vtable,v_trunmonth);
-- insert into tmp_time(vsql,gmt_create,vcount) values(@vtable,now(),i);
insert into tmp_time(vsql,gmt_create,vcount) values(@mergesql,now(),i);
insert into tmp_time(vsql,gmt_create,vcount) values(@trunsql,now(),i);
-- 执行merge脚本
prepare stmt1 from @mergesql;
execute stmt1;
DEALLOCATE PREPARE stmt1;
-- 执行truncate 脚本
prepare stmt2 from @trunsql;
execute stmt2;
DEALLOCATE PREPARE stmt2;
set @mergesql='';
set @trunsql='';
set i=i+1;
-- 异常处理
if v_error=1 then
insert into tmp_debug(spname,msg,vcount,vdate)
values('sp_rename_table','ERROR:not found,sqlexception',i,now());
leave error_label;
end if;
END WHILE;
-- 记录log表
delete from tmp_time where gmt_create
insert into tmp_time(vsql,gmt_create,vcount) values(concat('------------',v_tablename,' end merge---'),now(),i);
-- 更新状态，表示merge成功
update tmp_merge_status set status=1,thismerge=mod(thismerge,3)+1,gmt_modified=now() where tablename=v_tablename and status=-1;
else
insert into tmp_debug(spname,msg,vdate)
values(v_tablename,'ERROR:tmp_merge_status no metadata,please check',now());
end if;
END$$
DELIMITER ;
声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。
