php 关于重复密钥更新 - 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8991593/
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
On Duplicate Key Update - Multiple Columns
提问by Lucy Weatherford
When using insert... on duplicate key update, what is the syntax to update multiple columns?
在重复键更新上使用insert... 时，更新多列的语法是什么？
INSERT INTO table1 (col1, col2, col3, col4) VALUES ('$val1', ‘$val2', ‘$val3', ‘$val4')
ON DUPLICATE KEY UPDATE col2=‘$val2', col3=‘$val3', col4=‘$val4' // <-- not sure
Update: I am using this within PHP. Since this is a syntax question, it very relevant.
更新：我在 PHP 中使用它。由于这是一个语法问题，因此非常相关。
$result = mysql_query("INSERT INTO table1 (col1, col2, col3, col4)
VALUES ('$val1', ‘$val2', ‘$val3', ‘$val4')
ON DUPLICATE KEY UPDATE (col2=‘$val2', col3=‘$val3', col4=‘$val4')")
Again, not sure about this last part with the "Update".
同样，不确定“更新”的最后一部分。
回答by sathia
INSERT INTO table1
(`col1`, `col2`, `col3`, `col4`)
VALUES
('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
`col2`='val2',
`col3`='val3', [...]
I fixed your quotes and tickmarks.
我修正了你的报价和刻度线。
Edit:
编辑：
In PHP:
在 PHP 中：
$result = mysql_query("
INSERT INTO table1
(col1, col2, col3, col4)
VALUES
('" . $val1 . "', '" . $val2 . "', '" . $val3 . "', '" . $val4 . "')
ON DUPLICATE KEY UPDATE
col2='" . $val2 . "',
col3='" . $val3 . "',
col4='" . $val4 . "'"
);
Note that the values are surrounded by single quotation marks '. If the values are a number type (INT, FLOAT, etc) you can drop those quotation marks. Backticks are optional around the column names as long as you are not using column names like count, type, or table.
请注意，这些值用单引号括起来'。如果值是数字类型（INT、FLOAT 等），您可以去掉这些引号。反引号周围的列名选择的，只要你不使用列名状count，type或table。
In the PHP example, string concatenation is used to clearly separate out the variables.
在 PHP 示例中，字符串连接用于清楚地分离变量。
回答by Wrikken
Well, this is old. But of course you only need to provide a value once, there's no reason to add it a second time in the query (which comes in handy for multiple inserts, or prepared statements):
嗯，这是旧的。但是当然你只需要提供一个值一次，没有理由在查询中第二次添加它（这对于多次插入或准备好的语句很方便）：
INSERT INTO table1
(col1, col2, col3, col4)
VALUES
('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
col2=VALUES(col2),
col3=VALUES(col3) [,...]
Which has as advantage it will still work for a multiple insert statement:
它具有优势，它仍然适用于多个插入语句：
INSERT INTO table1
(col1, col2, col3, col4)
VALUES
('val1', 'val2', 'val3', 'val4'),
('val5', 'val6', 'val7', 'val8'),
('val9', 'val10', 'val11', 'val12')
ON DUPLICATE KEY UPDATE
col2=VALUES(col2),
col3=VALUES(col3) [,...]
回答by Wojciech Jasiński
Your query seems to be correct. Here is my example of this type of query:
您的查询似乎是正确的。这是我的此类查询示例：
INSERT INTO Stat (id, month, year, views, redirects, onList, onMap, emails) VALUE ("' . $Id . '","' . $month . '","' . $year . '",0,0,"' . $data['onList'] . '","' . $data['onMap'] . '",0) ON DUPLICATE KEY UPDATE onList=onList+' . $data['onList'] . ', onMap=onMap+' . $data['onMap']
回答by sbrbot
For the sake of clear syntax there's another syntax form;
为了清晰的语法，还有另一种语法形式；
INSERT INTO `table1` SET `id`=$id,
`col2`='$col2',
`col3`='$col3'[, ...]
ON DUPLICATE KEY UPDATE `col2`='$col2',
`col4`='$col4'[, ...]
Example;
例子;
INSERT INTO customers SET cid=10,
createdon=NOW(),
createdby='user',
cname='Steve'
ON DUPLICATE KEY UPDATE modifiedon=NOW(),
modifiedby='user',
cname='Steve';
If there does not exist a customer with ID=10 in database it will be created and columns cid, createdon, createdby, cname will be set. If it does exist, then it will be updated and columns modifiedon, modifiedbym, cname will be updated.
如果数据库中不存在 ID=10 的客户，则将创建该客户，并设置列 cid、createdon、createdby、cname。如果它确实存在，那么它将被更新，并且列 modifiedon, modifiedbym, cname 将被更新。
NOTE#1: IF you put for primary key cid=0 here, it will fire AUTO_INCREMENT (of course, if pk column is defined as AUTO_INCREMENT) and a record will be inserted!
注意#1：如果在这里设置主键 cid=0，它将触发 AUTO_INCREMENT（当然，如果 pk 列定义为 AUTO_INCREMENT）并插入一条记录！
NOTE#2: ON DUPLICATE KEY UPDATE makes update for existing PK ID record. But also it makes update if DUPLICATE is made on any UNIQUE KEY column. For example, if you defined that cname column is UNIQUE, then saving record with cname='Steve' that already exist will result in UPDATE of that record (not new INSERT). Take care about this because you may expect that DB returns error for UNIQUE KEY constraint violation which will not happened here.
注意#2：ON DUPLICATE KEY UPDATE 更新现有的 PK ID 记录。但如果在任何 UNIQUE KEY 列上进行了 DUPLICATE，它也会进行更新。例如，如果您将 cname 列定义为 UNIQUE，那么保存已存在的 cname='Steve' 记录将导致该记录的 UPDATE（不是新的 INSERT）。请注意这一点，因为您可能期望 DB 因违反 UNIQUE KEY 约束而返回错误，而这不会在这里发生。
