批量参数化插入

发布于 2024-10-11 10:24:59 字数 1059 浏览 8 评论 0原文

我正在尝试将一些硬编码查询切换为使用参数化输入，但遇到了一个问题：如何格式化参数化批量插入的输入？

目前，代码如下所示：

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

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

原文

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?

Currently, the code looks like this:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉城凉梦凉人心 2024-10-18 10:24:59

嗯，你有三个选择。

构建一次 - 执行多次。基本上，您为一行准备一次插入，然后循环执行它的行。由于 SQLSERVER 扩展不支持在准备好查询后重新绑定查询（您需要执行带有引用的肮脏黑客）可能不是最好的选择。
构建一次 - 执行一次。基本上，您按照示例中所述构建一个巨大的插入，将其绑定一次，然后执行它。这有点脏，并且错过了准备好的查询所带来的一些好处。然而，由于选项 1 的参考文献的要求，我会这样做。我认为构建一个巨大的查询比依赖变量引用更干净。

构建多个 - 执行多个。基本上，采用您正在执行的方法，并对其进行调整以每隔这么多记录重新准备查询。这可以防止过大的查询并“批量”查询。所以像这样：

$sql = '插入 my_table (field1, field2, field3) 值';
$参数=数组();
$数据=数组();

$execute = 函数($params, $data) use ($my_connection, $sql) {
    $查询=$sql。内爆(', ', $参数);
    $stmt = sqlsrv_prepare($my_connection, $query, $data);
    sqlsrv_execute($stmt);
}

而（$我的条件）{
    $parameters[] = '(?, ?, ?)';
    $数据[] = 值1;
    $数据[] = 值2;
    $数据[] = 值3；
    if (count($parameters) % 25 == 0) {
        //每25条记录刷新一次
        $执行（$参数，$数据）；
        $参数=数组();
        $数据=数组();
    }
}
if (!empty($parameters)) {
    $执行（$sql，$参数，$数据）；
}

任何一种方法都足够了。做你认为最适合你的要求的事情......

Well, you have three options.

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

Either method will suffice. Do what you think fits your requirements best...

回复收藏 0 原文