MySQL批量更新的四种方法

1

2

replace into `user` (id,age) values (1,'2'),(2,'3'),(3,'4'),(4,'98');

-- > 时间: 0.038s

1

2

insert into `user`(id,age) values (1,'5'),(2,'7'),(3,'2'),(4,'198') on duplicate key update age=values(age)

-- > > 时间: 0.017s

1

2

3

4

5

6

7

8

9

10

11

DROP TABLE if EXISTS tmp;

-- > 时间: 0.016s

create temporary table tmp(id int(4) primary key,age varchar(50));

-- > 时间: 0.01s

insert into tmp values  (1,'13'), (2,'16'),(3,'18'),(4,'18');

-- > 时间: 0.009s

update `user`, tmp set `user`.age=tmp.age where `user`.id=tmp.id;

-- > 时间: 0.022s

1

2

3

4

5

6

7

8

9

10

11

12

13

update `user`

    set age = CASE id

    WHEN 1 THEN '22'

    WHEN 2 THEN '22'

    WHEN 3 THEN '22'

    WHEN 4 THEN '22'

END WHERE id IN(1,2,3,4);

-- > 时间: 0.015s

update (表名)

set (更新字段) = case (被更新字段)

when (被更新字段值) then (更新字段值)...

end where (被更新字段) in((被更新字段值)...)