拆表用的MySQL存储过程

MySQL存储过程用途很广泛，下面就为您介绍拆表用的MySQL存储过程，希望对您学习MySQL存储过程方面能够有所帮助。

mysql表或分表的数据达到一定量（也许是800w或者1000w..）这个时候非常需要再分表，简单的办法是直接写
–假设根据user_id分表，分成64张

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次，做的无用功比较多，而且导致停机时间比较长。

虽然MySQL存储过程不是很熟，稍稍学习了下写了两个脚本，一个全量+一个增量脚本完成表的拆分。
线上库也实践了下，8个分表，每个分表1000W记录拆分到64个分表。
全量时间 150分钟，全量的时候几个分表可以一起跑，我是同时跑3个分表
增量时间每个分表4分钟 4个一起跑，一共是 8分钟搞定。这样停机时间加上应用的发布一共只需要20分钟就可以搞定了。

附脚本：

###################
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();  
 
#### 增量脚本 ######  
 
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;  
//  
 
-------