php Batch Inserting Data Instances into mysql Tutorial

Preface

If I have such a table, I want to insert a lot of data into this table

CREATE TABLE IF NOT EXISTS `user_info` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' Self-increasing primary key ',
 `name` varchar(255) NOT NULL default '' COMMENT ' Name ',
 `age` int(11) NOT NULL default '0' COMMENT ' Age ',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' User information table ';

Bulk insertion

Method 1. Insert using for loop

When inserting a small amount of data into mysql, we generally 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";

//  Create a connection 
$conn = new mysqli($servername, $username, $password, $dbname, $port);

//  Detect a connection 
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, if you still use for loop to insert it is no problem, but it will take a long time.

Compare the time spent inserting a small amount of data with inserting a large amount of data under 1, using the above for loop insertion:

条数	时间 (单位：秒)
10	0.011
1000	0.585
10000	5.733
100000	60.587

Method 2. Merge inserts using the insert statement

In mysql, you can use insert statement to merge and insert, for example

INSERT INTO user_info (name, age) VALUES ('name1', 18), ('name2', 19); Indicates inserting two pieces of data at a time

Let's look at the sample code and look at the different number of data bars

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

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

//  Create a connection 
$conn = new mysqli($servername, $username, $password, $dbname, $port);

//  Detect a connection 
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;
  }

 //  Remove the last 1 Comma with a closing semicolon 
 $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 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 the insert merge insert saves a lot of time compared with the for loop insert just now

条数	时间 (单位：秒)
10	0.006
1000	0.025
10000	0.131
100000	1.23

Of course, if you think the array is too large and want to reduce the risk of sql errors, you can also use array_chunk to cut the array into blocks of specified size, and then insert each block with insert merge

Summarize