批量参数化插入

Question

批量参数化插入

10

我想将一些硬编码的查询改为使用参数化输入，但是遇到了一个问题：如何为参数化批量插入格式化输入？

目前，代码看起来像这样：

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

一个可能的解决方案（修改自如何使用PHP和PDO将数组插入单个MySQL预处理语句）看起来是：

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

是否有更好的方法使用参数化查询来进行批量插入？

- Zac Howland

1

一个针对预处理语句的潜在解决方案是“单次准备，多次执行”。 - Your Common Sense

我试图避免这样做以限制事务处理的需求。如果任何一个插入操作失败，整个操作都应该失败。 - Zac Howland

如果我执行单独的语句，我将不得不添加一个事务，以便在添加任何给定行时出现错误时可以进行回滚。批量插入不需要事务，因为它要么全部成功，要么全部失败。 - Zac Howland

2个回答

2

为什么不使用“准备一次，执行多次”的方法呢？我知道你希望它要么全部失败，要么全部成功，但是用事务处理并不难。

http://www.php.net/manual/en/pdo.begintransaction.php

http://www.php.net/manual/en/pdo.commit.php

http://www.php.net/manual/en/pdo.rollback.php

- Stephen

这并不难，但它给这段代码增加了一定的复杂度，而这并不是真正需要的。批量插入是我问题的最优雅解决方案，只是语法上我不太确定。 - Zac Howland

此外，这里使用的是本地的 sqlsrv 扩展，这并不是世界上最明智的选择（如果他使用 PDO 可能会更好），但本地的 sqlsrv 扩展在很多方面都存在缺陷... - ircmaxell

1

如果重点是转向参数化查询，为什么不同时转向PDO呢？至于“复杂性”，添加一个检查是否调用execute()失败的操作并不难，如果是这样，执行回滚（rollback()）然后进行任何其他错误处理即可。 - Stephen

因为PDO不再支持使用SQL Server（TDS）驱动程序，并且为连接到此数据库创建特例不是可行的选择（已经建立了一个全局连接字符串，可以与mssql_和sqlsrv_扩展一起正常工作，但在使用ODBC时无法与PDO一起使用）。 - Zac Howland

@ZacHowland 我不会说这是最优雅的解决方案，也许我会说这是最简单的。对我来说，“准备一次/多次执行”是最优雅的。 - Pierre de LESPINAY

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ircmaxell · Accepted Answer

好的，你有三个选择。

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.
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);
}

任何一种方法都可以。选择最适合您需求的方法即可...