mysql?批量更新與批量更新多條記錄的不同值實現(xiàn)方法

在mysql中批量更新我們可能使用update,replace?into來操作，下面詳細介紹mysql批量更新與性能。

批量更新

mysql更新語句很簡單，更新一條數(shù)據的某個字段，一般這樣寫：

UPDATE?mytable?SET?myfield?=?'value'?WHERE?other_field?=?'other_value';

如果更新同一字段為同一個值，mysql也很簡單，修改下where即可：

UPDATE?mytable?SET?myfield?=?'value'?WHERE?other_field?in?('other_values');

這里注意‘other_values’?是一個逗號（，）分隔的字符串，如：1,2,3

那如果更新多條數(shù)據為不同的值，可能很多人會這樣寫：

foreach?($display_order?as?$id?=&gt;?$ordinal)?{?  ????$sql?=?"UPDATE?categories?SET?display_order?=?$ordinal?WHERE?id?=?$id";?  ????mysql_query($sql);?  }

即是循環(huán)一條一條的更新記錄。一條記錄update一次，這樣性能很差，也很容易造成阻塞。

那么能不能一條sql語句實現(xiàn)批量更新呢？mysql并沒有提供直接的方法來實現(xiàn)批量更新，但是可以用點小技巧來實現(xiàn)。

UPDATE?mytable?  ????SET?myfield?=?CASE?id?  ????????WHEN?1?THEN?'value'  ????????WHEN?2?THEN?'value'  ????????WHEN?3?THEN?'value'  ????END  WHERE?id?IN?(1,2,3)

這里使用了case?when?這個小技巧來實現(xiàn)批量更新。

舉個例子：

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

這句sql的意思是，更新display_order?字段，如果id=1?則display_order?的值為3，如果id=2?則?display_order?的值為4，如果id=3?則?display_order?的值為5。

即是將條件語句寫在了一起。

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

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

UPDATE?categories?  ????SET?display_order?=?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)

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

但是要在業(yè)務中運用，需要結合服務端語言，這里以php為例，構造這條mysql語句：

$display_order?=?array(?  ????1?=&gt;?4,?  ????2?=&gt;?1,?  ????3?=&gt;?2,?  ????4?=&gt;?3,?  ????5?=&gt;?9,?  ????6?=&gt;?5,?  ????7?=&gt;?8,?  ????8?=&gt;?9?  );?  $ids?=?implode(',',?array_keys($display_order));?  $sql?=?"UPDATE?categories?SET?display_order?=?CASE?id?";?  foreach?($display_order?as?$id?=&gt;?$ordinal)?{?  ????$sql?.=?sprintf("WHEN?%d?THEN?%d?",?$id,?$ordinal);?  }?  $sql?.=?"END?WHERE?id?IN?($ids)";?  echo?$sql;

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

性能分析

當我使用上萬條記錄利用mysql批量更新，發(fā)現(xiàn)使用最原始的批量update發(fā)現(xiàn)性能很差，將網上看到的總結一下一共有以下三種辦法：

1.批量update，一條記錄update一次，性能很差

update?test_tbl?set?dr='2'?where?id=1;

2.replace?into?或者insert?into?…on?duplicate?key?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);

3.創(chuàng)建臨時表，先更新臨時表，然后從臨時表中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?權限。

下面是上述方法update?100000條數(shù)據的性能測試結果：

逐條update

real????0m15.557s

user????0m1.684s

sys????0m1.372s

replace?into

real????0m1.394s

user????0m0.060s

sys????0m0.012s

insert?into?on?duplicate?key?update

real????0m1.474s

user????0m0.052s

sys????0m0.008s

create?temporary?table?and?update:

real????0m0.643s

user????0m0.064s

sys????0m0.004s

就測試結果來看，測試當時使用replace?into性能較好。

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

replace?into　操作本質是對重復的記錄先delete?后insert，如果更新的字段不全會將缺失的字段置為缺省值

insert?into?則是只update重復記錄，不會改變其它字段。

QUESTION:

Error?Code:?1175.?You?are?using?safe?update?mode?and?you?tried?to?update?a?table?without?a?  WHERE?that?uses?a?KEY?column?To?disable?safe?mode,?toggle?the?option?in?Preferences?-&gt;?SQL?Queries?and?reconnect.