面试问题:如何创建10w测试数据并将10w不同的数据插入数据库？

序

面试问题:如果创建10w测试数据，如何将10w数据无重复地插入数据库？

最近面试经常问sql相关的问题。在数据库中创建测试数据是日常工作中常见的场景。一般压力测试和性能测试也需要先在数据库中准备好测试数据。那么如何批量生成大量的测试数据呢？

因为经常使用python，所以想到了用python生成sql，然后执行sql将数据插入数据库。

语言:python 3.6

插入数据

首先我要插入SQL语句，每个id不要重复。下面是一条insert语句的执行过程。

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 1 '，''，' test123 '，' 2019-12-17 ')；

10w执行时间太长。用python先生用1w测执行时间。

首先，我需要生成多个惰性语句。这里我用python语言写段落生成sql文本。

将需要更改的字段值替换为% s，如果需要更改多个值，可以将对应的值替换为多个% s，我这里设计的表，只要id不同就可以成功插入。

使用for循环，在每个循环的id上加1，这样可以保证id不重复，否则插入数据库时重复的ID无法写入成功。

a是附加文字。

用分号分隔每个sql。

每次写数据的时候，在末尾加一个换行符。

python3

作者:上海优优QQ群717225969

对于范围内的I(10000):

a = " INSERT INTO apps . API app _ card(id，card_id，card_user，add_time)值(' %s '，''，' test123 '，' 2019-12-17 ')；"%str(i+1)

用open("a.txt "，" a ")作为fp:

fp.write(a+"\n ")

执行python代码，在本地生成a.text文件，打开生成的数据。该部分如下。

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 1 '，''，' test123 '，' 2019-12-17 ')；

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 2 '，''，' test123 '，' 2019-12-17 ')；

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 3 '，''，' test123 '，' 2019-12-17 ')；

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 4 '，''，' test123 '，' 2019-12-17 ')；

......

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 10000 '，''，' test123 '，' 2019-12-17 ')；

如果id是手机号，如何生成10w个不同的手机号？

可以根据手机号码前三位开头的号码字段生成，比如186开头的号码。首先初始数据是1860000000，然后数字每次加1。

加到1860009999，那么数字字段186000000-1860009999就是10w。

将id更改为手机号码后，按如下方式修改代码

python3

作者:上海优优QQ群717225969

对于范围内的I(10000):

a = " INSERT INTO apps . API app _ card(id，card_id，card_user，add_time)值(' %s '，''，' test123 '，' 2019-12-17 ')；"%str(i+1860000000)

用open("a.txt "，" a ")作为fp:

fp.write(a+"\n ")

只要在上面的基础上把str(i+1)改成STR (I+186000000)，就可以生成手机号了。

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 1860000000 '，''，' test123 '，' 2019-12-17 ')；

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 1860000001 '，''，' test123 '，' 2019-12-17 ')；

插入到apps.apiapp_card (id，card_id，card_user，add_time)值(' 1860000002 '，''，' test123 '，' 2019-12-17 ')；

复制生成的文本，将INSERT INTO对应的多个sql一次粘贴到navicat客户端执行。

执行完大概用了5分钟，也就是说10w要50分钟，太慢了。如果数据再多，时间就太长了，这不是我们想要的！

批量执行

因为单次执行时间太长，现在需要优化，改成惰性语句，批量插入数据，一次只写一个insert into来批量写入数据库，会快很多。

您可以拼接SQL语句，使用insert into table () values()、()、()、()、()然后一次插入它们。

要么所有批处理执行都成功，要么都不会成功写入。当写出来的SQL语法有问题，就写不成功。

注意:

拼接sql，用英文逗号分隔多个值。

该值应该与数据表中的字段一一对应。

一定要注意，最后一个数据后面不是逗号，而是分号。

python3

作者:上海优优QQ群717225969

insert_sql = "插入到apps.apiapp_card值"

用open("b.txt "，" a ")作为fp:

fp.write(insert_sql+"\n ")

对于范围内的I(10000):

a = "('%s '，''，' test123 '，' 2019-12-17 ')，" %str(i+10001)

用open("b.txt "，" a ")作为fp:

fp.write(a+"\n ")

执行完成后，复制b.text文件的内容。需要注意的是，这必须改为；结尾，否则语法是错误的。

一些数据内容如下所示。

插入到apps.apiapp_card值中

(' 10001 '，''，' test123 '，' 2019-12-17 ')，

(' 10002 '，''，' test123 '，' 2019-12-17 ')，

......

(' 20000 '，''，' test123 '，' 2019-12-17 ')；

将生成的插入内容复制到navicat客户端以供执行。

执行后最终测试结果显示，1w条数据仅用时0.217秒，速度明显提升。

10w数据插入

那么，生成10 w条数据需要多长时间？

作者:上海优优QQ群717225969

python3

insert_sql = "插入到apps.apiapp_card值"

用open("b.txt "，" a ")作为fp:

fp.write(insert_sql+"\n ")

对于范围内的I(100000):

a = "('%s '，''，' test123 '，' 2019-12-17 ')，" %str(i+100000)

