通用分表存储过程

使用创建分表存储过程

Java代码  

1. set @field_list ='  
2.   `syslog_id` int(11) NOT NULL AUTO_INCREMENT,  
3.   `create_user` varchar(32) DEFAULT NULL,  
4.   PRIMARY KEY (`syslog_id`)  
5. ';    
6. call branch_table('test', @field_list, 4, 2);  

创建分表存储过程

Java代码  

1. DROP PROCEDURE IF EXISTS `branch_table`;  
2. CREATE PROCEDURE `branch_table`(  
3.     IN     p_table_name   VARCHAR(200),       
4.     IN     p_field_list   VARCHAR(2048),   
5.     IN     p_branch_size  INT,                        
6.     in     p_lpad         INT  
7. )  
8. BEGIN  
9.     /*定义变量*/  
10.     DECLARE m_begin_row INT DEFAULT 0;  
11.       
12.     WHILE m_begin_row<p_branch_size DO    
13.         /*构造语句*/     
14.         SET @MAIN_STRING = CONCAT('CREATE TABLE ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0), '(', p_field_list ,')ENGINE=InnoDB DEFAULT CHARSET=utf8;');  
15.        
16.         /*预处理*/  
17.         PREPARE main_stmt FROM @MAIN_STRING;  
18.         EXECUTE main_stmt;  
19.         SET m_begin_row=m_begin_row+1;  
20.     END WHILE;  
21. END;  

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

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

Java代码  

1. insert into table_new_0000 select * from table_old where mod(user_id,64)=0;    
2. insert into table_new_0001 select * from table_old where mod(user_id,64)=1;  

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

创建分表

Java代码  

1. delimeter //  
2. --- 全量脚本：  
3. CREATE PROCEDURE  sp_xf_move_item()    
4. begin    
5. declare v_exit int default 0;    
6. declare v_spid bigint;    
7. declare v_id bigint;    
8. declare i int default 0;    
9. declare c_table int;  
10. --定义游标（要分拆的表，定义一个数量的截止时间）  
11.   
12. declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified < '2010-8-25 00:00:00';    
13. declare  continue handler for not found set v_exit=1;    
14. open c_ids;    
15. repeat    
16. --将需要的值装入变量  
17.   
18. fetch c_ids into v_id,v_spid;    
19. if v_exit = 0 then    
20. set @vv_id = v_id;    
21. --根据取模字段获取数据存在的表  
22.   
23. select mod(v_spid,64) into c_table;    
24. --组装动态sql  
25. SET @SQL_CONTEXT =    
26. CONCAT('insert into item_record_',    
27. LPAD(c_table, 4, 0),    
28. ' select * from item_records_0000 where id = ?');    
29.    
30. PREPARE STMT FROM @SQL_CONTEXT;    
31. --执行sql    
32. EXECUTE STMT using @vv_id;    
33. DEALLOCATE PREPARE STMT;    
34. end if;    
35. set ii=i+1;    
36.    
37. --100条提交一次，以提高效率，记得执行存储过程前设置auto_commit  
38.   
39. if mod(i,100)=0 then commit;    
40. end if;    
41. until v_exit=1   
42. end repeat;    
43. close c_ids;    
44. commit;    
45. end;    
46. //   

set auto_commit=0;  
call sp_xf_move_item(); 

添加数据

Java代码  

1. #### 增量脚本 ######    
2. CREATE PROCEDURE sp_xf_add_item()    
3. begin    
4. declare v_exit int default 0;    
5. declare v_spid bigint;    
6. declare v_id bigint;    
7. declare i int default 0;    
8. declare c_table int;    
9. declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified >= '2010-8-25 00:00:00';    
10. declare  continue handler for not found set v_exit=1;    
11. open c_ids;    
12. repeat    
13.    
14. fetch c_ids into v_id,v_spid;    
15. if v_exit = 0 then    
16. set @vv_id = v_id;    
17. set @v_row=0;    
18. select mod(v_spid,64) into c_table;    
19.    
20. --判断数据是否已经存在  
21.   
22. SET @SQL_C =    
23. CONCAT('select count(*) into @v_row from item_record_',    
24. LPAD(c_table, 4, 0),    
25. ' where id = ?');    
26.    
27. PREPARE STMT_C FROM @SQL_C;    
28. EXECUTE STMT_C using @vv_id;    
29. DEALLOCATE PREPARE STMT_C;                           
30.    
31. SET @SQL_INSERT =    
32. CONCAT('insert into bbc_item_record_',    
33. LPAD(c_table, 4, 0),    
34. ' select * from item_records_0000 where id = ?');    
35.    
36. PREPARE STMT_I FROM @SQL_INSERT;             
37.    
38. SET @SQL_DELETE =    
39. CONCAT('DELETE FROM bbc_item_record_',    
40. LPAD(c_table, 4, 0),    
41. ' where id = ?');    
42. PREPARE STMT_D FROM @SQL_DELETE;         
43. --如果数据已经存在，则先delete在insert               
44.   
45. if @v_row>0 then     
46.    
47. EXECUTE STMT_D using @vv_id;    
48. DEALLOCATE PREPARE STMT_D;    
49.    
50. end if;    
51. EXECUTE STMT_I using @vv_id;    
52. DEALLOCATE PREPARE STMT_I;           
53.    
54. end if;    
55. set ii=i+1;    
56. if mod(i,100)=0 then commit;    
57. end if;    
58. until v_exit=1   
59. end repeat;    
60. close c_ids;    
61. commit;    
62. end;    
63. //    

 call sp_xf_add_item()