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

成都創(chuàng)新互聯(lián)長(zhǎng)期為1000多家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為秦皇島企業(yè)提供專業(yè)的網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站設(shè)計(jì)，秦皇島網(wǎng)站改版等技術(shù)服務(wù)。擁有十載豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

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字段類型簡(jiǎn)介

Mysql外鍵用法分析

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

文章名稱：拆表用的MySQL存儲(chǔ)過(guò)程
標(biāo)題網(wǎng)址：http://uogjgqi.cn/article/dhjigeg.html