如何在php中将插入质量值与预准备语句结合起来

Question

In the old question asked below the top answer describes how to speed up the MySQL insert process. Since this an old question I'm curious on how to do this with prepared statements. I use MySQL with innoDB. I'll paste in an example from the answer here for people who don't want to check the link.

Here I'll describe 3 methods for inserting data, ranging from slow to fast:

The following is extremely slow if you have many rows to insert:

INSERT INTO mytable (id,name) VALUES (1,'Wouter');
INSERT INTO mytable (id,name) VALUES (2,'Wouter');
INSERT INTO mytable (id,name) VALUES (3,'Wouter');

This is already a lot faster:

INSERT INTO mytable (id, name) VALUES
  (1, 'Wouter'),
  (2, 'Wouter'),
  (3, 'Wouter');

And this is usually the fastest:

Have CSV file that looks like this:

1,Wouter
2,Wouter
3,Wouter

And then run something like

LOAD DATA FROM INFILE 'c:/temp.csv' INTO TABLE mytable

old question

This is obvious an old answer. I want to know how to do this with prepared statements in a way that will insert as fast as possible, but let's go one step at a time. First I want to know how to do this with a prepared statements. Any examples that can be given will be much appreciated. I will reply or edit this question with any extra information that you may need.

I'm using mysqli_ *Edited for Rick James's question.

I'm going to do my best and make an educated guess on how this can be done. Please correct me if I'm wrong.

I think I can convert

INSERT INTO mytable (id, name) VALUES
  (1, 'Wouter'),
  (2, 'Wouter'),
  (3, 'Wouter');

into

$bindParamsToBuild = '($myArray[key], ?,?),'
for (i = 0; i < count($myArray); i++)
{
 if (i === count($myArray))
 {
  $bindParamsToBuild += '($myArray[key], ?,?);';
 }
 else
 {
  $bindParamsToBuild += '($myArray[key], ?,?),';
 }
}
"INSERT INTO mytable (id, name) VALUES".$bindParamsToBuild;

Answer 1

Are you using PDO or mysqli?

If using mysqli, use real_escape_string() separately on each item in the list. It is a bit tedious but can be made easier by gradually building the insert string. I suggest not putting more than 100 items into a single INSERT.

https://stackoverflow.com/a/780046/1766831 is a pretty good discussion of the details. (But be sure to use mysqli_*, not mysql_*.)

Plan A: Construct the query as discussed in some of those links. Use real_escape_string and PHP string operations instead of "prepare".

Plan B: Build a string with lots of question mark. Simultaneously, build an array with the values to put into those question marks. Then do a single "prepare" to finish the multi-row INSERT statement.