本文共 4536 字，大约阅读时间需要 15 分钟。

Mysql 批量修改四种方式效率对比

环境信息

• 数据库版本：mysql-5.7.12
• 运行环境：mac pro
• 开发工具：idea(分配最大内存2g)

测试数据

• 数据量：10万
• 数据生成方式：通过循环生成唯一的id、firstName和lastName

依赖管理

       
            
     
      org.springframework.boot
             
     
      spring-boot-starter
         
        
            
     
      org.springframework.boot
             
     
      spring-boot-starter-web
         
        
            
     
      org.project.lombok
             
     
      lombok
             
     
      1.18.12
         
        
            
     
      org.mybatis.spring.boot
             
     
      mybatis-spring-boot-starter
             
     
      2.1.2
         
        
            
     
      mysql
             
     
      mysql-connector-java
             
     
      runtime
         
        
            
     
      org.springframework.boot
             
     
      spring-boot-starter-test
             
     
      test
             
                 
                      
       
        org.junit.vintage
                       
       
        junit-vintage-engine
                   
              
         
    
   

数据库结构

CREATE TABLE `people` (    `id` bigint(8) NOT NULL AUTO_INCREMENT,    `first_name` varchar(50) NOT NULL DEFAULT '',    `last_name` varchar(50) NOT NULL DEFAULT '',    PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

初始化测试数据

@Testvoid init10wData() {    for (int i = 0; i < 100000; i++) {        People people = new People();        people.setFirstName(UUID.randomUUID().toString());        people.setLastName(UUID.randomUUID().toString());        peopleDAO.insert(people);    }}

批量修改方案

第一种：for循环

       
    
             update people        
                 
      
                       first_name = #{item.firstName,jdbcType=VARCHAR}            
                  
      
                       last_name = #{item.lastName,jdbcType=VARCHAR}            
              
             where id = #{item.id,jdbcType=BIGINT}    
    
   

第二种：case when

       
    
             update people        
                 
                      
                           
        
          when id=#{i.id} then #{i.firstName} 
                        
                   
                  
                      
                           
        
          when id=#{i.id} then #{i.lastName} 
                        
                   
              
             where id in        
     
                  #{item.id,jdbcType=BIGINT}        
         
    
   

第三种：replace into

       
    
             replace into people        (id, first_name, last_name) values        
     
                  (#{item.id}, #{item.firstName}, #{item.lastName})        
         
    
   

第四种：ON DUPLICATE KEY UPDATE

       
    
             insert into people        (id, first_name, last_name) values        
     
                  (#{item.id}, #{item.firstName}, #{item.lastName})        
             ON DUPLICATE KEY UPDATE        id=values(id), first_name=values(first_name), last_name=values(last_name)    
    
   

测试代码

@Testvoid updateBatch() {    List
   
     list = new ArrayList<>();    int loop = 100;    int count = 100000;    Long maxCost = 0L;    Long minCost = Long.valueOf(Integer.MAX_VALUE);    System.out.println("开始");    Long startTime = System.currentTimeMillis();    for (int j = 0; j < count; j++) {        People people = new People();        people.setId(ThreadLocalRandom.current().nextLong(0, 100000));        people.setFirstName(UUID.randomUUID().toString());        people.setLastName(UUID.randomUUID().toString());        list.add(people);    }    for (int i = 0; i < loop; i++) {        Long curStartTime = System.currentTimeMillis();        peopleDAO.updateBatch4(list);        Long curCostTime = System.currentTimeMillis() - curStartTime;        if (maxCost < curCostTime) {            maxCost = curCostTime;        }        if (minCost > curCostTime) {            minCost = curCostTime;        }        System.out.println("耗时-" + (System.currentTimeMillis() - curStartTime));    }    System.out.println("结束");    System.out.println("平均-" + (System.currentTimeMillis() - startTime) / loop + "ms");    System.out.println("最小-" + minCost + "ms");    System.out.println("最大-" + maxCost + "ms");}
   

效率比较

总结

• for循环：效率相当高，但大量数据可能导致SQL阻塞。
• case when：虽然循环次数较多，但每条SQL只执行一次，适合小批量数据。
• replace into：在大批量数据下容易导致数据丢失，且公司通常禁止使用。
• ON DUPLICATE KEY UPDATE：效率最高，但由于可能导致数据丢失，通常不推荐使用。

参考文章：