Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use php in mysql to insert data in batches. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

If I have such a table, 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 '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'

Batch insert

Method 1. Insert using for loop

When inserting a small amount of data into mysql, we usually use a 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)

/ / check the 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 ('% slots,% d);", $item ['name'], (int) $item [' age'])

If ($conn- > query ($sql) = TRUE) {

Echo "insert success"

} else {

Echo "Error:". $sql. "

".$ 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 bulk, there will be no problem if you still use the method of for loop to insert it, but it will take a long time. Compare the time it takes to insert a small amount of data versus a large amount of data using the for loop above: number of entries (in seconds).

Method 2. Use insert statement to merge insert

In mysql, you can use insert statements for merge insertion, such as:

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

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

/ / check the 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 ("'% slots,% d", $item ['name'], (int) $item [' age'])

ItemStr. ='),'

$sql. = $itemStr

}

/ / remove the last comma and add the ending semicolon

$sql = rtrim ($sql,',')

$sql. =';'

If ($conn- > query ($sql) = TRUE) {

} else {

Echo "Error:". $sql. "

".$ 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, we can see that insert merge insertion saves a lot of time than just for loop insertion, and the effect is obvious in seconds.

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.

About how to use php in mysql bulk insert data to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.