生产环境收集到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.
发表评论
-
(ZZ)Oracle约束的关键字Enable/Disable/Validate/Novalidate
2015-09-16 11:34 7321 组合特性说明 Validate ... -
MySQL的权限
2014-07-09 17:49 519一.权限表 mysql数据 ... -
CentOS5.4安装源码MySQL5.5.38
2014-07-06 15:16 2214安装环境 系统 CentOS 5.4 ,安装系统时顺带安 ... -
(ZZ)MySQL日期时间函数大全
2014-03-21 14:32 668DAYOFWEEK(date) 返回 ... -
(ZZ)使用Percona Xtrabackup对数据库进行部分备份
2014-02-28 14:21 589今天同事问我用Xtrabackup工具怎么对某些表进行备份 ... -
mysql实现Oracle中row_number over partition by
2014-02-25 17:26 1205SELECT t1.empno,t1.sal,t1.dept ... -
根据生日计算年龄
2014-02-24 18:58 811select (year(now())-year('1 ... -
(ZZ)mysql创建定时任务
2014-02-17 19:55 555一、前言 自 MySQL5.1.6起,增加了一个非常有特色 ... -
(ZZ)提高MySQL效率与性能的技巧
2014-01-24 16:26 492今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于 ... -
MySQL 数据类型
2014-01-23 13:25 584MySQL 的数值数据类型可以大致划分为两个类别,一个是整数 ... -
(ZZ)phpMyAdmin设置mysql存储过程及其问题
2014-01-22 21:23 659这几天需要用到数据库,使用的是MySQL,数据库 ... -
(ZZ)MYSQL SQL_NO_CACHE的真正含义
2013-02-17 11:29 1185当我们想用SQL_NO_CACHE来禁止结果缓存时发现结果 ... -
(zz)skip-grant-tables:非常有用的mysql启动参数
2013-02-05 14:20 1011介绍一个非常有用的mysql启动参数—— --skip-gr ... -
前缀索引
2013-02-04 15:51 769应用场景: 数据库里有个地址(address)字段, ... -
mysql常用操作
2013-02-01 10:39 7211:打开mysql数据库 mysql -h主机地址 -u用 ... -
(ZZ)oracle与mysql的批量插入
2013-01-29 16:34 698oracle与mysql的批量插入 oracle 批量插入 ... -
(ZZ)MySQL主从复制
2012-12-12 11:17 747ysql主从复制原理: mysql的主从复制是一个异步复制的过 ... -
MySQL二进制日志备份和恢复
2012-12-11 10:56 917基本概念 定义: 二进 ... -
(ZZ)msyql索引类型
2012-11-27 16:36 874【转】http://hi.baidu.com/mesong1/ ... -
mysql的hibernate配置
2012-05-14 18:35 731<hibernate-configuratio ...
相关推荐
mysql数据库Insert语句后加ONDUPLICATE KEY UPDATE,保证唯性如果在INSERT语句末尾指定了ON DUPLICATE KEY UP
今天听同事介绍oracle到mysql的数据migration,他用了Insert into ..... on duplicate key update ...,我当时就想怎么不用Replace呢,于是回来就仔细查了下,它们果然还是有区别的
代码如下: insert into table(a, b, c) values (1, 2, 3) on duplicate key update c = c + 1;1 update table set c = c + 1 where a = 1; 另外值得一提的是,这个语句知识mysql中,而标准sql语句中是没有的。 ...
主要介绍了mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点,结合实例形式分析了replace into 与 insert into on duplicate key update的功能、基本用法与操作注意事项,需要的朋友可以...
mysql “ON DUPLICATE KEY UPDATE” 语法如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列...
本文介绍一下关于mysql中INSERT INTO… ON DUPLICATE KEY UPDATE用法
在对看看的后台进行排序的时候,遇到了一个像这样的需求,在电影表中有ID(主键自增)和orderby(排序字段) ,假设有十条数据id分别从1-10之间,对应的...下面来看看Mysql的REPLACE和INSERT … ON DUPLICATE KEY UPDATE REP
先看语法INSERT INTO table(id,a,……) values(1,‘xh’,……) ON DUPLICATE KEY Update a=‘value’,……; 举个例子 1,先创建表结构如下 DROP TABLE IF EXISTS `t_ware_duplicate`; CREATE TABLE `t_ware_...
数据2模型根据模型将XML和CSV数据保存到数据库该软件包旨在提供一种高性能,快速便捷的方式来... ON DUPLICATE KEY UPDATE查询: INSERT INTO exampleModel (`col1`,`col2`,`created_at`,`updated_at`) VALUES ('hel
当我插入一条数据时,我要判断(k1,k2)是否已经存在(1条selete),若存在就update,不存在就insert
INSERT语法 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,…)] VALUES ({expr | DEFAULT},…),(…),… [ ON DUPLICATE KEY UPDATE col_name=expr, … ] 或: INSERT [LOW_...
在之前面对这样的问题的时候,我们知道,MySQL提供了ON DUPLICATE KEY UPDATE或者REPLACE INTO来解决。 使用ON DUPLICATE KEY UPDATE 插入数据之前,表中就一条记录,如下图 SQL语句如下,当插入记录时候,与表中...
(1)Insert into ….on duplicate key update….. (2)Insert into (),()…语句 (3)带注释(comment)的create table语句 (4)alter table … change….语句; (5)alter table … modify….语句; (6)添加...
在PostgreSQL / Sqlite中使用INSERT … ON CONFLICT DO UPDATE ,在SqlServer中使用MERGE和INSERT INTO … ON DUPLICATE KEY UPDATE在MySQL中INSERT INTO … ON DUPLICATE KEY UPDATE 。 还支持注入sql命令运行...