2014-07-13 15:54:38
来 源
中存储网
存储过程
从来没有仔细写个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 ;

声明: 此文观点不代表本站立场;转载须要保留原文链接;版权疑问请联系我们。