MySQL的on duplicate key update关键字的作用

1.问题引入

这个关键字是在看项目代码的时候，看到SQL语句在批量插入数据的时候看到了这个关键字，于是就学习了一下这个关键字的作用。

2.说明

• 作用
  on duplicate key update这个关键字的作用主要是在插入数据的时候如果主键或唯一索引冲突(就是该主键或者该唯一索引的数据已经存在了)的时候执行update后面的更新操作，而不会给我们报一个异常。如果没有冲突的话就直接将这条数据插入。
• 使用样例
  首先先看一下mysql中的原数据,其中name字段是唯一索引：
  
  然后我们执行不加on duplicate key update关键字的sql语句

  insert into admin(name, password, email) 
  values("mcj", "123456", "[email protected]")
  

  可以得到结果插入失败：
  
  当加上这个关键字在执行后：
  • 插入一条数据

    insert into admin(name, password, email) 
    values("mcj", "123456", "[email protected]")
    on duplicate key update
    password = values(password),
    email = values(email)
    

    执行结果为：
    
    此时数据为：
    
    其中需要注意的是on duplicate key update关键字后面的字段赋值是指当主键或者索引冲突时要更新的字段，这边时你需要更新什么字段写什么字段，如果不写的话则不会更新
  • 批量插入

    insert into admin(name, password, email) 
    values
    ("mcj", "234567", "[email protected]"),
    ("mcj123", "234567", "[email protected]"),
    ("mcj456", "123456", "[email protected]")
    on duplicate key update
    password = values(password),
    email = values(email)
    

    执行之后数据为：
    
    可以看出在执行的时候已经存在的数据会进行更新，如果不存在的数据则进行插入。
• 注意点
  1.需要注意的是，如果有两个事务并发执行同样的语句可能会造成死锁，原因是在key已存在时，事务在获取记录时会加上共享锁，然后在进行更改的时候会加上排他锁，如果在事务A加共享锁和排他锁之间这个时间段内有另一个事务也对该数据进行操作加上了共享锁，那么会导致两个事务都加不上排他锁，所以就造成了死锁。
  2.由上面实例可以看出插入的数据的id并不是递增的，这是因为on duplicate key update关键字有一个特性，就是它在更新的情况下id也是增加的，所以会导致数据库中看到的id主键不是递增的。

3.扩展

另外针对批量插入数据时数据重复问题还有另外两种方法，

• 1.insert ignore into：这种是当数据库中没有要插入的这个条数据时会和普通的insert into语句一样，如果已经存在了，则会忽略掉这条插入。例子如下：

insert ignore into admin(name, password, email)
values ("mcj", "234567", "")

运行结果如下：

可以看到受影响的行数为0行，说明其没有插入成功

• 2.replace into:这种是当数据库中已经存在要插入的数据时，会把数据库中已存在的数据进行删除，然后再重新插入，如果不存在的话，则直接插入。例子如下：

replace into admin(name, password, email)
values
("mcj", "123456", "")

运行结果如下：

此时的数据为：

可以看到这条数据已经为最后一条插入的数据了。

4.总结

这三种方法都能解决批量插入数据时，数据重复的问题，不过需要注意的时，这三种方式都会导致主键的"不连续递增"。另外在并发量比较高的时候on duplicate key update关键字可能会出现死锁的情况。