Drupal 7 |使用 db_update 更新多行 [英] Drupal 7 | update multiple rows using db_update

问题描述

我有一个像

Array (
    [1] => 85590762,22412382,97998072
    [3] => 22412382 

)

其中 key 是 item_id，value 是我需要针对某个项目更新的列的值.我可以在循环中使用 db_update 但由于性能原因，我想避免这种策略.我想在单个 db 调用中更新所有行.我认为还使用 db_query 不是一个好主意.那么有没有办法使用 db_update 来更新这些行?

Where key is the item_id and value is the value of a column which I need to update against an item. I can use db_update in a loop but i want to avoid this strategy due to performance. I want to update all the rows in a single db call. Also using db_query I think will not be a good idea. So is there any way using db_update to update these rows?

根据上面的数据，标准的mysql查询会像

According to above data, standard mysql queries will be like

update items set sold= 1, users = '85590762,22412382,97998072' Where item_id = 1; 
update items set sold = 1, users = '22412382' Where item_id = 3;

推荐答案

很遗憾，您不能这样做.目前并且可能在该功能中，将不支持使用一个 db_update 更新具有不同值的不同行上的(多个)值.

Unfortunately you can't do that. Currently and probably in the feature, there won't be support for updating (multiple) values on different rows with different values with one db_update.

如果您的数据是这样的:

If your data is this:

$for_update = array(
  1 => "85590762,22412382,97998072",
  3 => "22412382",
);

您可以执行以下操作:

foreach ($for_update as $key => $value) {
  $fields = array('sold' => 1, 'users' => $value);
  db_update('items')->fields($fields)->condition('item_id', $key, '=')->execute();
}

优点:其他模块可以在您的查询中挂钩，可以支持多个数据库驱动程序
缺点:对象初始化缓慢，大量的数据库连接/流量

Pros: other modules can hook inside your query, can support multiple DB drivers
Cons: Object initialization is slow, lots of DB connection/traffic

