面試問題:如果創建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)
版權所有(c) 2000,2019,Oracle和/或其附屬公司。版權所有
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)
db.close()
time2 = time.time()
Print("總運行時間:%s"% (time2-time1))
用python執行結果:總時間太長:1.016256999969482,結果超出我的想象。10w條數據只需要1秒!
面試問題:如何創建10w測試數據並將10w不同的數據插入數據庫?
標簽:sql語句改善名稱data = ==commandatiblewhere