php 如何通过一个查询批量更新mysql数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11664684/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-25 01:50:11 来源:igfitidea点击:
How to bulk update mysql data with one query?
提问by Wei Keat
$query = mysql_query("UPDATE a SET fruit = '**apple**' WHERE id = '**1**' ");
$query2 = mysql_query("UPDATE a SET fruit = '**orange**' WHERE id = '**2**' ");
$query3 = mysql_query("UPDATE a SET fruit = '**peach**' WHERE id = '**3**' ");
is there any way to simplify it to one query?
有没有办法将其简化为一个查询？
回答by Yaroslav
I found a following solution:
我找到了以下解决方案：
INSERT into `table` (id,fruit)
VALUES (1,'apple'), (2,'orange'), (3,'peach')
ON DUPLICATE KEY UPDATE fruit = VALUES(fruit);
Id must be unique or primary key. But don't know about performance.
ID 必须是唯一的或主键。但不知道性能如何。
回答by Omesh
Yes you can do it using this query:
是的，您可以使用此查询来做到这一点：
UPDATE a
SET fruit = (CASE id WHEN 1 THEN 'apple'
WHEN 2 THEN 'orange'
WHEN 3 THEN 'peach'
END)
WHERE id IN(1,2 ,3);
回答by skumar
Using IF() function in MySQL this can be achieved as
在 MySQL 中使用 IF() 函数可以实现为
UPDATE a
SET fruit = IF (id = 1, 'apple', IF (id = 2, 'orange', IF (id = 3, 'peach', fruit)));
