php - 插入批量 php mysql，检查重复项和字段重复项的总和值

Question

我使用 foreach 有 100 万条数据。前表: table

数据 data我想使用批处理/多重插入插入该数据，但是当我收到重复数据时遇到问题。如果数据重复，我希望字段金额将求和并使用金额重复数据更新金额字段。

这是我之前的代码

<?php
foreach ($data_transaksi as $key)
      {
          if($key['STATUS_COA'] != $key['CHART_OF_ACCOUNT_STATUS'])
          {
              if($key['ACCOUNT_CATEGORY_CODE'] == '9')
              {
                  $amount = round($key['AMOUNT'],2);
              }
              else
              {
                  $amount = round($key['AMOUNT'],2) *-1;
              }
          }
          else
          {
              if($key['ACCOUNT_CATEGORY_CODE'] == '9')
              {
                  $amount = round($key['AMOUNT'],2)*-1;
              }
              else
              {
                  $amount = round($key['AMOUNT'],2);
              }
          }

          
          $dt_exsis = $this->ledger_model->cek_data_coa_exsis($key['COA_CODE'],$modul,$ID_USER);
          if(empty($dt_exsis['id']))
          {
              //TRYINSERTINGBATCH
              // $datainsert[] = '('.$key['COA_CODE'].','.$amount.','.$ID_USER.',"'.$modul.'")';
              // $test = $key['COA_CODE'];

              $datainput = array(
                      'COA_CODE' => $key['COA_CODE'],
                      'AMOUNT' => $amount,
                      'MODUL' => $modul,
                      'USER_ID' => $ID_USER
                      );
                      
              $this->ledger_model->save_rows_to_table($datainput,'finance_lapkue_temp');
          }
          else
          {
              $amount_fix = $amount + $dt_exsis['AMOUNT'];
              $data=array(
                'AMOUNT' => $amount_fix
              );
              $this->ledger_model->edit_rows_to_table_where($data,'finance_lapkue_temp','id',$dt_exsis['id']);
              // $q = "UPDATE finance_lapkue_temp set AMOUNT = '$amount_fix' where id = '".$dt_exsis['id']."'";
              // $this->db->query($q);
          }
          // $data_amount[$key['COA_CODE']] += $amount;
      }
     ?>

如果我使用这段代码，过程会很慢

Answer 1

好的选择是仅将数据传递到要插入的数据库。所有的数据清理任务都可以在 Controller 中完成。

// Create a data array and add all info
$data = [];
//if user does not exist add it in array
if (empty($dt_exist($id))) {
  $data[$ID_USER] = array(
                  'COA_CODE' => $key['COA_CODE'],
                  'AMOUNT' => $amount,
                  'MODUL' => $modul,
                  'USER_ID' => $ID_USER
                  );
}
else {
      //if user exist in $data just modify the amount
      if (!empty($data[$ID_USER])) {
        $data[$ID_USER]['AMOUNT'] += $dt_exsis['AMOUNT']; 
     }
      else {
        // if user does not exist in data create add all info
        $data[$dt_exsis['ID_USER']] = array(
                  'COA_CODE' => $dt_exsis['COA_CODE'],
                  'AMOUNT' => $dt_exsis['amount'],
                  'MODUL' => $dt_exsis['modul'],
                  'USER_ID' => $dt_exsis['ID_USER']
                  );
    }
}

这将节省对数据库的多次调用，最后您可以传递 $data 并执行多次插入。