一种方法:使用SET x=CASE..END(任何SQL)
是的,您可以这样做,但我怀疑除非您的查询具有真正的大延迟,否则它不会提高性能。
如果查询在搜索值上建立索引(例如,如果id
是主键),则定位所需的元组非常快,而且在第一次查询后,表将保存在内存中。
因此,在这种情况下,多个UPDATE并不那么糟糕。
另一方面,如果条件需要对整个表进行全表扫描,并且更糟糕的是,表的内存影响很大,那么即使评估更新比简单更新更昂贵(会在内部优化),使用单个复杂查询也会更好。
在后一种情况下,您可以这样做:
UPDATE table SET posX=CASE
WHEN id=id[1] THEN posX[1]
WHEN id=id[2] THEN posX[2]
...
ELSE posX END [, posY = CASE ... END]
WHERE id IN (id[1], id[2], id[3]...);
总成本或多或少由以下公式给出:NUM_QUERIES * (COST_QUERY_SETUP + COST_QUERY_PERFORMANCE)。 这样,您可以通过减少 NUM_QUERIES(从 N 个单独的 id 到 1 个)来降低成本,但是 COST_QUERY_PERFORMANCE 将会增加(MySQL 5.28 大约增加了 3 倍;尚未在 MySQL 8 中测试)。
否则,我建议对 id 进行索引或修改架构。
这是一个 PHP 的示例,其中我假设我们已经有一个需要进行完整表扫描的条件,并且可以将其用作键:
// Multiple update rules
$updates = [
"fldA='01' AND fldB='X'" => [ 'fldC' => 12, 'fldD' => 15 ],
"fldA='02' AND fldB='X'" => [ 'fldC' => 60, 'fldD' => 15 ],
...
];
右手表达式中更新的字段可以是一个或多个,但必须始终相同(在此情况下始终为fldC和fldD)。可以消除此限制,但需要修改算法。
然后我可以通过循环构建单个查询:
$where = [ ];
$set = [ ];
foreach ($updates as $when => $then) {
$where[] = "({$when})";
foreach ($then as $fld => $value) {
if (!array_key_exists($fld, $set)) {
$set[$fld] = [ ];
}
$set[$fld][] = $value;
}
}
$set1 = [ ];
foreach ($set as $fld => $values) {
$set2 = "{$fld} = CASE";
foreach ($values as $i => $value) {
$set2 .= " WHEN {$where[$i]} THEN {$value}";
}
$set2 .= ' END';
$set1[] = $set2;
}
// Single query
$sql = 'UPDATE table SET '
. implode(', ', $set1)
. ' WHERE '
. implode(' OR ', $where);
另一种方法:使用ON DUPLICATE KEY UPDATE(MySQL)
在MySQL中,如果id是主键,则可以更轻松地通过多个INSERT ON DUPLICATE KEY UPDATE
来完成此操作。需要注意的是,不存在的条件(例如“id = 777”但表中没有777)将被插入到表中,如果查询中未指定其他必需列(声明为NOT NULL),可能会导致错误:
INSERT INTO tbl (id, posx, posy, bazinga)
VALUES (id1, posY1, posY1, 'DELETE'),
...
ON DUPLICATE KEY SET posx=VALUES(posx), posy=VALUES(posy);
DELETE FROM tbl WHERE bazinga='DELETE';
以上的“bazinga”技巧允许删除任何可能因其ID不存在而无意中插入的行(在其他情况下,您可能希望插入的行保留下来)。
例如,从一组收集的传感器进行定期更新,但某些传感器可能没有被传输:
INSERT INTO monitor (id, value)
VALUES (sensor1, value1), (sensor2, 'N/A'), ...
ON DUPLICATE KEY UPDATE value=VALUES(value), reading=NOW();
(这是一个人为制造的情况,更合理的做法可能是锁定表格,将所有传感器更新为N/A和NOW(),然后只插入我们确实拥有的值)。
第三种方法:CTE(适用于PostgreSQL,不确定SQLite3是否适用)
从概念上讲,这几乎与INSERT MySQL技巧相同。 如所写,它适用于PostgreSQL 9.6:
WITH updated(id, posX, posY) AS (VALUES
(id1, posX1, posY1),
(id2, posX2, posY2),
...
)
UPDATE myTable
SET
posX = updated.posY,
posY = updated.posY
FROM updated
WHERE (myTable.id = updated.id);