拆表用的MySQL存储过程

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

成都创新互联长期为1000多家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为秦皇岛企业提供专业的网站设计制作、成都网站设计，秦皇岛网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。

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

 
 
 
      
  
  
  1. insert into table_new_0000 select * from table_old where mod(user_id,64)=0;  
    2.   
  
  
  2. insert into table_new_0001 select * from table_old where mod(user_id,64)=1;  
    3.   
  
  
  3. ...  
    4.

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

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

附脚本：

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

-----------
--- 全量脚本：

 
 
 
      
  
  
  1. CREATE PROCEDURE  sp_xf_move_item()  
    2.   
  
  
  2. begin  
    3.   
  
  
  3. declare v_exit int default 0;  
    4.   
  
  
  4. declare v_spid bigint;  
    5.   
  
  
  5. declare v_id bigint;  
    6.   
  
  
  6. declare i int default 0;  
    7.   
  
  
  7. declare c_table int;  
    8.

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

 
 
 
      
  
  
  1. declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified < '2010-8-25 00:00:00';  
    2.   
  
  
  2. declare  continue handler for not found set v_exit=1;  
    3.   
  
  
  3. open c_ids;  
    4.   
  
  
  4. repeat  
    5.

--将需要的值装入变量

 
 
 
      
  
  
  1. fetch c_ids into v_id,v_spid;  
    2.   
  
  
  2. if v_exit = 0 then  
    3.   
  
  
  3. set @vv_id = v_id;  
    4.

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

 
 
 
      
  
  
  1. select mod(v_spid,64) into c_table;  
    2.

--组装动态sql

 
 
 
      
  
  
  1. SET @SQL_CONTEXT =  
    2.   
  
  
  2. CONCAT('insert into item_record_',  
    3.   
  
  
  3. LPAD(c_table, 4, 0),  
    4.   
  
  
  4. ' select * from item_records_0000 where id = ?');  
    5.   
  
  
  5.  
    6.   
  
  
  6. PREPARE STMT FROM @SQL_CONTEXT;  
    7.   
  
  
  7. --执行sql  
    8.   
  
  
  8. EXECUTE STMT using @vv_id;  
    9.   
  
  
  9. DEALLOCATE PREPARE STMT;  
    10.   
  
  
  10. end if;  
    11.   
  
  
  11. set ii=i+1;  
    12.   
  
  
  12.  
    13.

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

 
 
 
      
  
  
  1. if mod(i,100)=0 then commit;  
    2.   
  
  
  2. end if;  
    3.   
  
  
  3. until v_exit=1 
    4.   
  
  
  4. end repeat;  
    5.   
  
  
  5. close c_ids;  
    6.   
  
  
  6. commit;  
    7.   
  
  
  7. end;  
    8.   
  
  
  8. //  
    9.   
  
  
  9.  
    10.   
  
  
  10. -----------  
    11.   
  
  
  11. set auto_commit=0;  
    12.   
  
  
  12. call  sp_xf_move_item();  
    13.   
  
  
  13.  
    14.   
  
  
  14. #### 增量脚本 ######  
    15.   
  
  
  15.  
    16.   
  
  
  16. CREATE PROCEDURE sp_xf_add_item()  
    17.   
  
  
  17. begin  
    18.   
  
  
  18. declare v_exit int default 0;  
    19.   
  
  
  19. declare v_spid bigint;  
    20.   
  
  
  20. declare v_id bigint;  
    21.   
  
  
  21. declare i int default 0;  
    22.   
  
  
  22. declare c_table int;  
    23.   
  
  
  23. declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified >= '2010-8-25 00:00:00';  
    24.   
  
  
  24. declare  continue handler for not found set v_exit=1;  
    25.   
  
  
  25. open c_ids;  
    26.   
  
  
  26. repeat  
    27.   
  
  
  27.  
    28.   
  
  
  28. fetch c_ids into v_id,v_spid;  
    29.   
  
  
  29. if v_exit = 0 then  
    30.   
  
  
  30. set @vv_id = v_id;  
    31.   
  
  
  31. set @v_row=0;  
    32.   
  
  
  32. select mod(v_spid,64) into c_table;  
    33.   
  
  
  33.  
    34.

