insert...on duplicate key update

本文转载自 http://millerrch.iteye.com/blog/1408324

生产环境收集到bug，在小组管理员进行ban／unban小组成员操作时需要更改小组成员的状态（单独的一张表，大概为：小组id，成员id，状态，前两列组成联合主键），往该表插入数据行时数据库提示insert主键冲突。分析原因，可能是服务器抖动（概率很小，而且看日志的重复次数不像是服务器原因），也可能是两个小组的管理员都在执行同一个小组成员的insert操作，一先一后，后操作的执行失败。 

可能的解决方法是，每次插入操作时执行一次查询操作，如果表中没有对应记录就执行insert，否则执行update，但这需要进行两次db操作。Mysq提供了insert...on duplicate key update操作，专门应付此问题。翻译一下Mysql官方文档： http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html  

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect: 

如果你执行insert操作，并且insert操作会导致primary key或者unique列上的值重复，这时，指定ON DUPLICATE KEY UPDATE将会update原来的数据行。举个栗子，如果列a声明为UNIQUE且包含值1，则如下两个语句等效： 

INSERT INTO table (a,b,c) VALUES (1,2,3) 
  ON DUPLICATE KEY UPDATE c=c+1; 

UPDATE table SET c=c+1 WHERE a=1; 

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. 
ON DUPLICATE KEY UPDATE语句可包行多个赋值语句，使用逗号分隔开。 

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. 
使用ON DUPLICATE KEY UPDATE，如果数据行插入成功，则返回的受影响的行数为1；如果更新了老的数据行，返回的首影响行是2。 

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead: 
如果列b同样声明为unique，INSERT语句等同于： 

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; 
If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes. 

如果a=1 OR b=2匹配了多行，只有一行会被更新。一般来说，你应该避免使用ON DUPLICATE KEY 语句操作拥有多个unique列的表。 

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. 
在INSERT...UPDATE语句里，你可以在UPDATE语句中使用VALUES（col_name)函数引用INSERT处的列值。也就是说，UPDATE 处的VALUES（col_name)函数，可以引用原本被插入列的值（如果没有DUPLICATE KEY）。该函数在执行多行插入时尤其有用，且只在INSERT...ON DUPLICATE KEY UPDATE语句中有意义，在其他情况下使用将返回NULL。 
Example: 
例子： 

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) 
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); 

That statement is identical to the following two statements: 
该语句等同于： 
INSERT INTO table (a,b,c) VALUES (1,2,3) 
  ON DUPLICATE KEY UPDATE c=3; 
INSERT INTO table (a,b,c) VALUES (4,5,6) 
  ON DUPLICATE KEY UPDATE c=9; 

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows: 
如果表包含AUTO_INCREMENT列并且INSERT...UPDATE语句插入了新行，LAST_INSERT_ID()函数返回AUTO_INCREMENT值。反之，如果执行语句更新了一行，LAST_INSRET_ID()的返回值无意义。然而，你可以使用LAST_INSERT_ID(expr)函数取到值。假定id列声明为AUTO_INCREMENT，可在UPDATE中如此使用LAST_INSERT_ID()函数： 

INSERT INTO table (a,b,c) VALUES (1,2,3) 
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; 

The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.