写了个Mysql的存储过程

从来没有仔细写个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 ;