通过PHP进行MySQL批量插入 [英] MySQL Bulk Insert Via PHP

问题描述

在PHP中，我从URI中提取了大量JSON数据，然后通过内置的json_decode函数将其序列化为关联的PHP数组.

In PHP, I pull a large amount of JSON data from a URI, then serialize it into an associative PHP array via the built-in json_decode function.

然后，我创建一个数组:

Then, I create an array:

$inserts = array();

我遍历JSON关联数组，为JSON数组中的每一项添加一个新的键/值对到我的$inserts数组中:

I loop through the JSON associative array, adding a new key/value pair to my $inserts array for each item in the JSON array:

foreach($JSON_data as $key => $value) {     
    $inserts[] = "(".mysql_real_escape_string($value["prop1"]).","
                    .mysql_real_escape_string($value["prop2"]).","
                    .mysql_real_escape_string($value["prop3"]).")";
}

然后，我只需插入已准备好的插入物即可执行批量插入:

Then, I perform a bulk insert simply by imploding the inserts I already prepared:

mysql_query("INSERT INTO `MyTable` (`col1`,`col2`,`col3`) VALUES ".implode(",",$inserts));

无论如何，我发现不再建议使用mysql_*系列.因此，我想知道如何使用准备好的语句来实现这种类型的模式，或者不使用新的可接受的结构吗?我担心的是要消除SQL注入，并用少于10个并发的开放连接(最好是1个)尽可能快地迅速更新MySQL.另外，为了使事情尽可能简单和快捷.

Anyways, I found that the mysql_* family is no longer suggested to be used. So I'm wondering how this type of pattern is suppose to be accomplished using prepared statements or w/e the new accepted constructs are? My concerns are to eliminate SQL injection, and also to update MySQL as quickly as possible with fewer than 10 concurrent, open connections (preferably 1). Also, to keep things as simple and quick as possible.

或者，如果存在执行这种批量交易的新模式或首选方法.

Or, if there's a new pattern or preferred method to perform such a bulk transaction.

推荐答案

如果使用准备好的语句，则可以使用foreach循环遍历$JSON_data数组，并使用该数据块运行INSERT.

If you use a prepared statement, you can loop over your $JSON_data array with a foreach loop and run the INSERT with that chunk of the data.

使用准备好的语句将减少构建查询的开销，只需在循环的每次迭代中将新数据发送到数据库即可.

Using prepared statements will reduce the overhead of building the query, simply sending the new data to the database on each iteration of the loop.

$query = mysqli_prepare("INSERT INTO `MyTable` (`col1`,`col2`,`col3`)
    VALUES(?,?,?)");

foreach($JSON_data as $key => $value) {
    $query->bind_param('sss',$value["prop1"],$value["prop2"],$value["prop3"];
    $query->execute();
}

请注意，bind_param()的第一个参数告诉它您将绑定多少个值，以及每个值的类型.
s对应于字符串数据，i对应于整数数据，d对应于double(浮点)，b对应于二进制数据.

Note that the first argument to bind_param() tells it how many values you will be binding, as well as the type for each value.
s corresponds to string data, i corresponds to integer data, d corresponds to double (floating point), and b corresponds to binary data.

另一个警告，不要使用否引用任何字符串数据，因为s数据类型告诉mysql需要一个字符串.如果在准备好的语句中引用?，它将告诉您参数数量错误.如果您引用字符串，它将在mysql中引用.

One other word of caution, do NOT quote any string data, as the s datatype tells mysql to expect a string. If you quote the ? in the prepared statement, it will tell you the number of params is wrong. If you quote the strings, it will be quoted in mysql.

如果要使用相同的范例(通过一个查询插入多行)，则可以使用多种方法.一种方法是创建一个将聚集bind_param调用并在执行查询时执行一个bind_param的类.的代码是此处.

If you want to use the same paradigm (inserting multiple rows with one query), there are ways to do it. One way is to create a class that will aggregate the bind_param calls and do one bind_param when you execute the query. Code for that is here.

这篇关于通过PHP进行MySQL批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！