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; // -------
如果全量和增量之间的时间拖的比较长，那么可以设置时间，多做几次增量已缩短最后的停机时间，你懂的。。。
call sp_xf_add_item()//