mysql当插入重复时更新的方法:
第一种方法:
示例一:插入多条记录
假设有一个主键为 client_id 的 clients 表,可以使用下面的语句:
- insert into clients
- (client_id,client_name,client_type)
- select supplier_id,supplier_name,'advertising'
- from suppliers
- where not exists(select * from clients where clients.client_id=suppliers.supplier_id);
示例一:插入单条记录
- insert into clients
- (client_id,client_name,client_type)
- select 10345,'ibm','advertising'
- from dual
- where not exists (select * from clients where clients.client_id=10345);
使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。
第二种方法:
insert 中on duplicate key update的使用(本文重点)
如果您指定了on duplicate key update,并且插入行后会导致在一个unique索引或primary key中出现重复值,则执行旧行update。例如,如果列a被定义为unique,并且包含值1,则以下两个语句具有相同的效果:
- mysql>insert into table (a,b,c) values (1,2,3) on duplicate key update c=c 1;
- mysql>update table set c=c 1 where a=1;
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
注释:如果列b也是唯一列,则insert与此update语句相当:
- mysql>update table set c=c 1 where a=1 or b=2 limit 1;
如果a=1 or b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用on duplicate key子句。
您可以在update子句中使用values(col_name)函数从insert...update语句的insert部分引用列值。换句话说,如果没有发生重复关键字冲突,则update子句中的values(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。values()函数只在insert...update语句中有意义,其它时候会返回null。
示例:
- mysql>insert into table (a,b,c) values (1,2,3),(4,5,6)
- ->on duplicate key update c=values(a) values(b);
本语句与以下两个语句作用相同:
- mysql>insert into table (a,b,c) values (1,2,3)
- ->on duplicate key update c=3;
- mysql>insert into table (a,b,c) values (4,5,6)
- ->on duplicate key update c=9;
当您使用on duplicate key update时,delayed选项被忽略。
第三种方法:
replace语句
我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用delete语句删除原先的记录,然后再使用insert插入新的记录。而在mysql中为我们提供了一种新的凯发k8国际娱乐官网入口的解决方案,这就是replace语句。使用replace插入一条记录时,如果不重复,replace就和insert的功能一样,如果有重复记录,replace就使用新记录的值来替换原来的记录值。
使用replace的最大好处就是可以将delete和insert合二为一,形成一个原子操作。这样就可以不必考虑在同时使用delete和insert时添加事务等复杂操作了。
在使用replace时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则replace就和insert完全一样的。
在执行replace后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了delete删除这条记录,然后再记录用insert来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。
replace的语法和insert非常的相似,如下面的replace语句是插入或更新一条记录。
replace into users (id,name,age) values(123, '赵本山', 50);
插入多条记录:
replace into users(id, name, age)
- values(123, '赵本山', 50), (134,'mary',15);
- replace也可以使用set语句
- replace into users set id = 123, name = '赵本山', age = 50;
上面曾提到replace可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,replace将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。
create table table1(a int not null unique,b int not null unique,c int not null unique);
假设table1中已经有了3条记录
a b c
1 1 1
2 2 2
3 3 3
下面我们使用replace语句向table1中插入一条记录。
replace into table1(a, b, c) values(1,2,3);
返回的结果如下
query ok, 4 rows affected (0.00 sec)
在table1中的记录如下
a b c
1 2 3
相关推荐
今天听同事介绍oracle到mysql的数据migration,他用了insert into ..... on duplicate key update ...,我当时就想怎么不用replace呢,于是回来就仔细查了下,它们果然还是有区别的
本文实例讲述了mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点。分享给大家供大家参考,具体如下: replace into和insert into on duplicate key update都是为了解决我们平时的一个...
代码如下: 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中insert into… on duplicate key update用法
先看语法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_...
例如,如果列 a 为 主键 或 拥有unique索引,并且包含值1,则以下两个语句具有相同的效果: 代码如下:insert into table (a,c) values (1,3) on duplicate key update c=c 1;update table set c=c 1 w
当我插入一条数据时,我要判断(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_...
疫情期间在家工作时,同事使用了 insert into on duplicate key update 语句进行插入去重,但是在测试过程中发现了死锁现象: error 1213 (40001): deadlock found when trying to get lock; try restarting ...
在postgresql / sqlite中使用insert … on conflict do update ,在sqlserver中使用merge和insert into … on duplicate key update在mysql中insert into … on duplicate key update 。 还支持注入sql命令运行...
replace into和insert into on duplicate key 区别 replace的用法 当不冲突时相当于insert,其余列默认值 当key冲突时,自增列更新,replace冲突列,其余列默认值 com_replace会加1 innodb_rows_updated会加1 ...
insert into tbl (columna,columnb,columnc) values (1,2,3) on duplicate key update columna=if(columnb>0,1,columna) 更新多个字段: insert into tbl (columna,columnb,columnc) values (1,2,3) on duplicate ...
代码如下:insert table (auto_id, auto_name) values (1, ‘yourname’) on duplicate key update auto_name=’yourname’on duplicate key update的使用 如果您指定了on duplicate key update,并且插入行后会导致...
(1)insert into ….on duplicate key update….. (2)insert into (),()…语句 (3)带注释(comment)的create table语句 (4)alter table … change….语句; (5)alter table … modify….语句; (6)添加...
') on duplicate key update `data` = values(`data`); insert into `authitem` (`name`, `type`, `description`, `bizrule`, `data`) values('d2tasks.ttsktask.create','0','d2tasks.ttsktask module create',null...
如果我们希望插入一条新记录(insert),但如果记录已经存在,就更新该记录,此时,可以使用”insert into … on duplicate key update …”语句: 情景示例:这张表存了用户历史充值金额,如果第一次充值就新增一...