用open("b.txt "，" a ")作为fp:

fp.write(a+"\n ")

使用python脚本执行后生成的数据如下

插入到apps.apiapp_card值中

(' 100000 '，''，' test123 '，' 2019-12-17 ')，

(' 100001 '，''，' test123 '，' 2019-12-17 ')，

......

(' 199999 '，''，' test123 '，' 2019-12-17 ')；

如果直接插入mysql，会得到一个错误:err 1153-得到一个大于' max _ allowed _ packet '字节的数据包。

错误原因:mysql由于数据量大，会限制单表数据量大的sql，10w数据的字符串超过max_allowed_packet。

的允许范围。

解决方法:mysql数据库中max_allowed_packet的值需要修改的大一些。

最大允许数据包

首先，在navicat中输入命令，查看max_allowed_packet最大允许数据包。

显示全局变量，如“max _ allowed _ packet”；

发现值是4194304，最大限制是40 m，我们只需要的sql字符串太大，超出了这个范围。

在navicat客户端，我们不能直接修改对应的值，需要登录mysql，用命令行修改。

我的mysql是建立在docker上的，所以我需要一个高级的容器，登录mysql。

操作步骤如下:

Docker exec进入Docker容器。

Mysql-urot-p输入密码，登录Mysql。

set global max _ allowed _ packet = 419430400；设置最大允许数据包400M m

显示全局变量，如“max _ allowed _ packet”；检查之前的设置是否生效。

[root @ VM _ 0 _ 2 _ centos ~]# docker exec-it 934 b 30 a6 DC 36/bin/bash

root @ 934 b 30 a6 DC 36:/# MySQL-u root-p

输入密码:

欢迎使用MySQL monitor。命令以结尾；或者\g。

您的MySQL连接id是303822

服务器版本:5.7.27 MySQL社区服务器(GPL)

Oracle是Oracle Corporation和/或其

附属公司。其他名称可能是其各自的商标

业主。

键入“help”或“\h”寻求帮助。键入' \c '清除当前的输入语句。

mysql & gt显示全局变量，如“max _ allowed _ packet”；

+ - + - +

|变量名称|值|

+ - + - +

| max _ allowed _ packet | 4194304 |

+ - + - +

65438+集合中的0行(0.00秒)

mysql & gtset global max _ allowed _ packet = 419430400；

查询正常，0行受影响(0.00秒)

mysql & gt显示全局变量，如“max _ allowed _ packet”；

+ - + - +

|变量名称|值|

+ - + - +

| max _ allowed _ packet | 419430400 |

+ - + - +

65438+集合中的0行(0.00秒)

mysql & gt

从上面的查询结果可以看出，已经生效。

再次重新执行上述10w数据，检查运行结果大约需要11秒。

受影响的线路:100000

时间:11.678秒

以上方法只能暂时生效。当mysql重启后，你会发现已经恢复了。

还有一个永久的方法，需要修改my.cnf配置文件。

在[mysqld]部分添加一个句子，如果有，修改相应的值:

最大允许数据包=40M

这里的值可以以m为单位。修改后需要重启mysql才能生效。

在python中执行

如果用python代替navicat客户端需要多长时间？

先封装连接mysql的方法，然后拼接执行的sql语句。拼接时注意最后一个字符需要改成；

执行代码前获取当前时间戳，代码执行后再次获取时间戳。两次之间的时间间隔就是执行的时间，时间单位是s。

Python参考以下内容执行mysql代码

导入pymysql

'''

python3

作者:上海优优QQ群717225969

pip安装PyMySQL==0.9.3

'''

dbinfo = {

“主机”:“192.168.1.x”，

“用户”:“root”，

“密码”:“123456”，

【端口】:3306}

类DbConnect():

def init(self，db_cof，database= " "):

self.db_cof = db_cof

#打开数据库连接

self . db = pymysql . connect(database = database，

cursor class = pymysql . cursors . dict cursor，

**db_cof)

#使用cursor()方法获取操作光标。

self.cursor = self.db.cursor()

定义选择(自身，sql):

# SQL查询语句

# SQL = " SELECT * FROM EMPLOYEE # WHERE INCOME & gt；%s" % (1000)

self.cursor.execute(sql)

results = self.cursor.fetchall()

返回结果

定义执行(self，sql):

# SQL删除、提交和修改语句

# sql = "从员工中删除年龄& gt%s" % (20)

尝试:

#执行SQL语句

self.cursor.execute(sql)

#提交更改

self.db.commit()

除了:

#发生错误时回滚

self.db.rollback()

定义关闭(自己):

#关闭连接

self.db.close()

if name == 'main ':

导入时间

insert_sql = "插入到apps.apiapp_card值"

insert_values = " "。join(["('%s '，''，' test123 '，' 2019-12-17 ')，\ n " % str(I+100000)for I in range(10000)])

#拼接sql

sql =插入sql +插入值[:-3]+"；"

#打印(sql)

#执行sql

time1 = time.time()

db = DbConnect(dbinfo，database="apps ")

数据库执行(sql)