mysql批量更新的sql语句

mysql单挑更新浪费性能，肯定使用批量更新能够减少其网络交互时间，提高msyql执行性能。

例如有个tag表，需要根据id批量更新num字段，sql语句如下即可:

update tag SET num = CASE id WHEN 4 THEN 1627934 WHEN 5 THEN 1623435 WHEN 6 THEN 1622588 WHEN 7 THEN 1618736 WHEN 70 THEN 415 WHEN 71 THEN 24 WHEN 72 THEN 18 WHEN 74 THEN 45 WHEN 75 THEN 32 WHEN 76 THEN 31 END WHERE id IN ('4','5','6','7','70','71','72','74','75','76');

如果用php，则需要拼成一个字符串，代码如下:

$sql = "update recommend_tag SET total_sub_num = CASE id ";

foreach ($idnumArr as $id => $num) { //idnumArr是一个以id为key，以num为value的键值对

     $sql .= sprintf("WHEN %d THEN %d ", $id, $num);

}

$sql .= $sql .= "END WHERE id IN ('". implode("','", array_keys($idnumArr)."')";