Home  >  Article  >  Backend Development  >  PHP implements batch insertion of data into mysql

PHP implements batch insertion of data into mysql

王林
王林forward
2020-01-31 21:46:484370browse

PHP implements batch insertion of data into mysql

Now there is such a table. How can we insert a large amount of data into this table?

CREATE TABLE IF NOT EXISTS `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(255) NOT NULL default '' COMMENT '姓名',
`age` int(11) NOT NULL default '0' COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

Batch insert

Method 1. Use for loop to insert

When inserting a small amount of data into mysql, we usually use for loop

(related Video tutorial sharing: php video tutorial)

$arr = [ 
[
'name' => 'testname1',
'age' => 18,
],
[
'name' => 'testname2',
'age' => 19,
],
[
'name' => 'testname3',
'age' => 18,
],
];

$servername = "localhost";
$port = 3306;
$username = "username";
$password = "password";
$dbname = "mytestdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname, $port);

// 检测连接
if ($conn->connect_error) {
die("connect failed: " . $conn->connect_error);
} 

$costBegin = microtime(true);

foreach($arr as $item) {
$sql = sprintf("INSERT INTO user_info (name, age) VALUES ( '%s', %d);", $item['name'], (int)$item['age']); 
if ($conn->query($sql) === TRUE) {
echo "insert success";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}

$costEnd = microtime(true);
$cost = round($costEnd - $costBegin, 3);
var_dump($cost);

$conn->close();

If you want to insert a large amount of data in batches, there is no problem if you also use a for loop to insert, but it will take longer. Compare inserting a small amount of data with inserting a large amount of data. The time it takes to insert using the for loop above: Number of items time (unit: seconds)

PHP implements batch insertion of data into mysql

Method 2. Use the insert statement to merge Insert

In mysql, you can use the insert statement to merge and insert. For example,

INSERT INTO user_info (name, age) VALUES (&#39;name1&#39;, 18), (&#39;name2&#39;, 19);表示一次插入两条数据

$arr = [ 
[
&#39;name&#39; => &#39;testname1&#39;,
&#39;age&#39; => 18,
],
[
&#39;name&#39; => &#39;testname2&#39;,
&#39;age&#39; => 19,
],
[
&#39;name&#39; => &#39;testname3&#39;,
&#39;age&#39; => 18,
],
// 此处省略
……
……
];

$servername = "localhost";
$port = 3306;
$username = "username";
$password = "password";
$dbname = "mytestdb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname, $port);

// 检测连接
if ($conn->connect_error) {
die("connect failed: " . $conn->connect_error);
} 

$costBegin = microtime(true);

if (!empty($arr)) {
$sql = sprintf("INSERT INTO user_info (name, age) VALUES ");

foreach($arr as $item) {
$itemStr = &#39;( &#39;;
$itemStr .= sprintf("&#39;%s&#39;, %d", $item[&#39;name&#39;], (int)$item[&#39;age&#39;]);
$itemStr .= &#39;),&#39;;
$sql .= $itemStr;
}

// 去除最后一个逗号,并且加上结束分号
$sql = rtrim($sql, &#39;,&#39;);
$sql .= &#39;;&#39;;

if ($conn->query($sql) === TRUE) {
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}

$costEnd = microtime(true);
$cost = round($costEnd - $costBegin, 3);
var_dump($cost);

$conn->close();

Let’s take a look at the time comparison between a small amount of data and a large amount of data. From the overall time, it can be seen that insert merge insertion saves a lot of time compared to the for loop insertion just now, and the effect is very obvious. Number of times (unit: seconds)

PHP implements batch insertion of data into mysql

If you think If the array is too large, if you want to reduce the risk of SQL errors, you can also use array_chunk to cut the array into blocks of a specified size, and then merge and insert each block.

Recommended related articles and tutorials: php tutorial

The above is the detailed content of PHP implements batch insertion of data into mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:cnblogs.com. If there is any infringement, please contact admin@php.cn delete