Mysql批量插入数据时如何解决重复问题？

基本用法：on dupdate key update 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。

1，on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。

2，记录已存在时，只会更新on duplicate key update之后指定的字段。

3，如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。

4，特殊用法：参考第五、第六条。

一、构造测试数据

注意里面的唯一键

   

    

     

      

     
     

      

       #创建表
      
     
    

     

      

     
     

      

       drop table if exists tbl_test;
      
     
    

     

      

     
     

      

       create table tbl_test(
      
     
    

     

      

     
     

      

       id int primary key auto_increment,
      
     
    

     

      

     
     

      

       name varchar(30) unique not null,
      
     
    

     

      

     
     

      

       age int comment '年龄',
      
     
    

     

      

     
     

      

       address varchar(50) comment '住址',
      
     
    

     

      

     
     

      

       update_time datetime default current_timestamp on update current_timestamp
      
     
    

     

      

     
     

      

       ) comment '测试表';
      
     
    

     

      

     
     

      
 
      
     
    

     

      

     
     

      

       #插入语句 来源【码农编程进阶笔记】
      
     
    

     

      

     
     

      

       insert into tbl_test(name,age,address) values('huahua',20,'京华市'),('caocao',21,'京海市');
      
     
   
  

效果如下：

二、sql用法介绍

on dupdate key update 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。

如下sql：

   

    

     

      

     
     

      

       insert into tbl_test(id,name,age,address) values(1,'huahua1',201,'京华市1')
      
     
    

     

      

     
     

      
 
      
     
    

     

      

     
     

      

       on duplicate key update
      
     
    

     

      

     
     

      
 
      
     
    

     

      

     
     

      

       age = values(age),
      
     
    

     

      

     
     

      
 
      
     
    

     

      

     
     

      

       address = values(address);
      
     
   
  

从执行结果可以看出，更新了id为1的age，address两个字段，而name字段没有修改生效。由此我们可以得出两个重要结论：

1. on duplicate key update 语句根据主键id来判断当前插入是否已存在。

2. 已存在时，只会更新on duplicate key update之后限定的字段。

三，sql用法介绍2

如下sql：

   

    

     

      

     
     

      

       insert into tbl_test(name,age,address) values('huahua',202,'京华市2')
      
     
    

     

      

     
     

      

       on duplicate key update
      
     
    

     

      

     
     

      

       age = values(age),
      
     
    

     

      

     
     

      

       address = values(address);
      
     
   
  

从执行结果看，这次没有传id，但是age,address字段仍然更新了。

由此可以得出另一个结论：

3. on duplicate key update 语句也可以根据唯一键来判断当前插入的记录是否已存在。

三、sql用法介绍

如下sql：

   

    

     

      

     
     

      

       insert into tbl_test(name,age,address) values('huahua2',202,'京华市2')
      
     
    

     

      

     
     

      

       on duplicate key update
      
     
    

     

      

     
     

      

       age = values(age),
      
     
    

     

      

     
     

      

       address = values(address);
      
     
   
  

这条执行就比较简单了，没有主键或唯一键字段值相同，即判断当前记录不存在，新插入一条。

四、sql用法介绍

如下sql：

   

    

     

      

     
     

      

       insert into tbl_test(id,name,age,address) values(1,'huahua1',202,'京华市2')
      
     
    

     

      

     
     

      

       on duplicate key update
      
     
    

     

      

     
     

      

       name = values(name),
      
     
    

     

      

     
     

      

       age = values(age),
      
     
    

     

      

     
     

      

       address = values(address);
      
     
   
  

从上面可以看出，连唯一键name也被修改了。结论：

4.如果传递了主键，是可以修改唯一键字段内容的。

这里要注意，如果这里的name修改为 caocao,huahua2

会报唯一键冲突的。可以自行尝试。

五，on dupdate key update之后没有用values的情况

分为两种情况：

1，如果为如上面的name = "abc"，则会一直更新为"abc".

2，如果为如上面的name = name ，则name会保持数据库中的值，不会更新。

六，对values使用判断

如下sql

   

    

     

      

     
     

      

       #来源公众号：【码农编程进阶笔记】
      
     
    

     

      

     
     

      

       insert into tbl_test(id,name,age,address) values(1,'huahua1',202,'京华市2')
      
     
    

     

      

     
     

      

       on duplicate key update
      
     
    

     

      

     
     

      

       name = ifnull(values(name),name),
      
     
    

     

      

     
     

      

       age = values(age);
      
     
   
  

达到的效果是，如果传入的name值为null，则不更新。不为null则更新。这里与mybatis配合使用比较好。