批量更新sql

1.创建临时表，插入相关字段之后更新
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

2.case 语句更新
UPDATE table
    SET 
    a = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END, 
    b = CASE id 
        WHEN 1 THEN 'x'
        WHEN 2 THEN 'y'
        WHEN 3 THEN 'z'
    END
WHERE id IN (1,2,3)

对于hibenate，可以开启批量处理

spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true spring.jpa.properties.hibernate.batch_versioned_data=true

对于类型一致的实体，也可以显式的操作数据

@Transactional
@Test
public void whenFlushingAfterBatch_ThenClearsMemory() {
    for (int i = 0; i < 10; i++) {
        if (i > 0 && i % BATCH_SIZE == 0) {
            entityManager.flush();
            entityManager.clear();
        }
        School school = createSchool(i);
        entityManager.persist(school);
    }
}