使用where语句批量更新mysql

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脚本？

3 个解决方案

#1

The easiest solution in your case is to use ON DUPLICATE KEY UPDATE construction. 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 CASE construction

或使用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);

#2

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

#3

If you have data in array format then try this

如果你有数据格式的数据，那么试试这个

and your query is like "UPDATE table WHERE column1 = ? SET column2 = ?"

你的查询就像“UPDATE表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.

参考这个。

#1