... 因为在那种情况下没有对象实例化/操作，这有点昂贵和其他转换.(即:https://drupal.stackexchange.com/questions/129669/whats-faster-db-query-db-select-or-entityfieldquery)

... because in that case there's no object instantiation/operation, which is a bit costly and other conversion. (i.e.: https://drupal.stackexchange.com/questions/129669/whats-faster-db-query-db-select-or-entityfieldquery)

foreach ($for_update as $key => $value) {
  db_query("UPDATE items SET sold = :sold, users = :users WHERE item_id = :item_id",
    array(':sold' => 1, ':users' => $value, ':item_id' => $key)
  );
}

优点:无对象初始化和操作<所以它更快
缺点:其他模块无法在您的查询中挂钩，您的代码可能会在不同的数据库驱动程序下中断，大量的数据库连接/流量

Pros: no object initialization and operation < so it's faster
Cons: other modules can't hook inside your query, your code can break under different DB drivers, lots of DB connection/traffic

在某些情况下，这可以稍微提高您的表现.

This can boost a bit your performance at some case.

$transaction = db_transaction();
try {
  foreach ($for_update as $key => $value) {
    $fields = array('sold' => 1, 'users' => $value);
    db_update('items')->fields($fields)->condition('item_id', $key, '=')->execute();
  }
}
catch (Exception $e) {
  $transaction->rollback();
  watchdog_exception('my_type', $e);
}

注意:交易最常用，当您想要一切或什么都不想要时.但是使用某些数据库驱动程序，您可以优化 COMMIT 命令.
在第 1 种情况下，您会得到如下信息:

Note: transaction mostly used, when you want everything or nothing. But with some DB drivers, you can optimize out the COMMIT commands.
In case 1 you get something like this:

UPDATE items SET sold = 1, users = '85590762,22412382,97998072' WHERE item_id = 1;
COMMIT;
UPDATE items SET sold = 1, users = '22412382' WHERE item_id = 3;
COMMIT;

通过交易，您可以执行以下操作:

With transaction you do something like this:

UPDATE items SET sold = 1, users = '85590762,22412382,97998072' WHERE item_id = 1;
UPDATE items SET sold = 1, users = '22412382' WHERE item_id = 3;
COMMIT;

使用 COMMIT，您将数据写到最终位置(进入长期存储，直到此为止，它只是内存中的某个位置或临时位置).当您使用回滚时，您将放弃这些更改.至少我是这样理解的.(即，当我使用 sqlite 时，我的性能得到了提升.)

With COMMIT you write out the data to the final place (into long-term storage, until this, it's only somewhere in the memory or a temporary place). When you use rollback, you drop these changes. At least this is how I understand this. (i.e. I get performance improvement with this when I used sqlite.)

同情况 1 或 2 +
优点:如果需要保持一致，可以回滚数据，只需将数据写入驱动器一次<所以sql只一次"做IO，大量的数据库连接/流量

Same as case 1 or 2 +
Pros: you can rollback your data if you need to be consistent, you write out data only once to the drive < so sql only "once" do IO, lots of DB connection/traffic

$ids = array();
$when_then = "";
foreach ($for_update as $key => $value) {
  $ids[] = $key;
  $when_then .= "WHEN $key THEN '$value' ";
}
db_query("UPDATE items
  SET sold = 1, users = CASE item_id 
                    $when_then
                    ELSE users
                    END
  WHERE item_id IN(:item_ids)", array(':item_ids' => $ids));

这可能是从这里开始的最快方式.
注意:$when_then 变量不包含最佳解决方案，如果您可以使用参数或转义不安全数据会更好.

Probably this is the fastest way from all from here.
NOTE: $when_then variable doesn't contain the best solution, it's better if you can use the params or you escape unsafe data.

优点:在你的服务器和 sql 之间只会有一个请求和更少的冗余数据，一个更大的 sql 查询比很多小的查询更快
缺点:其他模块无法在您的查询中挂钩，您的代码可能会在不同的数据库驱动程序下中断

Pros: between your server and sql there will be only one request and less redundant data, one bigger sql queries faster then a lot of small ones
Cons: other modules can't hook inside your query, your code can break under different DB drivers

另外请注意，在(我认为)PHP 5.3 之后，默认情况下您不能在 db_query 或 PDO 中运行多个语句，因为它可能是 SQL 注入，因此它会阻塞.但是你可以设置这个，但不推荐.(PDO 支持多个查询(PDO_MYSQL、PDO_MYSQLND))

Also please note, after (I think) PHP 5.3 you can't run more than one statement in db_query or in PDO by default, because of it can be an SQL Injection, so it blocks. However you can set this, but not recommended. (PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND))

13-05-2019
顺便提一下，我在几个月前对中等数据集进行了一些性能测量，以通过 db_select 和 db_query 查询一些数据.这些性能的大小是:在你运行一个db_select的情况下，你可以运行两个db_query.这也适用于 db_update 和 db_query.该测试使用简单的 SELECT 查询了一些列，没有 JOINS，没有花哨的东西，只有两个条件.当然，测量还包含/包括建立这些查询所需的时间(在两种情况下).但是请注意，Drupal 编码标准要求在正常修改时使用 db_update、db_delete、db_merge，只有 'allows' db_query 而不是 db_select 操作.

13-05-2019 edit:
Just a side note, I did a few month ago some performance measurement on medium dataset to query down some data via db_select and db_query. The magnitude of the performance of these are: under you run one db_select, you can run two db_query. This also applies to db_update and db_query. The test queried down some columns with a simple SELECT, no JOINS, no fancy dandy stuff, just two condition. Of course, the measurement also contained/included the time which was required to build up these queries (at both case). However please note, the Drupal Coding Standard requires to use db_update, db_delete, db_merge on modification normally, only 'allows' db_query instead of db_select operation.

这篇关于Drupal 7 |使用 db_update 更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！