使用 where 语句批量更新 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35726910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk update mysql with where statement
提问by user3673384
How to update mysql data in bulk ? How to define something like this :
如何批量更新mysql数据？如何定义这样的东西：
UPDATE `table`
WHERE `column1` = somevalues
SET `column2` = othervalues
with somevalues like :
具有一些值，例如：
VALUES
('160009'),
('160010'),
('160011');
and othervalues :
和其他值：
VALUES
('val1'),
('val2'),
('val3');
maybe it's impossible with mysql ? a php script ?
也许 mysql 是不可能的？一个 php 脚本？
回答by Farside
The easiest solution in your case is to use ON DUPLICATE KEY UPDATEconstruction. It works really fast, and does the job in easy way.
在您的情况下，最简单的解决方案是使用ON DUPLICATE KEY UPDATE构造。它的工作速度非常快，并且以简单的方式完成工作。
INSERT into `table` (id, fruit)
VALUES (1, 'apple'), (2, 'orange'), (3, 'peach')
ON DUPLICATE KEY UPDATE fruit = VALUES(fruit);
or to use CASEconstruction
或使用CASE构造
UPDATE table
SET column2 = (CASE column1 WHEN 1 THEN 'val1'
WHEN 2 THEN 'val2'
WHEN 3 THEN 'val3'
END)
WHERE column1 IN(1, 2 ,3);
回答by Davis
If the "bulk" data you have is dynamic and is coming from PHP (you did tag it, after all), then the query would look something like this:
如果您拥有的“批量”数据是动态的并且来自 PHP（毕竟您确实标记了它），那么查询将如下所示：
INSERT INTO `foo` (id, bar)
VALUES
(1, 'pineapple'),
(2, 'asian pear'),
(5, 'peach')
ON DUPLICATE KEY UPDATE bar = VALUES(bar);
and the PHP to generate this from an existing array (assuming the array is of a format like:
以及从现有数组生成它的 PHP（假设数组的格式如下：
$array = (
somevalues_key => othervalues_value
);
) would look something like this (by no means the best (doesn't address escaping or sanitizing the values, for instance), just an quick example):
) 看起来像这样（绝不是最好的（例如，不解决转义或清理值），只是一个简单的例子）：
$pairs = array();
foreach ($array as $key => $value) {
$pairs[] = "($key, '$value')";
}
$query = "INSERT INTO `foo` (id, bar) VALUES " . implode(', ', $pairs) . " ON DUPLICATE KEY UPDATE bar = VALUES(bar)";
回答by Virus
If you have data in array format then try this
如果你有数组格式的数据，那么试试这个
and your query is like "UPDATE table WHERE column1 = ? SET column2 = ?"
你的查询就像 "UPDATE table WHERE column1 = ? SET column2 = ?"
then set it like below
然后像下面这样设置
foreach($data as $key => $value) {
$query->bind_param('ss', $key, $value);
$query->execute();
}
hope it'll work.
希望它会起作用。
Reference from this.
从参考这个。
