批量参数化插入 [英] Bulk Parameterized Inserts
问题描述
我正在尝试将一些硬编码查询切换为使用参数化输入,但是遇到一个问题:如何格式化参数化批量插入的输入?
I'm trying to switch some hard-coded queries to use parameterized inputs, but I've run into a problem: How do you format the input for parameterized bulk inserts?
当前,代码如下:
$data_insert = "INSERT INTO my_table (field1, field2, field3) ";
$multiple_inserts = false;
while ($my_condition)
{
if ($multiple_inserts)
{
$data_insert .= " UNION ALL ";
}
$data_insert .= " SELECT myvalue1, myvalue2, myvalue3 ";
}
$recordset = sqlsrv_query($my_connection, $data_insert);
A potential solution (modified from How to insert an array into a single MySQL Prepared statement w/ PHP and PDO) appears to be:
$sql = 'INSERT INTO my_table (field1, field2, field3) VALUES ';
$parameters = array();
$data = array();
while ($my_condition)
{
$parameters[] = '(?, ?, ?)';
$data[] = value1;
$data[] = value2;
$data[] = value3;
}
if (!empty($parameters))
{
$sql .= implode(', ', $parameters);
$stmt = sqlsrv_prepare($my_connection, $sql, $data);
sqlsrv_execute($stmt);
}
是否有更好的方法来完成带有参数化查询的批量插入?
Is there a better way to accomplish a bulk insert with parameterized queries?
推荐答案
嗯,您有三个选择.
Build once - execute multiple. Basically, you prepare the insert once for one row, then loop over the rows executing it. Since the SQLSERVER extension doesn't support re-binding of a query after it's been prepared (you need to do dirty hacks with references) that may not be the best option.
构建一次-执行一次.基本上,您按照示例中的说明构建了一个巨型插入物,将其绑定一次,然后执行它.这有点脏,并且错过了准备查询所提供的一些好处.但是,由于需要选项1的引用,我愿意这样做.我认为构建大型查询而不是依赖变量引用会更清洁.
Build once - execute once. Basically, you build one giant insert as you said in your example, bind it once, and execute it. This is a little bit dirty and misses some of the benefits that prepared queries gives. However, due to the requirement of references from Option 1, I'd do this one. I think it's cleaner to build a giant query rather than depend on variable references.
构建多个-执行多个.基本上,采用您正在执行的方法,并对其进行调整以每隔这么多记录重新准备查询.这样可以防止过大的查询,并使查询成批".像这样:
Build multiple - execute multiple. Basically, take the method you're doing, and tweak it to re-prepare the query every so many records. This prevents overly big queries and "batches" the queries. So something like this:
$sql = 'INSERT INTO my_table (field1, field2, field3) VALUES ';
$parameters = array();
$data = array();
$execute = function($params, $data) use ($my_connection, $sql) {
$query = $sql . implode(', ', $parameters);
$stmt = sqlsrv_prepare($my_connection, $query, $data);
sqlsrv_execute($stmt);
}
while ($my_condition) {
$parameters[] = '(?, ?, ?)';
$data[] = value1;
$data[] = value2;
$data[] = value3;
if (count($parameters) % 25 == 0) {
//Flush every 25 records
$execute($parameters, $data);
$parameters = array();
$data = array();
}
}
if (!empty($parameters)) {
$execute($sql, $parameters, $data);
}
这两种方法都足够.做您认为最适合您需求的事情...
Either method will suffice. Do what you think fits your requirements best...
这篇关于批量参数化插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!