MySQL存儲(chǔ)過(guò)程用途很廣泛，下面就為您介紹拆表用的MySQL存儲(chǔ)過(guò)程，希望對(duì)您學(xué)習(xí)MySQL存儲(chǔ)過(guò)程方面能夠有所幫助。

mysql表或分表的數(shù)據(jù)達(dá)到一定量（也許是800w或者1000w..）這個(gè)時(shí)候非常需要再分表，簡(jiǎn)單的辦法是直接寫(xiě)
--假設(shè)根據(jù)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 搞定。  但是這個(gè)一張表被全表掃描了64次，做的無(wú)用功比較多，而且導(dǎo)致停機(jī)時(shí)間比較長(zhǎng)。

雖然MySQL存儲(chǔ)過(guò)程不是很熟，稍稍學(xué)習(xí)了下寫(xiě)了兩個(gè)腳本，一個(gè)全量+一個(gè)增量腳本完成表的拆分。
線上庫(kù)也實(shí)踐了下，8個(gè)分表，每個(gè)分表1000W記錄拆分到64個(gè)分表。
全量 時(shí)間 150分鐘，全量的時(shí)候幾個(gè)分表可以一起跑，我是同時(shí)跑3個(gè)分表
增量 時(shí)間 每個(gè)分表4分鐘 4個(gè)一起跑，一共是 8分鐘搞定。 這樣停機(jī)時(shí)間加上應(yīng)用的發(fā)布一共只需要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;  

--定義游標(biāo)（要分拆的表，定義一個(gè)數(shù)量的截止時(shí)間）

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;  

--根據(jù)取模字段獲取數(shù)據(jù)存在的表

select mod(v_spid,64) into c_table;  

--組裝動(dòng)態(tài)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;  
--執(zhí)行sql  
EXECUTE STMT using @vv_id;  
DEALLOCATE PREPARE STMT;  
end if;  
set ii=i+1;  
 

--100條提交一次，以提高效率，記得執(zhí)行存儲(chǔ)過(guò)程前設(shè)置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;  
 

--判斷數(shù)據(jù)是否已經(jīng)存在

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;       

--如果數(shù)據(jù)已經(jīng)存在，則先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;  
//  
 
-------  
 

如果全量和增量之間的時(shí)間拖的比較長(zhǎng)，那么可以設(shè)置時(shí)間，多做幾次增量已縮短最后的停機(jī)時(shí)間，你懂的。。。
call sp_xf_add_item()//
 

【編輯推薦】

深入探討MySQL鎖機(jī)制

MySQL字段中的集合

MySQL字段類(lèi)型簡(jiǎn)介

Mysql外鍵用法分析

詳解MySQL數(shù)據(jù)表類(lèi)型