Java通过on duplicate key update实现MySQL的插入或更新

阳光穿透心脏的1/2处 2024-04-01 12:40 23阅读 0赞

**目录**

使用场景

语法

根据主键实现

根据唯一索引实现

语句返回值

对返回值的应用

--------------------

## 使用场景 ##

如果有需求是：对一条或多条数据，查看数据库中是否有已存在的数据（是否已存在这个标准可以自定义），不存在则插入，存在则更新，那么on duplicate key update是很好的实现方式，因为它保证了数据库的原子性，不用担心因为并发操作导致各种数据问题。

## 语法 ##

假设一张表students有5个字段：自增id（主键），姓名，班级，年龄。现在有三条数据：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>name</td> 
   <td>class</td> 
   <td>age</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>name1</td> 
   <td>class1</td> 
   <td>16</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>name2</td> 
   <td>class1</td> 
   <td>17</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>name3</td> 
   <td>class2</td> 
   <td>17</td> 
  </tr> 
 </tbody> 
</table>

insert into students (id,name,class,age)
    values(#{id},#{name},#{class},#{age}) 
    on duplicate key update 
    id=#{id},name=#{name},class=#{class},age=#{age}

注意：更新时只会更新 on duplicate key update 后面的字段

### 根据主键实现 ###

假设代码传入的数据是（3，name4，class2，17），因为插入后主键会冲突，因此这条语句会变为更新，执行后表数据将会变成

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>name</td> 
   <td>class</td> 
   <td>age</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>name1</td> 
   <td>class1</td> 
   <td>16</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>name2</td> 
   <td>class1</td> 
   <td>17</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>name4</td> 
   <td>class2</td> 
   <td>17</td> 
  </tr> 
 </tbody> 
</table>

注意：虽然只更新没插入，但此时表的主键计数已经变为4，如果此时插入一行，新的一行的id会是5。因为这个语句不论是插入还是更新都会把数据的初始化执行一次，例如：主键自增、创建时间默认当前时间、字段默认null等。所以尽量不要用主键实现这个语句。

### 根据唯一索引实现 ###

那么我们把语句改一下，不对主键进行操作

insert into students (name,class,age)
    values(#{name},#{class},#{age}) 
    on duplicate key update 
    name=#{name},class=#{class},age=#{age}

在Java中批量操作时更常用的写法：

insert into students (name,class,age)
    values(#{name},#{class},#{age}) 
    on duplicate key update 
    name=values(name),class=values(class),age=values(age)

此时给表增加一条姓名和班级的唯一索引：

ALTER TABLE students ADD CONSTRAINT uk_name_class UNIQUE KEY(name,class);

那么再传入一条数据(name4,class2,16)，与唯一索引冲突，变为更新，执行后的结果变成

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>name</td> 
   <td>class</td> 
   <td>age</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>name1</td> 
   <td>class1</td> 
   <td>16</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>name2</td> 
   <td>class1</td> 
   <td>17</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>name4</td> 
   <td>class2</td> 
   <td>16</td> 
  </tr> 
 </tbody> 
</table>

针对一些业务场景还可以设置两个唯一索引，此时再加一个姓名和年龄的唯一索引：

ALTER TABLE students ADD CONSTRAINT uk_name_age UNIQUE KEY(name,age);

此时传入一条数据(name4,class3,16)，会触发第二条唯一索引，也会变为更新操作。

## 语句返回值 ##

*  如果执行语句的结果是插入，则返回值是1；如果执行语句的结果是更新，则返回值是2；如果执行语句并没有更新或插入，则返回值是0。
 *  更多细节详见[MySQL5.7官网][MySQL5.7]

### 对返回值的应用 ###

*  如果业务对于数据的准确性要求很高，需要确保本次更新确实生效。可以在单次插入或更新后获取到返回值，校验affectRow >= 0;
 *  对于批量的插入或更新，可以选择先通过唯一索引批量查询将会被更新的条数updateRow，批量执行后校验affectRow = updateRow \* 2 + (list.size() - updateRow);
    
     *  批量的校验一般不推荐，因为涉及到两步操作，所以查询前一定要加锁，批量加锁的风险太大。

[MySQL5.7]: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html