php insert batch失败,Batch Insert in PHP MySQLi

Batch Insert in PHP MySQLi When I was using oracle database long time ago, I know batch insert is much faster if we do know the data we want to insert. Recently I was using PHP 5.6 and MySQL. I found out that the batch insert is hundreds of times faster. Here is how I do the batch insert. public function batchInsertDailyInfo($dailySpends){ $logger = $this->ioc->getService("logger"); $conn = $this->getJobDBConn(); $query = " INSERT INTO daily_spendings (date, job_id, daily_spending) VALUES "; $i = 0; foreach ($dailySpends as $spend){ if($i > 0){ $query .= ", "; } $i++; $query .= "('" . "{$spend['date']}". "', {$spend['job_id']}, {$spend['spending']})"; if($i > $this->batchSize){ $conn->query($query); $i = 0; $query = " INSERT INTO daily_spendings (date, job_id, daily_spending) VALUES "; } } if($i > 0){ $conn->query($query); } $this->closeDBConn($conn); } And I have a very simple PHPUNIT code to test the perf.     //501 ms, Memory: 38.50MB, 35000 rows batch insert     public function testBatchInsertDailyInfo(){     $jobIDs = 4294967295;     $date = "2016-08-23";     $spending = 12.36;     $spendings = array();     //35000     for($i = 0; $i<35021; $i++){     $spendings[] = array(     'job_id' => $jobIDs + $i,     'date' => $date,     'spending' => $spending,     );     }     $this->mySQLDAO->batchInsertDailyInfo($spendings);     } It should relate to the size of each row as well. But this works for me in this requirement. There is one more option, we can prepare and bind_param as well. /** * * @param unknown $dailySpends * @return unknown */ public function batchInsertDailyInfo3($dailySpends){   $logger = $this->ioc->getService("logger");   $conn = $this->getJobDBConn();   $n = 0;   $values = array();   $params = array();   foreach ($dailySpends as $spend){   //prepare values   $values = array_merge($values,array($spend['date'], $spend['job_id'], $spend['spend']));   $n++;   if($n > $this->batchSize){   $this->batchPrepareInsert($conn, $values, $n);   $values = array();   $params = array();   $n = 0;   }   }   if($n > 0){   $this->batchPrepareInsert($conn, $values, $n);   }   $this->closeDBConn($conn);   }   private function batchPrepareInsert($conn, $values, $n){   $query = "   INSERT INTO   daily_spendings (date, job_id, daily_spending) VALUES(?, ?, ?)".str_repeat(",(?,?,?)",$n-1);   $stmt = $conn->prepare($query);   //prepare types and bind params   $types = str_repeat("sid", $n);   $params[] = &$types;   for($i = 0; $i < count($values); $i++) {   $params[] = & $values[$i];   }   //bind params   call_user_func_array(array($stmt, 'bind_param'), $params);   //excute batch   $stmt->execute();   } References: http://stackoverflow.com/questions/19512498/mysqli-multiple-row-insert-simple-multi-insert-query