mysql批量更新多条记录的同一个字段为不同值的方法
首先mysql更新数据的某个字段，一般这样写：
UPDATEmytableSETmyfield='value'WHEREother_field='other_value';
也可以这样用in指定要更新的记录：
UPDATEmytableSETmyfield='value'WHEREother_fieldin('other_values');
这里注意‘other_values'是一个逗号（，）分隔的字符串，如：1,2,3
如果更新多条数据而且每条记录要更新的值不同，可能很多人会这样写：
foreach($valuesas$id=>$myvalue){
$sql="UPDATEmytableSETmyfield=$myvalueWHEREid=$id";
mysql_query($sql);
}
即是循环一条一条的更新记录。一条记录update一次，这样性能很差，也很容易造成阻塞。
那么能不能一条sql语句实现批量更新呢？
mysql并没有提供直接的方法来实现批量更新，但是可以用点小技巧来实现。
UPDATEmytable
SETmyfield=CASEid
WHEN1THEN'myvalue1'
WHEN2THEN'myvalue2'
WHEN3THEN'myvalue3'
END
WHEREother_field('other_values')
如果where条件查询出记录的id不在CASE范围内，myfield将被设置为空。
如果更新多个值的话，只需要稍加修改：
UPDATEmytable SETmyfield1=CASEid WHEN1THEN'myvalue11' WHEN2THEN'myvalue12' WHEN3THEN'myvalue13' END, myfield2=CASEid WHEN1THEN'myvalue21' WHEN2THEN'myvalue22' WHEN3THEN'myvalue23' END WHEREidIN(1,2,3)
这里以php为例，构造这两条mysql语句：
1、更新多条单个字段为不同值,mysql模式
$ids_values=array(
1=>11,
2=>22,
3=>33,
4=>44,
5=>55,
6=>66,
7=>77,
8=>88,
);
$ids=implode(',',array_keys($ids_values));
$sql="UPDATEmytableSETmyfield=CASEid";
foreach($ids_valuesas$id=>$myvalue){
$sql.=sprintf("WHEN%dTHEN%d",$id,$myvalue);
}
$sql.="ENDWHEREidIN($ids)";
echo$sql.";<br/>";
输出：
UPDATEmytableSETmyfield=CASEidWHEN1THEN11WHEN2THEN22WHEN3THEN33WHEN4THEN44WHEN5THEN55WHEN6THEN66WHEN7THEN77WHEN8THEN88ENDWHEREidIN(1,2,3,4,5,6,7,8);
2、更新多个字段为不同值,PDO模式
$data=array(array('id'=>1,'myfield1val'=>11,'myfield2val'=>111),array('id'=>2,'myfield1val'=>22,'myfield2val'=>222));
$where_in_ids=implode(',',array_map(function($v){return":id_".$v['id'];},$data));
$update_sql='UPDATEmytableSET';
$params=array();
$update_sql.='myfield1=CASEid';
foreach($dataas$key=>$item){
$update_sql.="WHEN:id_".$key."THEN:myfield1val_".$key."";
$params[":id_".$key]=$item['id'];
$params[":myfield1val_".$key]=$item['myfield1val'];
}
$update_sql.="END";
$update_sql.=',myfield2=CASEid';
foreach($dataas$key=>$item){
$update_sql.="WHEN:id_".$key."THEN:myfield2val_".$key."";
$params[":id_".$key]=$item['id'];
$params[":myfield1va2_".$key]=$item['myfield2val'];
}
$update_sql.="END";
$update_sql.="WHEREidIN(".$where_in_ids.")";
echo$update_sql.";<br/>";
var_dump($params);
输出：
UPDATEmytableSETmyfield1=CASEidWHEN:id_0THEN:myfield1val_0WHEN:id_1THEN:myfield1val_1END,myfield2=CASEidWHEN:id_0THEN:myfield2val_0WHEN:id_1THEN:myfield2val_1ENDWHEREidIN(:id_1,:id_2); array(size=6) ':id_0'=>int1 ':myfield1val_0'=>int11 ':id_1'=>int2 ':myfield1val_1'=>int22 ':myfield1va2_0'=>int111 ':myfield1va2_1'=>int222
另外三种批量更新方式
1.replaceinto批量更新
replaceintomytable(id,myfield)values(1,'value1'),(2,'value2'),(3,'value3');
2.insertinto...onduplicatekeyupdate批量更新
insertintomytable(id,myfield1,myfield2)values(1,'value11','value21'),(2,'value12','value22'),(3,'value13','value23')onduplicatekeyupdatemyfield1=values(myfield2),values(myfield2)+values(id);
3.临时表
DROPTABLEIFEXISTS`tmptable`; createtemporarytabletmptable(idint(4)primarykey,myfieldvarchar(50)); insertintotmptablevalues(1,'value1'),(2,'value2'),(3,'value3'); updatemytable,tmptablesetmytable.myfield=tmptable.myfieldwheremytable.id=tmptable.id;
- 【replaceinto】和【insertinto】更新都依赖于主键或唯一值，并都可能造成新增记录的操作的结构隐患
- 【replaceinto】操作本质是对重复记录先delete然后insert，如果更新的字段不全缺失的字段将被设置成缺省值
- 【insertinto】则只是update重复的记录，更改的字段只能依循公式值
- 【临时表】方式需要用户有temporary表的create权限
- 数量较少时【replaceinto】和【insertinto】性能最好，数量大时【临时表】最好，【CASE】则具有通用型也不具结构隐患
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。