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
分享到:
相关推荐
里面有mongodb和oracle的批处理入库,还有access数据库与java的链接,以及activeMQ的例子
探索者需要插件,批量插入计算书,很实惠,很实用的的插件!!!
DESCRIPTION Even though running batch processes is an everyday task in almost all IT departments, Java developers have had few options for writing batch applications. The result? No standards, poor ...
Spring Batch in Action英文pdf版,最新Spring教科书
Spring Batch in Action
Spring Batch In Action
Spring batch in action,很好的一本书。
Work with all aspects of batch processing in a modern Java environment using a selection of Spring frameworks. This book provides up-to-date examples using the latest configuration techniques based on...
Manning.Spring.Batch.in.Action.Oct.2011 -- 英文
google batch processing in a neural network processor
讲解Spring Batch的书,很详细的哦。
http://u.download.csdn.net/images/btn_submit.png
Maning.Spring.Batch.in.Action.2012
spring-batch4.0.0 batch spring-batch集成 spring-batch.jar
Accurate, Large Minibatch SGD: Training ImageNet in 1 Hour
CREATE PROCEDURE batchInsert(in args int) BEGIN declare i int default 1; start TRANSACTION; while i <= args DO insert into A_student(id,name) VALUES (i, concat(“陈瓜皮-”, i)); set i = i+1; end ...
spring batch官方文档:https://docs.spring.io/spring-batch spring batch3.x中文文档:http://www.kailing.pub/SpringBatchReference spring batch官方入门实例:https://projects.spring.io/spring-batch/ 简单...
MybatisPlus自带的批量插入说白了就是for循环一条一条插入,我重新弄了一个