MySQL批量更新的四種方式總結(jié)

更新時間：2023年01月12日 15:11:46 作者：凌隕心

最近需要批量更新大量數(shù)據(jù),習慣了寫sql,所以還是用sql來實現(xiàn),下面這篇文章主要給大家總結(jié)介紹了關于MySQL批量更新的四種方式,需要的朋友可以參考下

直接update的弊端

最近做一個需求，更新3w條數(shù)據(jù)，一個一個update去更新的，結(jié)果花了80分鐘，這樣性能上很差，也容易阻塞，所以就找了一些MySQL批量更新的方式，在此記錄一下

方法一；replace into

這種更新會將其它字段更新為默認值，因為它是先將重復記錄刪掉再更新，謹慎使用

replace into `user` (id,age) values (1,'2'),(2,'3'),(3,'4'),(4,'98');
-- > 時間: 0.038s

方法二：insert into [table] values… on duplicate key update

這種方式應該也是刪掉記錄，再更新，但是保存的原來的其它字段數(shù)據(jù)，所以其它字段不會改變

insert into `user`(id,age) values (1,'5'),(2,'7'),(3,'2'),(4,'198') on duplicate key update age=values(age)
-- > > 時間: 0.017s

方法三：創(chuàng)建臨時表

創(chuàng)建臨時表，將更新數(shù)據(jù)插入臨時表，再執(zhí)行更新，需要有建表權(quán)限

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

方法四：使用MySQL自帶批量更新語句

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((被更新字段值)...)

舉例說明：set age = CASE id WHEN 1 THEN ‘22’

簡單來說就是以id為查詢條件，當id=1時更新age=22，where語句提高了sql的執(zhí)行效率，先過濾需要修改的記錄然后再更新。