--判断数据是否已经存在

 
 
 
      
  
  
  1. SET @SQL_C =  
    2.   
  
  
  2. CONCAT('select count(*) into @v_row from item_record_',  
    3.   
  
  
  3. LPAD(c_table, 4, 0),  
    4.   
  
  
  4. ' where id = ?');  
    5.   
  
  
  5.  
    6.   
  
  
  6. PREPARE STMT_C FROM @SQL_C;  
    7.   
  
  
  7. EXECUTE STMT_C using @vv_id;  
    8.   
  
  
  8. DEALLOCATE PREPARE STMT_C;                         
    9.   
  
  
  9.  
    10.   
  
  
  10. SET @SQL_INSERT =  
    11.   
  
  
  11. CONCAT('insert into bbc_item_record_',  
    12.   
  
  
  12. LPAD(c_table, 4, 0),  
    13.   
  
  
  13. ' select * from item_records_0000 where id = ?');  
    14.   
  
  
  14.  
    15.   
  
  
  15. PREPARE STMT_I FROM @SQL_INSERT;           
    16.   
  
  
  16.  
    17.   
  
  
  17. SET @SQL_DELETE =  
    18.   
  
  
  18. CONCAT('DELETE FROM bbc_item_record_',  
    19.   
  
  
  19. LPAD(c_table, 4, 0),  
    20.   
  
  
  20. ' where id = ?');  
    21.   
  
  
  21. PREPARE STMT_D FROM @SQL_DELETE;       
    22.

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

 
 
 
      
  
  
  1. if @v_row>0 then   
    2.   
  
  
  2.  
    3.   
  
  
  3. EXECUTE STMT_D using @vv_id;  
    4.   
  
  
  4. DEALLOCATE PREPARE STMT_D;  
    5.   
  
  
  5.  
    6.   
  
  
  6. end if;  
    7.   
  
  
  7. EXECUTE STMT_I using @vv_id;  
    8.   
  
  
  8. DEALLOCATE PREPARE STMT_I;         
    9.   
  
  
  9.  
    10.   
  
  
  10. end if;  
    11.   
  
  
  11. set ii=i+1;  
    12.   
  
  
  12. if mod(i,100)=0 then commit;  
    13.   
  
  
  13. end if;  
    14.   
  
  
  14. until v_exit=1 
    15.   
  
  
  15. end repeat;  
    16.   
  
  
  16. close c_ids;  
    17.   
  
  
  17. commit;  
    18.   
  
  
  18. end;  
    19.   
  
  
  19. //  
    20.   
  
  
  20.  
    21.   
  
  
  21. -------  
    22.   
  
  
  22.  
    23.

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

【编辑推荐】

深入探讨MySQL锁机制

MySQL字段中的集合

MySQL字段类型简介

Mysql外键用法分析

详解MySQL数据表类型

  

文章名称：拆表用的MySQL存储过程
文章出自：http://www.csdahua.cn/qtweb/news47/168847.html

网站建设、网络推广公司-快上网，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 快上网

成都快上网为您推荐相关内容

建站公司知识

同城分类信息

官网SEO排名　　 　德阳服务器托管　　 　乐尚佳花箱　　 　软文推广　　 　app开发公司　　 　电信机房托管　　 　绵阳主机托管　　 　成都网络营销　　 　成都婚庆策划　　 　商城网站　　 　制作网站　　 　东电技服　　 　香港主机　　 　成都印刷厂　　 　B2C电商系统　　 　ssl证书申请　　 　网站空间　　 　成都活动策划　　 　成都画册快印　　 　梓潼网站制作公司