php批量isnert优化

Question

如下当$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()
      ));
    }    

Answer 1

【以下回答由 GPT 生成】

首先，优化的关键在于减少与数据库的交互次数。目前的代码在每次循环中执行一次查询和一次插入操作，这样的效率很低。

优化的思路是：将查询操作移动到循环之前，使用IN语句一次性查询所有需要的数据，然后再根据条件执行插入操作。

下面是优化后的代码：

```php
$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);

// 构建IN语句的参数
$inParams = implode(',', array_fill(0, count($localidds), '?'));

// 查询所有需要的数据
$sql_global = "SELECT localid, style, model, address, process, failure, repairtime, repeattimes, name FROM sev_repairdetail WHERE localid IN ($inParams) ORDER BY operation_time DESC";
$stmt_global = $pdo->prepare($sql_global);
$stmt_global->execute($localidds);

// 预处理插入语句
$sql_insert = "INSERT INTO table_name (localid, status, remark_pending, remark_ng, name, inspector) VALUES (:localid, :status, :remark_pending, :remark_ng, :name, :inspector)";
$stmt_insert = $pdo->prepare($sql_insert);

$stmtCount = 0; // 初始化插入成功的条数为0
$stmt1Count = 0;
$successCount = 0;

while($result = $stmt_global->fetch()) {
    $localid = $result['localid'];
    $new_model = $result['model'];
    $new_style = $result['style'];
    $new_repeattimes = $result['repeattimes'];
    $new_address = $result['address'];
    $new_failure = $result['failure'];

    // 绑定插入语句的参数
    $stmt_insert->bindParam(':localid', $localid);
    $stmt_insert->bindParam(':status', $status);
    $stmt_insert->bindParam(':remark_pending', $remark_pending);
    $stmt_insert->bindParam(':remark_ng', $remark_ng);
    $stmt_insert->bindParam(':name', $name);
    $stmt_insert->bindParam(':inspector', $inspector);

    if ($stmt_insert->execute()) {
        $stmtCount++;
    } else {
        $stmt1Count++;
    }
}

$successCount = $stmtCount;

// 返回结果

优化后的代码将查询操作移到了循环之前，使用IN语句一次性查询所有需要的数据。然后，在循环中执行插入操作，减少了与数据库的交互次数，提高了执行效率。

注意：根据实际情况，将代码中的table_name替换成正确的表名。 ```

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^