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

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

    insertintotable_new_0000select*fromtable_oldwheremod(user_id,64)=0; insertintotable_new_0001select*fromtable_oldwheremod(user_id,64)=1; ...

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

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

附脚本：

################### delimeter //

———– — 全量脚本：

    CREATEPROCEDUREsp_xf_move_item() begin declarev_exitintdefault0; declarev_spidbigint; declarev_idbigint; declareiintdefault0; declarec_tableint;

–定义游标（要分拆的表，定义一个数量的截止时间）

    declarec_idscursorforselectid,user_idfromitem_records_0000wheregmt_modified<'2010-8-2500:00:00'; declarecontinuehandlerfornotfoundsetv_exit=1; openc_ids; repeat

–将需要的值装入变量

    fetchc_idsintov_id,v_spid; ifv_exit=0then set@vv_id=v_id;

–根据取模字段获取数据存在的表

    selectmod(v_spid,64)intoc_table;

–组装动态sql

    SET@SQL_CONTEXT= CONCAT('insertintoitem_record_', LPAD(c_table,4,0), 'select*fromitem_records_0000whereid=?'); PREPARESTMTFROM@SQL_CONTEXT; --执行sql EXECUTESTMTusing@vv_id; DEALLOCATEPREPARESTMT; endif; setii=i+1;

–100条提交一次，以提高效率，记得执行存储过程前设置auto_commit

    ifmod(i,100)=0thencommit; endif; untilv_exit=1endrepeat; closec_ids; commit; end; // ----------- setauto_commit=0; callsp_xf_move_item(); ####增量脚本###### CREATEPROCEDUREsp_xf_add_item() begin declarev_exitintdefault0; declarev_spidbigint; declarev_idbigint; declareiintdefault0; declarec_tableint; declarec_idscursorforselectid,supplier_idfromitem_records_0000wheregmt_modified>='2010-8-2500:00:00'; declarecontinuehandlerfornotfoundsetv_exit=1; openc_ids; repeat fetchc_idsintov_id,v_spid; ifv_exit=0then set@vv_id=v_id; set@v_row=0; selectmod(v_spid,64)intoc_table;

–判断数据是否已经存在

    SET@SQL_C= CONCAT('selectcount(*)into@v_rowfromitem_record_', LPAD(c_table,4,0), 'whereid=?'); PREPARESTMT_CFROM@SQL_C; EXECUTESTMT_Cusing@vv_id; DEALLOCATEPREPARESTMT_C; SET@SQL_INSERT= CONCAT('insertintobbc_item_record_', LPAD(c_table,4,0), 'select*fromitem_records_0000whereid=?'); PREPARESTMT_IFROM@SQL_INSERT; SET@SQL_DELETE= CONCAT('DELETEFROMbbc_item_record_', LPAD(c_table,4,0), 'whereid=?'); PREPARESTMT_DFROM@SQL_DELETE;

–如果数据已经存在，则先delete在insert

    if@v_row>0then EXECUTESTMT_Dusing@vv_id; DEALLOCATEPREPARESTMT_D; endif; EXECUTESTMT_Iusing@vv_id; DEALLOCATEPREPARESTMT_I; endif; setii=i+1; ifmod(i,100)=0thencommit; endif; untilv_exit=1endrepeat; closec_ids; commit; end; // -------

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

【编辑推荐】

深入探讨MySQL锁机制

MySQL字段中的集合

MySQL字段类型简介

Mysql外键用法分析

详解MySQL数据表类型