在重复的键更新-多列。

When using insert... on duplicate key update, what is the syntax to update multiple columns?

当使用插入……在重复键更新时，更新多个列的语法是什么?

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".

同样，对于最后一部分的“更新”也不确定。

4 个解决方案

#1

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.

我修正了你的报价和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等)，则可以删除这些引号。只要不使用诸如计数、类型或表之类的列名，那么对列名进行反勾是可选的。

In the PHP example, string concatenation is used to clearly separate out the variables.

在PHP示例中，字符串连接用于清晰地分离变量。

#2

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) [,...]

#3

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']

#4

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列。

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:在重复的键上更新现有的PK ID记录。但是如果在任何唯一的键列上复制，它也会进行更新。例如，如果您定义了cname列是唯一的，那么使用cname='Steve'保存已经存在的记录将导致该记录的更新(而不是新的插入)。注意这一点，因为您可能期望DB返回唯一键约束违反的错误，这在这里不会发生。

#1

INSERT INTO table1
  (`col1`, `col2`, `col3`, `col4`)
VALUES
  ('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
  `col2`='val2',
  `col3`='val3', [...]