mysql 存储过程示例1

mysql 存储过程示例：

该存储过程用来将原有未分表的数据迁移到对应子表中

p_baseTable  基表，p_baseCol  数据分发基于那个字段（必须是日期字段），p_year p_month 起始年月

delimiter $$
create procedure shardingTable(IN p_baseTable char(80),IN p_baseCol char(80),IN p_year int,IN p_month int)
begin
    declare v_year int default p_year;
    declare v_month int default p_month;
    declare v_strYear char(4);
    declare v_strMonth char(2);
    declare v_strYearMonth char(6);
    
    declare v_cur_year int;
    declare v_cur_month int;
    declare v_subTable varchar(80);
    
    declare v_strSTMT varchar(300);
    
    select year(now()) into v_cur_year;
    select month(now()) into v_cur_month;
    SHARDING_TABLE_DATE:loop
        if v_year>v_cur_year or (v_year=v_cur_year and v_month>v_cur_month)then
            leave SHARDING_TABLE_DATE;
        end if;
        select CAST(v_year as char) into v_strYear;

        if v_month>9 then
            set v_strMonth=CAST(v_month as char);
        else
            set v_strMonth=concat('0',CAST(v_month as char));
        end if;

        set v_strYearMonth=concat(v_strYear,v_strMonth);
        
        set v_subTable=concat(p_baseTable,'_',v_strYearMonth);
        set v_strSTMT=concat(" insert into ",v_subTable," select * from ",p_baseTable," where DATE_FORMAT(",p_baseCol,",'%Y%m')=? ;");
        set @v_temp = v_strSTMT;
        set @v_temp2 = v_strYearMonth;
        -- select v_strSTMT;
        
        prepare myStmt from @v_temp; -- 不知道为什么这里不能直接使用 v_strSTMT，在编写时，使用v_strSTMT会使创建过程失败
        execute myStmt using @v_temp2; -- v_strYearMonth 同上
        deallocate prepare myStmt;
        
        set v_month=v_month+1;
        if v_month>12 then
            set v_year=v_year+1;
            set v_month=1;
        end if;
        
    end loop;
end$$

delimiter ;

call shardingTable('xxx','DATE_UPDATED',2017,7);
call shardingTable('xxx','logCreateDate',2017,7);
call shardingTable('xxx','DATE_CREATED',2017,9);

drop procedure shardingTable;