专栏首页从零开始学自动化测试面试题:如何造10w条测试数据,在数据库插入10w条不同数据

面试题:如何造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');
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

加到 18600099999,这样号码段1860000000-18600099999就是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.txt", "a") as fp:
fp.write(a+"\n")

只需在上面基础上把 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条数据的时候,会花多少时间?

# 作者:上海-悠悠
# python3insert_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
  • 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 |
+--------------------+-----------+
1 row in set (0.00 sec)mysql>

从上面的查询结果可以看到,已经生效了。

再次重新执行上面10w条数据,查看运行结果总共花11秒左右时间。

受影响的行: 100000

时间: 11.678s

上面的方法只能临时生效,当重启mysql后,你会发现又还原回去了。

这里还有一种永久生效的方法,需修改my.cnf配置文件

在[mysqld]部分添加一句,如果有就修改对应的值:

  • max_allowed_packet=40M

这里的值,可以用 M单位,修改后,需要重启下mysql就可以生效了

使用python执行

如果不用 navicat 客户端,直接用python去执行,会花多少时间呢?

先封装连接mysql的方法,然后拼接执行的sql语句,拼接的时候需注意,最后的字符 ,需改成 ;

在执行代码前先获取当前的时间戳,代码执行完成后再次获取一次时间戳。两次的时间间隔,就是执行的时间了,时间单位是s

python 执行 mysql 代码参考如下

import pymysql
'''
# python3
作者:上海-悠悠
pip install PyMySQL==0.9.3
'''dbinfo = {
"host": "192.168.1.x",
"user": "root",
"password": "123456",
"port": 3306}class DbConnect():
def __init__(self, db_cof, database=""):
self.db_cof = db_cof
# 打开数据库连接
self.db = pymysql.connect(database=database,
cursorclass=pymysql.cursors.DictCursor,
**db_cof)# 使用cursor()方法获取操作游标
self.cursor = self.db.cursor()def select(self, sql):
# SQL 查询语句
# sql = "SELECT * FROM EMPLOYEE \
#        WHERE INCOME > %s" % (1000)
self.cursor.execute(sql)
results = self.cursor.fetchall()
return resultsdef execute(self, sql):
# SQL 删除、提交、修改语句
# sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交修改
self.db.commit()
except:
# 发生错误时回滚
self.db.rollback()def close(self):
# 关闭连接
self.db.close()if __name__ == '__main__':
import time
insert_sql = "INSERT INTO `apps`.`apiapp_card`  VALUES "
insert_values = "".join(["('%s', '', 'test123', '2019-12-17'), \n"%str(i+100000) for i in range(100000)])
# 拼接sql
sql = insert_sql + insert_values[:-3]+";"
# print(sql)
# 执行sql
time1 = time.time()
db = DbConnect(dbinfo, database="apps")
db.execute(sql)
db.close()
time2 = time.time()
print("总过耗时:%s" % (time2-time1))

使用python执行结果:总过耗时:1.0816256999969482,结果超出我的想象,10w条数据居然只要1秒钟!

本文分享自微信公众号 - 从零开始学自动化测试(yoyoketang),作者:上海悠悠

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Appium+python自动化21-DesiredCapabilities详解

    前言 Appium Desired Capabilities里面配置的参数还是蛮多的,android和ios的又有很多不一样地方,另外还能起到浏览器,做wap的...

    上海-悠悠
  • python测试开发django-57.xadmin选项二级联动

    当我们选择项目分类的时候,一个项目下关联多个模块,同时有这两个选项框的时候,需要实现选中一个项目,模块里面自动删除出该项目下的模块,如下图这种

    上海-悠悠
  • Selenium2+python自动化8-SeleniumBuilder辅助定位元素

    前言 福利来了,对于用火狐浏览器的小伙伴们,你还在为定位元素而烦恼嘛? 上古神器Selenium Builder来啦,哪里不会点哪里,妈妈再也不用担心我的定位元...

    上海-悠悠
  • 如何造10w条测试数据,在数据库插入10w条不同数据

    最近面试经常会问到sql相关的问题,在数据库中造测试数据是平常工作中经常会用到的场景,一般做压力测试,性能测试也需在数据库中先准备测试数据。那么如何批量生成大量...

    上海-悠悠yoyoketang
  • SQL练习题共50道附答案(MySQL)

    最近真的超忙超忙,大家可以根据自己的查询需求更改数据,如果你sql很6那么当然最好啦,如果不是特别厉害建议理解这50道Sql题目,完事你会发现自己进步蛮大!!!...

    框架师
  • SQL练习题共50道附答案(MySQL)

    大家可以根据自己的查询需求更改数据,如果你sql很6那么当然最好啦,如果不是特别厉害建议理解这50道Sql题目,完事你会发现自己进步蛮大!!!加油

    JAVA葵花宝典
  • PHP-TP(Request请求类)

    http://www.tpshop.com/home/test/index/id/100?page=10

    cwl_java
  • 【React】【案例】:TimeLine 时间轴

    WEBJ2EE
  • 从并发处理谈PHP进程间通信(一)外部介质

    进程间通信 进程间通信(IPC,Inter-Process Communication),多进程开发中,进程间通信是一个永远也绕不开的问题。在 web开发中,我...

    枕边书
  • SpringBoot 初入门

    移动到项目的 target 目录下,可以看到一个后缀名为 jar 的文件,便是刚刚我们打包的 jar 包,

    希希里之海

扫码关注云+社区

领取腾讯云代金券