MySQL加快批量更新 UPDATE優化

如果是更新為同樣的內容，沒啥難度，直接在where裡面下功夫就好了，大家都懂，我要說的是針對更新內容不一樣的情況

mysql 批量更新如果一條條去更新效率是相當的慢, 迴圈一條一條的更新記錄,一條記錄update一次，這樣效能很差，也很容易造成阻塞。

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

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

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 許可權。

mysql 實現批量可以用點小技巧來實現:

update
yoiurtable
set dingdan =
case
id         
when
1then
3when
2then
4when
3then
5end
where id in (1,2,3)

這句sql 的意思是，更新dingdan 欄位，如果id=1 則dingdan 的值為3，如果id=2 則dingdan 的值為4……

where部分不影響**的執行，但是會提高sql執行的效率。確保sql語句僅執行需要修改的行數，這裡只有3條資料進行更新，而where子句確保只有3行資料執行。

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

update

categories

set dingdan =

case

when

1then

3when

2then

4when

3then

5end

, title

=case

when

1then

'new title 1

'when

2then

'new title 2

'when

3then

'new title 3

'end

where id in (1,2,3)

到這裡，已經完成一條mysql語句更新多條記錄了。

php中用陣列形式賦值批量更新的**：

$display_order = array
(     1 => 4, 
2 => 1, 
3 => 2, 
4 => 3, 
5 => 9, 
6 => 5, 
7 => 8, 
8 => 9); 
$ids = implode(',', array_keys($display_order
)); 
$sql = "update categories set display_order = case id "; 
foreach ($display_order
as$id => $ordinal
)  $sql .= "end where id in ($ids)"; 
echo
$sql;

這個例子，有8條記錄進行更新。**也很容易理解，你學會了嗎

更新 100000條資料的效能就測試結果來看，測試當時使用replace into效能較好。

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

還有一種是我偶爾在寫臨時指令碼的時候用的懶方法，實現起來非常簡單，速度肯定不如插入的方法，但是比起一條一條更新，效果也相當明顯

就是直接在迴圈之前啟動事務，迴圈結束後一起提交，省去每次連線資料庫，解析sql語句等時間。（注意：如果量太大，最好還是要分割一下，比如1000條分割一次，分批次提交）

MySQL加快批量更新 UPDATE優化

相關推薦