拆表用的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.36103.cn/qtweb/news2/25252.html

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

广告

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

猜你还喜欢下面的内容

静态网站知识

分类信息网站

高防机房　　 　成都400电话申请　　 　成都logo设计标志　　 　成都网络营销　　 　四川雅安服务器托管　　 　成都全屋定制　　 　西信中心　　 　名片印刷厂　　 　成都LED显示屏　　 　眉山网站建设　　 　微信小程序开发　　 　内江广告　　 　成都网站设计　　 　成都响应式网站建设公司　　 　成都西信机房托管　　 　成都防护栏　　 　中国电信成都枢纽中心　　 　温江网站建设　　 　成都棕数机房　　 　免备案虚拟主机