本文主要是介绍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.
这篇关于insert...on duplicate key update的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!