执⾏批量插⼊_⾯试题：如何造10w条测试数据，在数据库插
⼊10w条不同数据
前⾔
⾯试题：如果造10w条测试数据，如何在数据库插⼊10w条数据，数据不重复
最近⾯试经常会问到sql相关的问题，在数据库中造测试数据是平常⼯作中经常会⽤到的场景，⼀般做压⼒测试，性能测试也需在数据库中先准备测试数据。那么如何批量⽣成⼤量的测试数据呢？
由于平常⽤python较多，所以想到⽤python先⽣成sql，再执⾏sql往数据库插⼊数据。
使⽤语⾔：python 3.6
插⼊数据
⾸先我要插⼊的 SQL 语句，需每条 id 不重复 ，下⾯是执⾏单个插⼊语句
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('1', '', 'test123', '2019-12-17');
10w 太多执⾏时间长，⽤ python 先⽣成 1w条测下执⾏时间。
⾸先要⽣成多个insert 语句，这⾥我⽤ python 语⾔写段⽣成sql的脚本。
⽤ %s 替换需要变的字段值，如果有多个值都需要变，可以⽤多个%s替换对应值，我这⾥设计的表，只要id不⼀样就可以插⼊成功。代码生成图片
⽤for 循环，每次循环 id 加1，这样 id 就可以保证不会重复，否则插⼊数据库时有重复的⽆法写⼊成功。
a 是追加写⼊
每条sql后⾯分号隔开
每次写⼊数据，最后⾯加\n 换⾏
# python3
# 作者：上海-悠悠
for i in range(10000):
a = "INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('%s', '', 'test123', '2019-12-17');"%str(i+1)
with open("a.txt", "a") as fp:
fp.write(a+"\n")
执⾏python代码，在本地⽣成⼀个 a.text ⽂件，打开⽣成的数据，部分如下
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('1', '', 'test123', '2019-12-17');
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('2', '', 'test123', '2019-12-17');
mysql面试题常问INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('3', '', 'test123', '2019-12-17');
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('4', '', 'test123', '2019-12-17');
......
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('10000', '', 'test123', '2019-12-17');
如果id是⼿机号呢，如何⽣成10w个不同⼿机号？
可以按⼿机号前3位开头的号码段⽣成，⽐如186开头的，先⽤初始数据 1860000000，再这个数字基础上每次加1
加到 186********，这样号码段1860000000-186********就是10w个⼿机号了。
把id换成⼿机号后，修改代码如下
# python3
# 作者：上海-悠悠for i in range(10000):
a = "INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('%s', '', 'test123', '2019-12-17');"%str(i+1860000000)    with open("a        fp.write(a+"\n")prototype是什么牌子
只需在上⾯基础上把 str(i+1) 改成 str(i+1860000000) 就可以⽣成⼿机号了
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('1860000000', '', 'test123', '2019-12-17');
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('1860000001', '', 'test123', '2019-12-17');
INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('1860000002', '', 'test123', '2019-12-17');
把⽣成的⽂本复制出来 ，多个INSERT INTO 对应的 sql ⼀次性贴到 navicat 客户端执⾏
执⾏完成花了5分钟左右，也就是说10w条得50分钟，这太慢了，要是数据更多，会等太久，不是我们想要的效果！
批量执⾏
由于单个执⾏，花费时间太长，现在需要优化下改成⼀个 inert 语句，改成批量插⼊数据，只写⼀个 insert into 这样⼀次性批量写到数据
库，会快很多。
可以将SQL语句进⾏拼接，使⽤ insert into table () values (),(),(),()然后再⼀次性插⼊。
批量执⾏要么全部成功，要么⼀个都不会写⼊成功，当写的 SQL 语法有问题时就不会写⼊成功了。
需注意：
拼接 sql ，多个values 值中间⽤英⽂逗号隔开
value 值要与数据表的字段⼀⼀对应
⼀定要注意最后⼀条数据后⾯不是逗号，改成分号
# python3
# 作者：上海-悠悠
insert_sql = "INSERT INTO `apps`.`apiapp_card`  VALUES "
with open("b.txt", "a") as fp:
fp.write(insert_sql+"\n")
for i in range(10000):
a = "('%s', '', 'test123', '2019-12-17'),"%str(i+10001)
with open("b.txt", "a") as fp:
fp.write(a+"\n")
执⾏完成后，复制 b.text ⽂件的内容，需注意的是这⾥⼀定要改成 ；结尾，否则语法报错
部分数据内容展⽰如下
INSERT INTO `apps`.`apiapp_card` VALUES
('10001', '', 'test123', '2019-12-17'),
('10002', '', 'test123', '2019-12-17'),
......
('20000', '', 'test123', '2019-12-17');
复制⽣成的 INSERT INTO 到 navicat 客户端执⾏
执⾏完成，最后看的测试结果，1w条数据只⽤了0.217秒，速度明显提⾼不少。10w数据插⼊
接着测下，当⽣成10 w条数据的时候，会花多少时间？
# 作者：上海-悠悠
# python3
insert_sql = "INSERT INTO `apps`.`apiapp_card`  VALUES "
with open("b.txt", "a") as fp:
fp.write(insert_sql+"\n")
for i in range(100000):
a = "('%s', '', 'test123', '2019-12-17'),"%str(i+100000)
with open("b.txt", "a") as fp:
fp.write(a+"\n")
使⽤python脚本执⾏后⽣成的数据如下
INSERT INTO `apps`.`apiapp_card`  VALUES
('100000', '', 'test123', '2019-12-17'),
('100001', '', 'test123', '2019-12-17'),
......
('199999', '', 'test123', '2019-12-17');
直接插⼊mysql 这时候会有报错：Err 1153 - Got a packet bigger than ‘max_allowed_packet’ bytes
报错原因：由于数据量较⼤，mysql 会对单表数据量较⼤的 SQL 做限制，10w条数据的字符串超出了max_allowed_packet 的允许范围。
解决办法：需修改mysql 数据库的max_allowed_packet的值，改⼤⼀点
max_allowed_packet
先在 navicat 输⼊命令查看 max_allowed_packet 最⼤允许包
show global variables like ‘max_allowed_packet’;
抖音小程序源码下载
查看到 value 值是 4194304， 最⼤限制是 40 M，我们只需的sql字符串太⼤了，超出了这个范围。
在 navicat 客户端我们⽆法直接修改对应 value值，需登录到mysql，⽤命令⾏修改。
我这⾥ mysql 是搭建在 docker 上，需先进容器，登录到mysql.
操作步骤如下：
docker exec 进docker容器
mysql -uroot -p  输⼊密码后登录mysql
set global max_allowed_packet=419430400; 设置最⼤允许包 400M
vb入口过程show global variables like ‘max_allowed_packet’; 查看前⾯设置是否⽣效
[root@VM_0_2_centos ~]# docker exec -it 934b30a6dc36 /bin/bash
root@934b30a6dc36:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 303822
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name      | Value    |
+--------------------+-----------+
| max_allowed_packet | 4194304 |
+--------------------+-----------+
1 row in set (0.00 sec)
mysql> set global max_allowed_packet=419430400;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name      | Value    |
+--------------------+-----------+
| max_allowed_packet | 419430400 |
30岁了还能学c语言吗+--------------------+-----------+
1 row in set (0.00 sec)
mysql>
从上⾯的查询结果可以看到，已经⽣效了。
再次重新执⾏上⾯10w条数据，查看运⾏结果总共花11秒左右时间。受影响的⾏: 100000
时间: 11.678s
上⾯的⽅法只能临时⽣效，当重启mysql后，你会发现⼜还原回去了。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。