目录 　　1、背景 　　2、两种方式对比 　　　　2.1、一次插入一条数据 　　　　2.2、一次插入多条数据 　　3、拓展一下 　　4、Other 　 1、背景 　　我们在工作中基本都会碰到批量插入数据到DB的情况，这个时候我们就需要根据不同的情况选择不同的策略。 　　只要了解sql，就应该知道，向table中插入数据的命令，至少有insert和replace这两种，使用哪一种命令，和自己的业务有关； 　　本文就针对insert进行批量插入进行阐述，然后根据自身经历分享几个注意事项。 　 2、两种方式的对比 　　即使是insert命令，他也是有多种插入数据的方式的。但这里就不深入了解底层insert是怎么做的了，那个已经超出本人的知识范畴，哈哈。 　　但是我们可以大致了解MySQL的执行命令时的初略步骤： 　　1、首先建立连接（Socke连接）； 　　2、Client将要执行的sql命令通过TCP连接，发给Server； 　　Client，可以理解为我们用各种语言写的项目程序（客户端）； 　　Server，就是数据库Server，负责执行。 3、数据库Server收到数据（sql）后，会解析sql，然后进行处理； 4、将处理结果返回客户端。 有了上面的流程，我们就开始说insert的两种插入方式区别，下面是测试使用的表： 1 2 3 4 5 6 7 8 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(40) NOT NULL COMMENT '姓名', `gender` tinyint(1) DEFAULT '0' COMMENT '性别:1-男;2-女', `addr` varchar(40) NOT NULL COMMENT '住址', `status` tinyint(1) DEFAULT '1' COMMENT '是否有效:1-有效;2-无效', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 2.1、一次插入一条数据 　　最初学习数据库，都知道使用insert可以实现数据插入，比如向user表中插入一条数据： 1 2 3 4 5 6 7 8 9 10 mysql> insert into user (id, name, gender, addr, status) values (1, 'aaa', 1, 'beijing', 1); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+------+--------+---------+--------+ | id | name | gender | addr | status | +----+------+--------+---------+--------+ | 1 | aaa | 1 | beijing | 1 | +----+------+--------+---------+--------+ 1 row in set (0.00 sec) 　　这是最简单的方式了，当然这是在命令行里面，当然命令行也是一种客户端； 　　如果是客户端我们代码的程序，比如Java利用jdbc来执行sql，也是传给MySQL Server上面执行的insert命令； 　　上面的insert命令的确是能插入数据数据的，也就是每执行一条insert命令，就需要通过网络将命令发送给MySQL Server解析运行，如果有上千万行数据需要插入，那么是不是需要进行上千万次连接传输呢？虽然现在可以使用连接池，但是传输的次数是是躲不掉的。 　　使用insert一次插入一条数据的这种方式，绝大多数都是使用这种方式，来进行少量的数据插入！！！ 　　如果用这种方式进行大量数据的入库，哈哈，花的时间可以喝好多杯咖啡了。 2.2、一次插入多条数据 　　上面已经说到了，一次插入一条数据的主要缺陷是：需要建立N次连接，然后传输N连接，因为连接池的存在，可以忽略连接耗时，但是传输N次的耗时，不可小觑，所以我们可以从这方面进行考虑优化。 　　比如，一个工人负责将100块砖从A点搬到B点，每次搬1块砖，花费1个单位时间，那么搬完100块砖，需要100单位时间（不考虑来回）； 　　如果一次搬5块砖，那么只需要20单位时间，是不是快了很多呢？ 　　同理，我们使用insert也可以进行批量插入数据： 1 2 3 4 5 6 insert into user (id, name, gender, addr, status) values (2, 'bbb', 0, 'shanghai', 1), (3, 'ccc', 1, 'hangzhou', 0), (4, 'ddd', 0, 'chongqing', 0); 　　这样就可以一次性插入3条数据了。 　　对于客户端来说，只需要进行拼接sql语句即可，然后将拼接后的sql一次性发给MySQL Server就可以了。 　　注意，SQL要使用拼接，而不是说预处理！！！ 　　预处理的作用是避免频繁编译sql、sql注入；使用预处理来进行批量插入时，使用循环每次设置占位符值，这个和一次插入一条命令是等价的，如下面的示例，其实执行了3次1条记录插入： 1 2 3 4 5 6 7 8 9 prepare($sql); $stmt->execute(array(5, "eee", 1, "PEK", 1)); $stmt->execute(array(6, "fff", 0, "SHA", 0)); $stmt->execute(array(7, "ggg", 1, "LNL", 1)); ?> 　　 　　正确的方式： 1 2 3 4 5 6 7 8 9 10 11 exec($sql); ?> 　　 　　如果是Java可以使用原生JDBC，进行上面一样拼接，就不写代码了； 　　如果Java使用Mybatis的话，可以使用标签， 1 2 3 4 5 6 7 8 9 10 11 12 insert ignore into user (id, name, gender, addr, status) values ( #{item.id,jdbcType=INT}, #{item.name,jdbcType=VARCHAR}, #{item.gender,jdbcType=BIT}, #{item.addr,jdbcType=VARCHAR}, #{item.status,jdbcType=BIT} ) 　　 3、拓展一下 　　批量insert，每次insert的量是多少合适呢？ 　　以上面工人搬砖的例子，一次搬5块砖，需要20单位时间，那岂不是1次搬100块砖，只需要1单位时间了？是这个逻辑，但是这样是不行的，需要看实际情况！！！ 　　这个实际情况是什么呢？不好说，比如一个比较强壮的工人，一次100块砖，不是难事；如果工人没那么强转，一次100块砖，可能直接把工人给干倒了，1块砖也搬不了，这时可不止100单位时间。 　　另外，放砖的B点，是不是能一次接收100块砖，这也是一个问题。 　　上面的例子，类比到insert批量插入，就需要注意： 　　1、要根据情况设置一次批量插入的数据量，数据量大，在网络中传输的事件也越久，出现问题的可能也越大； 　　2、除了网络，还要看机器配置，MySQL Server配置差了，sql写得再好，效率也不会太高； 　　3、另外批量这个词，是指一次插入多条数据，我们除了要注意数据的条数，还要注意一条数据的大小，举个例子：比如一条记录的数据量有1M，10条记录的数据量就10M，这时一次插100条，100M数据，嘿嘿，你试试看！！所以，一次插入多少数据，一定要经过多次测试后再决定，别人1次插100条最优，你可能1次插10条才最优，没有绝对的最优值（批量插入未必总是比单条插入效率高）。 　　4、数据库有个参数，max_allowed_packet，也就是每一个包（sql）命令大小，默认是1M，那么sql的长度大于1M就会报错。你可能会说，咱们把这个参数设成10M，100M不就行了？？？对呀，没毛病，但你是DBA吗？你有权限吗？即使调大这一个参数，你要知道影响的可不止你这一张表，而是整个DB Server，那影响的可是很多库，很多表。 　　5、批量插入并不是越快越好，我们可能希望越快越好，这很正常，节省时间嘛。但是我们一定要知道，数据库分读写，有集群，这就意味着，需要同步！！！如果有分库分表分区的情况，如果短时间内插入的数据量太大，数据库同步可能就会比较迷了，读写数据不一致的情况在所难免了，可能会因为一张表的批量插入，影响整个DB服务组的同步，同时还要考虑并发问题，哈哈哈。　 　 4、Other 　　可以注意一下，我在上面写的insert语句中，基本每一条命令都写了插入的字段，如下： 1 insert into user (id, name, gender, addr, status) values (1, 'aaa', 1, 'beijing', 1); 　　 　　其实我知道表的各个字段的排列顺序，完全可以省略字段名，如下： 1 insert into user values (1, 'aaa', 1, 'beijing', 1); 　　 　　这两种方式的效率，这里就不谈了，不过第一种方式，在某些场景有优势，举个例子：比如user表中增加create_time、update_time： 1 2 3 4 5 6 7 8 9 10 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(40) NOT NULL COMMENT '姓名', `gender` tinyint(1) DEFAULT '0' COMMENT '性别:1-男;2-女', `addr` varchar(40) NOT NULL COMMENT '住址', `status` tinyint(1) DEFAULT '1' COMMENT '是否有效', `create_time` timestamp DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 　　 　　如果没有强制要求create_time和update_time必须从客户端接收，那么完全可以用默认值，insert的时候不用下面的语句： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --- 强制create_time和update_time使用client传递值 insert into user (id, name, gender, addr, status, create_time, update_time) values (2, 'bbb', 0, 'shanghai', 1, '2019-11-09 18:00:00', '2019-11-09 18:00:00'), (3, 'ccc', 1, 'hangzhou', 0, '2019-11-09 18:00:00', '2019-11-09 18:00:00'), (4, 'ddd', 0, 'chongqing', 0, '2019-11-09 18:00:00', '2019-11-09 18:00:00'); --- create_time和update_time不需要强制使用client传递值，可以使用默认值 insert into user (id, name, gender, addr, status) values (2, 'bbb', 0, 'shanghai', 1), (3, 'ccc', 1, 'hangzhou', 0), (4, 'ddd', 0, 'chongqing', 0); 　　 　　类似的，对于有些字段有默认值，并且批量插入的时候，都使用默认值时，可以省略该字段，因为拼接sql的时候可以少拼接一点，网络传输的数据就少一点，能提升一点是一点吧，这个还得看实际情况。 如需转载，请注明文章出处，谢谢！！！ 分类: MySQL 好文要顶 关注我 收藏该文 寻觅beyond 关注 - 1 粉丝 - 30 +加关注 1 0 « 上一篇： Git采坑与问题排查 posted @ 2019-11-09 16:07 寻觅beyond 阅读(178) 评论(0) 编辑 收藏 刷新评论刷新页面返回顶部 注册用户登录后才能发表评论，请 登录 或 注册， 访问 网站首页。 【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库 【活动】京东云服务器_云主机低于1折，低价高性能产品备战双11 【培训】马士兵老师强势回归！Java线下课程全免费，双十一大促！ 【推荐】天翼云双十一翼降到底，云主机11.11元起，抽奖送大礼 【推荐】流程自动化专家UiBot，体系化教程成就高薪RPA工程师 【福利】个推四大热门移动开发SDK全部免费用一年，限时抢！ 相关博文： · mysql批量插入数据优化 · MySql快速插入以及批量更新 · mysql批量插入数据 · MySql快速插入以及批量更新 · MYSQL批量插入数据库实现语句性能分析 » 更多推荐... 最新 IT 新闻: · 官宣！荣耀V30将于11月26日发布：双模5G全国通、Matrix镜头加持 · 谷歌“夜莺计划”曝光：采集数百万医疗隐私数据，医生患者毫不知情 · 滴滴回应司机持刀伤人：乘客要求超速行驶引发冲突，对乘客深表歉意 · 华为发放20亿元奖金，抗击美国断供，奖励员工加班奋斗 · 新方法利用叠加态原子测量引力 有助了解暗物质“性格” » 更多新闻...https://www.cnblogs.com/-beyond/p/11789885.html