一、SQL语句
1、批量更新一个字段：
UPDATE `user`SET `name` = CASE idWHEN 1 THEN '张三'WHEN 2 THEN '李四'WHEN 3 THEN '王五'ENDWHERE id IN (1,2,3)
这句sql 的意思是，更新user表的name字段，如果id=1 则name的值为张三，如果id=2 则name的值为李四
2、批量更新多个字段：
UPDATE `user`SET`name` = CASE idWHEN 1 THEN '张三'WHEN 2 THEN '李四'WHEN 3 THEN '王五'END,age = CASE idWHEN 1 THEN 16WHEN 2 THEN 17WHEN 3 THEN 18ENDWHERE id IN (1,2,3)
二、案例
//@todo 去除表中电话和传真的 '-'//表的最小ID是1，最大ID是121329;fax和phone都是varchar类型//每次查询100条数据，拼接sql语句批量更新public function updeAction(){set_time_limit(0);$mGather = new ClientGatherModel();//$mGather->setDb('gather'); //读线上gather库[区分线上和线下数据库]$cnt = 0;$minId = 1;$maxId = 121329;//每次最多插入100条数据，程序拼接for ($x=$minId; $x<=$maxId; $x+=100){$littleMax = $x+99;$querySql = "select * from crm_client_gather where id>=$x and id <=$littleMax order by id asc";$result = $mGather->query($querySql);$data = [];$sql = "";if($result){foreach ($result as $k => $v){$fax = $v['fax'] ? $v['fax'] : '';$phone = $v['phone'] ? $v['phone'] : '';$id = $v['id'];if($fax || $phone ){if($fax && strstr($fax, '-')){$fax = substr($fax,strpos($fax,'-')+1); //截取第一个 - 后面的内容}if($phone && strstr($phone, '-')){$phone = substr($phone,strpos($phone,'-')+1); //截取第一个 - 后面的内容}}$data[] = ['id'=>$id,'fax'=>$fax,'phone'=>$phone];}if($data){$sql .= " UPDATE crm_client_gather SET fax = CASE id ";$faxArr = array_column($data, 'fax', 'id');foreach ($faxArr as $k => $v){$sql .= " WHEN {$k} THEN '{$v}' ";}$sql .= " END, ";$phoneArr = array_column($data, 'phone','id');$sql .= " phone = CASE id ";foreach ( $phoneArr as $k => $v ){$sql .= " WHEN {$k} THEN '{$v}' ";}$sql .= ' END ';$idArr = array_column($data, 'id');$sql .= " WHERE id IN ( ";foreach ($idArr as $k => $v){if($idArr[count($idArr)-1] == $v ){$sql .= $v;}else{$sql .= $v.',';}}$sql .=")";$mGather->query($sql);if( $littleMax > $maxId ){$biggerId = $maxId;}else{$biggerId = $littleMax;}echo "ID:[{$x}]-[$biggerId] 更新了 [".count($data)."]条数据 ";//打印进度}}}}
