PHP inserts data in batches into mysql (code example)

Suppose I have such a table and I want to 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

$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 the time it takes to insert a small amount of data with a large amount of data. Use the above for loop to insert the time: number of items (unit: seconds)

Method 2: Use insert statement to merge and insert

In mysql, you can use insert statement to merge and insert, such as

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

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

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

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

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

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)

If you think the array is too big and want to reduce the risk of SQL errors, you can also use array_chunk to cut the array into chunks of a specified size, and then Perform insert merge insertion for each block.

For more PHP related knowledge, please visit php tutorial!

The above is the detailed content of PHP inserts data in batches into mysql (code example). For more information, please follow other related articles on the PHP Chinese website!