mysql中 replace into ... 和 insert into ... ON DUPLICATE KEY UPDATE ... 有则更新 无则添加

Wesley13
• 阅读 935

一、insert into ... ON DUPLICATE KEY UPDATE ... 

          如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,

  并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行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;

  如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。

      如想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明,详见MySQL参考文档:13.2.4. INSERT语法


     现在问题来了,如果INSERT多行记录, ON DUPLICATE KEY UPDATE后面字段的值怎么指定?

  要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE,到底他会更新一行记录,还是更新所有需要更新的行。

  这个问题困扰了我很久了,其实使用VALUES()函数一切问题都解决了。

      举个例子,字段a被定义为UNIQUE,并且原数据库表table中已存在记录(2,2,9)和(3,2,1),如果插入记录的a值与原有记录重复,则更新原有记录,否则插入新行:

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

    以上SQL语句的执行,发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突,则执行ON DUPLICATE KEY UPDATE,

  将原有记录(2,2,9)更新成(2,5,9),将(3,2,1)更新成(3,3,1),插入新记录(1,2,3)和(4,8,2)

      注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

二、replace into ... 

replace into t_table(dt, lodge_id, platform_id, price,  online_time) 
values('2018-12-10',111,6,31238232903,null)

  其中 dt, lodge_id, platform_id 为联合唯一索引

 replace语句会删除原有的一条记录, 并且插入一条新的记录来替换原记录。具体如下:

  1. 当replace into 记录只与主键冲突的时候,auto_increment不会增加,它会对与主键冲突的那一条记录进行更新,没有指定的列将会被更新为默认值
  2. 当replace into 记录与主键跟唯一索引同时冲突的时候,auto_increment不会增加
    1. 如果冲突的主键和索引在同一行记录,则replace into只做更新,对于没有指定值的其他列,将会被更新为默认值,
    2. 如果冲突的主键和索引分别对应2行数据,则MySQL将会删除唯一索引的那一行记录,更新对应主键的那一行记录。
  3. 当replace into 记录只与唯一索引进行冲突的时候,auto_increment + 1,再对数据进行更新。
  • 最后我们可以对总结分析下,MySQL对replace into的操作是首先是insert操作,如果insert失败,则对insert失败的这条记录进行update,如果update还是失败,则会进行delete操作之后再update。
  • 具体流程是这样的:insert记录,发现主键冲突,则update这一行,update的时候发现存在唯一键冲突,则delete对应的唯一键的行后再进行update。如果insert成功,auto_increment自然+1了,然后对这条记录进行update,update的时候发现存在唯一键冲突,则delete对应的唯一键的行后再进行update。
点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
3年前
SQL UPDATE 语句:更新表中的记录语法及案例剖析
SQLUPDATE语句UPDATE语句用于更新表中的记录。SQLUPDATE语句UPDATE语句用于更新表中已存在的记录。SQLUPDATE语法UPDATE_table\_name_SET_column1_\_valu
LosAngel LosAngel
3年前
golang实现MySQL数据库事物的提交与回滚
MySQL事务主要用于处理操作量大,复杂度高的数据。在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务用来管理insert,update,delete语句,事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行。一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicit
待兔 待兔
3年前
PostgreSQL语法、连接
1.语法1.插入数据(INSERT语句)在PostgreSQL中,INSERT查询用于在表中插入新行。可以一次插入单行或多行到表中。语法:INSERTINTOTABLE_NAME(column1,column2,column3,...columnN)VALUES(value1,value2,v
Wesley13 Wesley13
3年前
mysql数据库先update后insert死锁分析
先update再insert的并发死锁问题分析。背景“如果库里有对应记录,就更新,没有就插入”很简单的一个逻辑,相信很多人都会遇到。最近看一个工程里实现代码是这样的,mysql数据库走的是默认的事务级别:可重复读。包在一个事务中执行:ifupdate更新结果0thenreturn"成功";el
Stella981 Stella981
3年前
INSERT ... ON DUPLICATE KEY UPDATE产生death lock死锁原理
前言编辑(https://www.oschina.net/action/GoToLink?urlhttp%3A%2F%2F192.168.1.15%3A8080%2Fxwiki%2Fbin%2Fedit%2FMain%2F%25E6%25A9%2599%25E9%25B9%25B0%25E6%258A%2580%25E6%259C%25AF%
Wesley13 Wesley13
3年前
mysql自增id获取
mysql自增id获取使用max函数:selectmax(id)fromtablename 优点:使用方便快捷。缺点:获取的不是真正的自增id,是表中最大的Id,如果有删除数据的话,那么该值和自增id相差比较大。如果有连表数据,有可能导致数据错乱。使用LAST\_INSERT\_ID
Wesley13 Wesley13
3年前
MySQL表介绍
MySQLInnoDB表介绍一、索引组织表在InnoDB引擎中,表都是根据主键顺序存放的。这种存储方式称为索引组织表,在InnoDB引擎中,每张表都有逐渐。如果没有显示定义主键,则引擎会按照以下方式选择或创建主键。(1)、判断表是否有非空唯一索引,如果有,则该字段为主键。如果有多个非空唯一索引,则选择第一个定义的非空索引字段作为
Wesley13 Wesley13
3年前
MySQL记录操作
\TOC\一、概览MySQL数据操作:DML在MySQL管理软件中,可以通过SQL语句中的DML语言来实现数据的操作,包括:1.使用INSERT实现数据的插入2.UPDATE实现数据的更新3.使用DELETE实现数据的删除4.使用SELECT查询数据以及。二、插入数据(insert)
Stella981 Stella981
3年前
Mybatis别名的配置使用
之前,我们在sql映射xml文件中的引用实体类时,需要写上实体类的全类名(包名类名),如下<! 创建用户(Create)   <insert id"addUser" parameterType"me.gacl.domain.User"    insert into users(name,
Wesley13 Wesley13
3年前
sql 优化建议
1、查询语句,尽量避免查询全部,避免写select\fromtable,查哪个写哪个,提高效率; 更新语句,能update具体字段的,不要update所有字段,提升效率。 要尽量避免全表扫描,首先应考虑在where及orderby涉及的列上建立索引 2、一张表中添加索引太少不行,索引的添加可以让查询