数据库

mysql 循环批量插入的实例代码详解
数据库 2024/12/27 佚名
3 2

背景

前几天在MySql上做分页时，看到有博文说使用 limit 0,10 方式分页会有丢数据问题，有人又说不会，于是想自己测试一下。测试时没有数据，便安装了一个MySql，建了张表，在建了个while循环批量插入10W条测试数据的时候，执行时间之长无法忍受，便查资料找批量插入优化方法，这里做个笔记。

数据结构

寻思着分页时标准列分主键列、索引列、普通列3种场景，所以，测试表需要包含这3种场景，建表语法如下：

drop table if exists `test`.`t_model`;

Create table `test`.`t_model`( 
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `uid` bigint COMMENT '业务主键',
 `modelid` varchar(50) COMMENT '字符主键',
 `modelname` varchar(50) COMMENT '名称',
 `desc` varchar(50) COMMENT '描述',
 primary key (`id`),
 UNIQUE index `uid_unique` (`uid`),
 key `modelid_index` (`modelid`) USING BTREE
) ENGINE=InnoDB charset=utf8 collate=utf8_bin;

为了方便操作，插入操作使用存储过程通过while循环插入有序数据，未验证其他操作方式或循环方式的性能。

执行过程

1、使用最简单的方式直接循环单条插入1W条，语法如下：

drop procedure if exists my_procedure; 
delimiter //
create procedure my_procedure()
begin
 DECLARE n int DEFAULT 1;
 WHILE n < 10001 DO
 insert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc'); 
 set n = n + 1;
 END WHILE;
end
//　　　　　　　　　　　　　　　
delimiter ;

插入1W条数据，执行时间大概在6m7s，按照这个速度，要插入1000W级数据，估计要跑几天。

2、于是，构思加个事务提交，是否能加快点性能呢？测试每1000条就commit一下，语法如下：

delimiter //
create procedure u_head_and_low_pro()
begin
 DECLARE n int DEFAULT 17541;
 WHILE n < 10001 DO
   insert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc'); 
   set n = n + 1;
   if n % 1000 = 0 
   then
    commit;
   end if;
 END WHILE;
end
//
delimiter ;

执行时间 6 min 16 sec，与不加commit执行差别不大，看来，这种方式做批量插入，性能是很低的。

3、使用存储过程生成批量插入语句执行批量插入插入1W条，语法如下：

drop procedure IF EXISTS u_head_and_low_pro;
delimiter $$
create procedure u_head_and_low_pro()
begin
 DECLARE n int DEFAULT 1;
 set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';
 set @exedata = '';
 WHILE n < 10001 DO
 set @exedata = concat(@exedata,"(",n,",","'id20170831",n,"','","name",n,"','","desc'",")");
 if n % 1000 = 0 
 then
  set @exesql = concat(@exesql,@exedata,";");
  prepare stmt from @exesql;
  execute stmt;
  DEALLOCATE prepare stmt;
  commit; 
  set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';
  set @exedata = "";
 else
  set @exedata = concat(@exedata,',');
 end if;
 set n = n + 1;
 END WHILE;
end;$$ 
delimiter ;

执行时间 3.308s。

总结

批量插入时，使用insert的values批量方式插入，执行速度大大提升。

以上所述是小编给大家介绍的mysql 循环批量插入的实例代码详解,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

标签：
mysql,循环,mysql,批量插入
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
无争山庄资源网 Copyright www.whwtcm.com

评论“mysql 循环批量插入的实例代码详解”

暂无“mysql 循环批量插入的实例代码详解”评论...
www.whwtcm.com 无争山庄资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
无争山庄资源网 Copyright www.whwtcm.com