(PDO PHP)更新或插入多行的最快方法? [英] (PDO PHP) The fastest way to update or insert multiple rows?

问题描述

我不知道如何使用PDO更新或插入多行.请帮助我.

I don't know how to update or insert multiple rows by using PDO. Please help me.

我想到的是:

$stmt = $dbh->query("update_line_1; update_line_2; update_line_3");
//update_line_1: update table a set a.column1 = "s1" where a.id = 1
//update_line_2: update table a set a.column1 = "s2" where a.id = 2
//....

$stm = $dbh->query("insert_line_1; insert_line_3; insert_line_3");
//something is like the update line above.

我不知道这种方式行不通.如果您还有其他方法，请告诉我.非常感谢.

I don't know this way works or not. And If you have another way, please let me know. Thank you so so much.

如果我使用prepare语句，则每次都只更新每一行. (这比上面的方法安全得多)

And if I use prepare statement, I just update each row each time. (This is much more safe than above)

$stmt = $dbh->prepare("update table a set a.colum1 = :column1 where a.id = :id");
$stmt->bindParam(":column1","s1");
$stmt->bindparam(":id",1);
$stmt->execute();

我最不想做的事情是使用循环遍历数组中的所有元素，并每次更新或插入每个元素

是另一种安全地批量更新数据库或向数据库插入多行的方法吗?感谢您的帮助.

Is another way to mass safely update or insert multiple rows to database? thank for your help.

对不起，我的英语.

推荐答案

对于插入，您可以使用以下语法插入多行数据:

For inserts, you can insert multiple rows worth of data with the following syntax:

INSERT INTO table (col1, col2, col3)
VALUES
    ('1', '2', '3'),
    ('a', 'b', 'c'),
    ('foo', 'bar', 'baz')

对于更新，默认情况下，更新将影响符合查询条件的行数.所以像这样的东西会更新整个表

For updates, the update will by default effect as many rows as meet the criteria of the query. So something like this would update an entire table

UPDATE table SET col = 'a'

如果您尝试为每一行更新不同的值，那么除了对每个操作进行查询之外，您别无选择.但是我建议，以您的PDO示例为基础，您可以执行以下操作:

If you are trying to update different values for each row, you don't really have much of a choice other than to do a query for each operation. I would suggest however that, building on your PDO example, you could do something like this:

$update_array = array(
    1 => 'foo',
    2 => 'bar',
    10 => 'baz'
); // key is row id, value is value to be updated

$stmt = $dbh->prepare("UPDATE table SET column1 = :column1 where id = :id");
$stmt->bindParam(":column1",$column_value);
$stmt->bindparam(":id",$id);
foreach($update_array as $k => $v) {
    $id = $k
    $column_value = $v;
    $stmt->execute();
    // add error handling here
}

通过这种方法，您至少可以利用准备好的语句来最大程度地减少查询开销.

With this approach you are at least leveraging the use of the prepared statement to minimize query overhead.

这篇关于(PDO PHP)更新或插入多行的最快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！