MySQL INSERT INTO .. ON DUPLICATE KEY UPDATE 为多个项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/450682/
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
INSERT INTO .. ON DUPLICATE KEY UPDATE for multiple items
提问by Antti
I want to do something like this
我想做这样的事情
INSERT INTO t (t.a, t.b, t.c)
VALUES ('key1','key2','value')
ON DUPLICATE KEY UPDATE
t.c = 'value';
INSERT INTO t (t.a, t.b, t.c)
VALUES ('key1','key3','value2')
ON DUPLICATE KEY UPDATE
t.c = 'value2';
t.a and t.b are keys. This all works fine but i get an error on the second insert. With phpMyAdmin a query like this works fine but i'm guessing it's running the queries independently as it prints out the results from that query as comments?
ta 和 tb 是键。这一切正常，但我在第二次插入时出错。使用 phpMyAdmin，这样的查询工作正常，但我猜它正在独立运行查询，因为它将该查询的结果作为注释打印出来？
Something like this would be good too but i will need to have different values for each item. I prefer this but i'm not sure how i can change the value on the update for each value.
像这样的东西也很好，但我需要为每个项目设置不同的值。我更喜欢这个，但我不确定如何更改每个值的更新值。
INSERT INTO t (t.a, t.b, t.c)
VALUES ('key1','key2','value'), ('key1','key3','value2')
ON DUPLICATE KEY UPDATE
t.c = ???
The problem is in the question marks, what should i put there so that each insert/update will have the correct value? Obviously if i put a value there all the fields will get that value.
问题在于问号，我应该在那里放什么，以便每个插入/更新都具有正确的值？显然，如果我在那里放一个值，所有字段都将获得该值。
If there is another way of doing an "update if exists, otherwise insert" query on multiple fields with two keys, i'm up for other ideas too. I guess i could run each query separately (like phpMyAdmin?) but it's going to be a lot of queries so i really want to avoid that.
如果有另一种方法可以在具有两个键的多个字段上执行“如果存在则更新，否则插入”查询，我也有其他想法。我想我可以单独运行每个查询（比如 phpMyAdmin？），但是这将是很多查询，所以我真的想避免这种情况。
回答by ???u
Use the VALUES() function
使用 VALUES() 函数
INSERT INTO t (t.a, t.b, t.c)
VALUES ('key1','key2','value'), ('key1','key3','value2')
ON DUPLICATE KEY UPDATE
t.c = VALUES(t.c)
see http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
见http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
回答by Marc
Too low on rep for comment, but I wanted to add a slightly more complex syntax that was inspired by @???u response. To update multiple fields on duplicate key:
代表评论太少，但我想添加一个稍微复杂的语法，其灵感来自@???u 响应。要更新重复键上的多个字段：
INSERT INTO t (t.a, t.b, t.c, t.d)
VALUES ('key1','key2','value','valueb'), ('key1','key3','value2','value2b')
ON DUPLICATE KEY UPDATE
t.c = VALUES(t.c),
t.d = VALUES(t.d)
Hope that helps someone out there looking to perform bulk insert with multiple on duplicate key update. The syntax escaped me.
希望能帮助那些希望在重复密钥更新上执行批量插入的人。语法让我望而却步。
回答by Hunger
After MySQL 8.0.19, you can use askeyword, for example:
MySQL 8.0.19 之后，可以使用as关键字，例如：
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
or
或者
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
ref: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
参考：https: //dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
