通用分表存储过程-阿里云开发者社区

通用分表存储过程

2016-05-12 2069

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

使用创建分表存储过程

    Java代码  
     
   
set @field_list ='  
  `syslog_id` int(11) NOT NULL AUTO_INCREMENT,  
  `create_user` varchar(32) DEFAULT NULL,  
  PRIMARY KEY (`syslog_id`)  
';    
call branch_table('test', @field_list, 4, 2);  

创建分表存储过程

    Java代码  
     
   
DROP PROCEDURE IF EXISTS `branch_table`;  
CREATE PROCEDURE `branch_table`(  
    IN     p_table_name   VARCHAR(200),       
    IN     p_field_list   VARCHAR(2048),   
    IN     p_branch_size  INT,                        
    in     p_lpad         INT  
)  
BEGIN  
    /*定义变量*/  
    DECLARE m_begin_row INT DEFAULT 0;  
      
    WHILE m_begin_row<p_branch_size DO    
        /*构造语句*/     
        SET @MAIN_STRING = CONCAT('CREATE TABLE ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0), '(', p_field_list ,')ENGINE=InnoDB DEFAULT CHARSET=utf8;');  
       
        /*预处理*/  
        PREPARE main_stmt FROM @MAIN_STRING;  
        EXECUTE main_stmt;  
        SET m_begin_row=m_begin_row+1;  
    END WHILE;  
END;  

前期没有分表数据量太大后期拆表用的MySQL存储过程

简单的办法是直接写
--假设根据user_id分表，分成64张

    Java代码  
     
   
insert into table_new_0000 select * from table_old where mod(user_id,64)=0;    
insert into table_new_0001 select * from table_old where mod(user_id,64)=1;  

一共64条sql，OK 搞定。但是这个一张表被全表扫描了64次，做的无用功比较多，而且导致停机时间比较长

创建分表

    Java代码  
     
   
delimeter //  
--- 全量脚本：  
CREATE PROCEDURE  sp_xf_move_item()    
begin    
declare v_exit int default 0;    
declare v_spid bigint;    
declare v_id bigint;    
declare i int default 0;    
declare c_table int;  
--定义游标（要分拆的表，定义一个数量的截止时间）  
  
declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified < '2010-8-25 00:00:00';    
declare  continue handler for not found set v_exit=1;    
open c_ids;    
repeat    
--将需要的值装入变量  
  
fetch c_ids into v_id,v_spid;    
if v_exit = 0 then    
set @vv_id = v_id;    
--根据取模字段获取数据存在的表  
  
select mod(v_spid,64) into c_table;    
--组装动态sql  
SET @SQL_CONTEXT =    
CONCAT('insert into item_record_',    
LPAD(c_table, 4, 0),    
' select * from item_records_0000 where id = ?');    
   
PREPARE STMT FROM @SQL_CONTEXT;    
--执行sql    
EXECUTE STMT using @vv_id;    
DEALLOCATE PREPARE STMT;    
end if;    
set ii=i+1;    
   
--100条提交一次，以提高效率，记得执行存储过程前设置auto_commit  
  
if mod(i,100)=0 then commit;    
end if;    
until v_exit=1   
end repeat;    
close c_ids;    
commit;    
end;    
//   

set auto_commit=0;
call sp_xf_move_item();

添加数据

    Java代码  
     
   
#### 增量脚本 ######    
CREATE PROCEDURE sp_xf_add_item()    
begin    
declare v_exit int default 0;    
declare v_spid bigint;    
declare v_id bigint;    
declare i int default 0;    
declare c_table int;    
declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified >= '2010-8-25 00:00:00';    
declare  continue handler for not found set v_exit=1;    
open c_ids;    
repeat    
   
fetch c_ids into v_id,v_spid;    
if v_exit = 0 then    
set @vv_id = v_id;    
set @v_row=0;    
select mod(v_spid,64) into c_table;    
   
--判断数据是否已经存在  
  
SET @SQL_C =    
CONCAT('select count(*) into @v_row from item_record_',    
LPAD(c_table, 4, 0),    
' where id = ?');    
   
PREPARE STMT_C FROM @SQL_C;    
EXECUTE STMT_C using @vv_id;    
DEALLOCATE PREPARE STMT_C;                           
   
SET @SQL_INSERT =    
CONCAT('insert into bbc_item_record_',    
LPAD(c_table, 4, 0),    
' select * from item_records_0000 where id = ?');    
   
PREPARE STMT_I FROM @SQL_INSERT;             
   
SET @SQL_DELETE =    
CONCAT('DELETE FROM bbc_item_record_',    
LPAD(c_table, 4, 0),    
' where id = ?');    
PREPARE STMT_D FROM @SQL_DELETE;         
--如果数据已经存在，则先delete在insert               
  
if @v_row>0 then     
   
EXECUTE STMT_D using @vv_id;    
DEALLOCATE PREPARE STMT_D;    
   
end if;    
EXECUTE STMT_I using @vv_id;    
DEALLOCATE PREPARE STMT_I;           
   
end if;    
set ii=i+1;    
if mod(i,100)=0 then commit;    
end if;    
until v_exit=1   
end repeat;    
close c_ids;    
commit;    
end;    
//    

call sp_xf_add_item()

通用分表存储过程

热门文章

最新文章

相关电子书

相关实验场景