如下当$snds有10条以上换行符隔开的数值时,insert效率非常低,如何优化执行这个先查询再根据insert条件执行insert的语句?
$localidds = $_POST['localidds'];
$status = $_POST['status'];
$remark_pending = $_POST['remark_pending'];
$remark_ng = $_POST['remark_ng'];
$name = $_POST['name'];
$inspector = $_POST['inspector'];
$localidds = explode("\n",$localidds);
$stmtCount = 0; // 初始化插入成功的条数为0
$stmt1Count = 0;
$successCount = 0;
//$localids=explode(PHP_EOL,$localids);//分隔换行的数据
for($i=0;$i<count($localidds);$i++){
if(!empty(trim($localidds[$i]))) //confirm if null or not
{
$localid = $localidds[$i];
$sql_global = "SELECT style,model,address,process,failure,repairtime,repeattimes,name FROM sev_repairdetail WHERE localid = :localid ORDER BY operation_time DESC LIMIT 1";
$stmt_global = $pdo->prepare($sql_global);
$stmt_global->execute(['localid' => $localid]);
$result = $stmt_global->fetch();
$new_model = $result['model'];
$new_style = $result['style'];
$new_repeattimes = $result['repeattimes'];
$new_address = $result['address'];
$new_failure = $result['failure'];
$new_process= $result['process'];
$new_repairtime = $result['repairtime'];
$new_name = $result['name'];
if($inspector){
$aa = $inspector;
}else{
$aa = $aa;
}
if($status == '2'){
$sql = "INSERT INTO sev_repairdetail (localid,model,style,repeattimes,address,failure,process,repairtime,status,wh_operator,remark_pending,remark_ng,name,ip)values(:localid,:model,:style,:repeattimes,:address,:failure,:process,:repairtime,:status,:aa,:remark_pending,:remark_ng,:name,:ip)";
}elseif($status == '8'){
$sql1 = "INSERT INTO sev_repairdetail (localid,model,style,repeattimes,address,failure,process,repairtime,status,wh_operator,remark_pending,remark_ng,name,ip)values(:localid,:model,:style,:repeattimes,:address,:failure,:process,:repairtime,:status,:aa,:remark_pending,:remark_ng,:name,:ip)";
$sql2 = "INSERT INTO sev_repairdetail (operation_time,localid,model,style,repeattimes,address,failure,process,repairtime,status,wh_operator,remark_pending,remark_ng,name,ip)values(now() + INTERVAL 10 SECOND,:localid,:model,:style,:repeattimes,:address,:failure,:process,:repairtime,'2',:aa,:remark_pending,:remark_ng,:name,:ip)";
}else{
$sql1 = "INSERT INTO sev_repairdetail (localid,model,style,repeattimes,address,failure,process,repairtime,status,wh_operator,remark_pending,remark_ng,name,ip)values(:localid,:model,:style,:repeattimes,:address,:failure,:process,:repairtime,:status,:aa,:remark_pending,:remark_ng,:name,:ip)";
}
$stmt = $pdo->prepare($sql);
$stmt1 = $pdo->prepare($sql1);
$stmt2 = $pdo->prepare($sql2);
// 绑定参数
$stmt->bindParam(':localid', $localid);
$stmt->bindParam(':model', $new_model);
$stmt->bindParam(':style', $new_style);
$stmt->bindParam(':repeattimes', $new_repeattimes);
$stmt->bindParam(':address', $new_address);
$stmt->bindParam(':failure', $new_failure);
$stmt->bindParam(':process', $new_process);
$stmt->bindParam(':repairtime', $new_repairtime);
$stmt->bindParam(':status', $status);
$stmt->bindParam(':aa', $aa);
$stmt->bindParam(':remark_pending', $remark_pending);
$stmt->bindParam(':remark_ng', $remark_ng);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':ip', $ip);
// 绑定参数
$stmt1->bindParam(':localid', $localid);
$stmt1->bindParam(':model', $new_model);
$stmt1->bindParam(':style', $new_style);
$stmt1->bindParam(':repeattimes', $new_repeattimes);
$stmt1->bindParam(':address', $new_address);
$stmt1->bindParam(':failure', $new_failure);
$stmt1->bindParam(':process', $new_process);
$stmt1->bindParam(':repairtime', $new_repairtime);
$stmt1->bindParam(':status', $status);
$stmt1->bindParam(':aa', $aa);
$stmt1->bindParam(':remark_pending', $remark_pending);
$stmt1->bindParam(':remark_ng', $remark_ng);
$stmt1->bindParam(':name', $new_name);
$stmt1->bindParam(':ip', $ip);
// 绑定参数
$stmt2->bindParam(':localid', $localid);
$stmt2->bindParam(':model', $new_model);
$stmt2->bindParam(':style', $new_style);
$stmt2->bindParam(':repeattimes', $new_repeattimes);
$stmt2->bindParam(':address', $new_address);
$stmt2->bindParam(':failure', $new_failure);
$stmt2->bindParam(':process', $new_process);
$stmt2->bindParam(':repairtime', $new_repairtime);
//$stmt2->bindParam(':status', 2);
$stmt2->bindParam(':remark_pending', $remark_pending);
$stmt2->bindParam(':remark_ng', $remark_ng);
$stmt2->bindParam(':name', $new_name);
$stmt2->bindParam(':ip', $ip);
$stmt->execute();
$stmt1->execute();
$stmt2->execute();
$stmtCount = $stmt->rowCount();
$stmt1Count = $stmt1->rowCount();
$stmt2Count = $stmt2->rowCount();
if ($stmtCount > 0) {
$successCount += $stmtCount;
}
if ($stmt1Count > 0) {
$successCount += $stmt1Count;
}
}
}
if($successCount >0 ){
echo json_encode(array(
"code" => 0,
"msg" => "success",
"count" => $successCount,
"data" => array()
));
}else{
echo json_encode(array(
"code" => 1,
"msg" => "error",
"count" => 0,
"data" => array()
));
}