群友：10萬(wàn)條數(shù)據(jù)批量更新怎么做？

10 萬(wàn)條數(shù)據(jù)批量插入相信大家都不陌生《MySQL 插入 100 行數(shù)據(jù)，用時(shí)最短的獲得 Offer！》，但是如果 10 萬(wàn)條數(shù)據(jù)進(jìn)行批量更新該怎么操作呢？昨天群友就提了這個(gè)問(wèn)題，我們一起來(lái)看看具體可以怎么做。

mysql 批量更新如果一條條去更新效率是相當(dāng)?shù)穆? 循環(huán)一條一條的更新記錄,一條記錄update一次，這樣性能很差，也很容易造成阻塞。

mysql 批量更新共有以下四種辦法

1、.replace into 批量更新

replace?into?test_tbl?(id,dr)?values?(1,'2'),(2,'3'),...(x,'y');

2、insert into ...on duplicate key update批量更新

insert?into?test_tbl?(id,dr)?values?(1,'2'),(2,'3'),...(x,'y')?on?duplicate?key?update?dr=values(dr);

3.創(chuàng)建臨時(shí)表，先更新臨時(shí)表，然后從臨時(shí)表中update

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;

注意：這種方法需要用戶有 temporary 表的 create 權(quán)限。

4、使用 mysql 自帶的語(yǔ)句構(gòu)建批量更新

mysql 實(shí)現(xiàn)批量 可以用點(diǎn)小技巧來(lái)實(shí)現(xiàn)：

UPDATE?tableName
????SET?orderId?=?CASE?id?
????????WHEN?1?THEN?3?
????????WHEN?2?THEN?4?
????????WHEN?3?THEN?5?
????END
WHERE?id?IN?(1,2,3)

這句 sql 的意思是，更新 orderId 字段，如果id = 1 則 orderId 的值為 3，如果 id = 2 則 orderId 的值為 4……

where 部分不影響代碼的執(zhí)行，但是會(huì)提高 sql 執(zhí)行的效率。確保 sql 語(yǔ)句僅執(zhí)行需要修改的行數(shù)，這里只有3條數(shù)據(jù)進(jìn)行更新，而 where 子句確保只有 3 行數(shù)據(jù)執(zhí)行。

如果更新多個(gè)值的話，只需要稍加修改：

UPDATE?categories?
????SET?orderId?=?CASE?id?
????????WHEN?1?THEN?3?
????????WHEN?2?THEN?4?
????????WHEN?3?THEN?5?
????END,?
????title?=?CASE?id?
????????WHEN?1?THEN?'New?Title?1'
????????WHEN?2?THEN?'New?Title?2'
????????WHEN?3?THEN?'New?Title?3'
????END
WHERE?id?IN?(1,2,3)

到這里，已經(jīng)完成一條 mysql 更新多條記錄了。

根據(jù)我的測(cè)試，更新 100000 數(shù)據(jù)的性能就測(cè)試結(jié)果來(lái)看，測(cè)試當(dāng)時(shí)使用replace into性能較好。

replace into 和 insert into on duplicate key update 不同在于：

replace into 操作本質(zhì)是對(duì)重復(fù)的記錄先delete 后insert，如果更新的字段不全會(huì)將缺失的字段置為缺省值，用這個(gè)要悠著點(diǎn)否則不小心清空大量數(shù)據(jù)可不是鬧著玩的。

insert into 則是只update重復(fù)記錄，不會(huì)改變其它字段。