mysql資料庫批次插百萬資料的方法
因為前一段時間有個需求向資料庫插入30多萬條資料,雖然圓滿完成任務,但是感覺方法不是最好的方法,正巧今日看到此方法,向mysql資料庫批次插百萬資料的方法,只需要短短的10秒鐘就可以完成,太棒了,再次分享給大家。
的時間對比。從總體時間上,可以看出insert合併插入比剛才for迴圈插入節約了很多時間,效果很明顯
如果你覺得陣列太大,想要減少sql錯誤的風險,也可以使用
array_chunk
將陣列切成指定大小的塊,然後對每個塊進行insert合併插入。
mysql百萬級資料插入,耗時10s左右
最近做的一個專案,需求就是這樣,寫個功能模組,實現批次匯入,為客服省點時間(好吧,需求就是需求)。
好在插入的資料,都是些連續的數字,所以可以利用
foreach迴圈出這些資料,然後拼接成mysql的insert語句,進行大欄位的批次插入。原理就是這麼個原理,當然過程中有坑,什麼開啟擴充套件限制,暫且只看邏輯方面的吧。
框架的話,不是laravel,用的ci,例項程式碼如下:
/**
*
@desc
批次匯入百萬條資料入庫(方法已經寫通用,資料最少5000條,最大100W條)
*
@date
2020-10-26 20:45:45
*
@param
[int $start_no 起始號;int $end_no 截止號;string $express_type 型別]
*
@return
[type]
*/
public
function
import_million_express_no
()
{
//基本資料設定
header(
‘Content-Type:text/html;charset=utf-8’
);
ini_set(
‘memory_limit’
,
‘128M’
);
//設定型別對應資料庫中的表名
$express_to_form = [
‘test1’
=>
‘from1’
,
//平臺1對應的表名
‘test2’
=>
‘from2’
,
//平臺2對應的表名
];
//獲取引數
$start_no = trim(
$this
->input->post(
‘start_no’
));
$end_no = trim(
$this
->input->post(
‘end_no’
));
$express_type = trim(
$this
->input->post(
‘express_type’
));
//判斷引數是否存在
if
(!$start_no || !$end_no){
echo
‘’
;
return
;
}
//起始單號不能大於等於截止單號,錄入數量至少為5000個
if
($start_no >= $end_no) {
echo
‘’
;
return
;
}
else
{
if
($end_no - $start_no <
5000
) {
echo
‘’
;
return
;
}
if
($end_no - $start_no >
1000000
) {
echo
‘’
;
return
;
}
}
//判斷資料型別是否存在
$table_name = $express_to_form[$express_type];
if
(!$table_name){
echo
‘’
;
return
;
}
else
{
//判斷初始單號,截止單號是否已經錄入
$sql1 =
“select id from {$table_name} where express_no = {$start_no}”
;
$res1 =
$this
->db->query($sql1)->row();
if
($res1){
echo
‘’
;
return
;
}
$sql2 =
“select id from {$table_name} where express_no = {$end_no}”
;
$res2 =
$this
->db->query($sql2)->row();
if
($res2){
echo
‘’
;
return
;
}
}
/***上面的一系列判斷的可以不用看,直接看下面怎麼對資料進行邏輯處理***/
//將起始號和截止號進行區間劃分
$length = $end_no - $start_no +
1
;
$times = floor($length /
5000
);
$temp_data = [];
for
($i=
0
;$i<$times;$i++){
$temp_data[$i][
‘start_no’
] = $start_no;
//起始編號
$temp_data[$i][
‘end_no’
] = $start_no +
4999
;
//結束編號
$start_no +=
5000
;
//下一輪迴圈的起始編號
}
//檢驗陣列最後一組資料,判斷是否需要再新增
if
($end_no > $temp_data[$times
-1
][
‘end_no’
]){
$temp_data[$times][
‘start_no’
] = $temp_data[$times
-1
][
‘end_no’
] +
1
;
$temp_data[$times][
‘end_no’
] = $end_no;
}
//進行匯入資料庫sql語句的拼接
$add_time = time();
$add_user =
$this
->session->userdata[
‘user_name’
];
$tmp_val =
“(‘{$add_time}’,‘$add_user’,0,‘%s’,0),”
;
for
($j=
0
;$j //迴圈拼接sql插入語句 $sql = “insert into {$table_name} (field1,field2,field3,field4,field5) values ” ; for ($i=$temp_data[$j][ ‘start_no’ ]; $i<=$temp_data[$j][ ‘end_no’ ]; $i++) { $sql 。= sprintf($tmp_val, $i); } $sql = trim($sql, ‘,’ ) 。 ‘;’ ; $bool = $this ->db->query($sql); //執行插入有誤,寫進日誌異常表from3中 if (!$bool){ // 記錄日誌 $log_info = array (); $log_info[ ‘field1’ ] = time(); $log_info[ ‘field2’ ] = ‘型別:’ 。$express_type。 ‘執行有誤,單號’ 。$temp_data[$j][ ‘start_no’ ]。 ‘-’ 。$temp_data[$j][ ‘end_no’ ]。 ‘執行失敗’ ; $log_info[ ‘field3’ ] = $this ->session->userdata[ ‘user_name’ ]; $this ->db->insert( ‘from3’ , $log_info); //錯誤日誌標誌 $err_log_info = TRUE ; } } //資料返回 if ($err_log_info){ echo ‘’ ; } else { echo ‘’ ; } return ; } 上面就是封裝的一個完整的類,引數驗證什麼的,可以不用看了,直接看sql語句拼接,其實最後發現吧,做出來也沒啥。經過測試,基本耗時在10s左右徘徊。 本文參考公眾號 學習與交流 PHP自學中心