面試問題:如果創建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