Thinkphp method to update data in batches

This article mainly introduces the relevant information compiled by Thinkphp on the method of batch updating data. It is very good and has reference value. Friends in need can refer to it

The following editor has listed three types for you. The method of implementing thinkphp batch update data, please forgive me if it is not well written. If you have any opinions, please feel free to put forward, let’s learn and make progress together!

Method 1:

##

//批量修改 data二维数组 field关键字段 参考ci 批量修改函数 传参方式 function batch_update($table_name='',$data=array(),$field=''){ if(!$table_name||!$data||!$field){ return false; }else{ $sql='UPDATE '.$table_name; } $con=array(); $con_sql=array(); $fields=array(); foreach ($data as $key => $value) { $x=0; foreach ($value as $k => $v) { if($k!=$field&&!$con[$x]&&$x==0){ $con[$x]=" set {$k} = (CASE {$field} "; }elseif($k!=$field&&!$con[$x]&&$x>0){ $con[$x]=" {$k} = (CASE {$field} "; } if($k!=$field){ $temp=$value[$field]; $con_sql[$x].= " WHEN '{$temp}' THEN '{$v}' "; $x++; } } $temp=$value[$field]; if(!in_array($temp,$fields)){ $fields[]=$temp; } } $num=count($con)-1; foreach ($con as $key => $value) { foreach ($con_sql as $k => $v) { if($k==$key&&$key<$num){ $sql.=$value.$v.' end),'; }elseif($k==$key&&$key==$num){ $sql.=$value.$v.' end)'; } } } $str=implode(',',$fields); $sql.=" where {$field} in({$str})"; $res=M($table_name)->execute($sql); return $res; } //测试 function test(){ $update_array=array(); for ($i=2; $i <7 ; $i++) { $data=array(); $data['id']=$i; $data['memeber_type']=2; $data['memeber_type_state']=1; $update_array[]=$data; } $res=$this->batch_update('yl_member',$update_array,id); var_dump($res); }

Method Two:

is to loop through the update records one by one. One record is updated once, which has poor performance and can easily cause blocking.

So can batch updates be implemented with one SQL statement? MySQL does not provide a direct method to implement batch updates, but it can be achieved with a few tricks.

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)

The meaning of this sql is to update the display_order field. If id=1, the value of display_order is 3. If id=2, the value of display_order is 4. If id=3, the value of display_order is 5.

is to write the conditional statements together.

The where part here does not affect the execution of the code, but it will improve the efficiency of sql execution. Ensure that the sql statement only executes the number of rows that need to be modified. Only 3 rows of data are updated here, and the where clause ensures that only 3 rows of data are executed.

If you update multiple values, you only need to modify it slightly:

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)

Actual application

$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 .= sprintf("WHEN %d THEN %d ", $id, $ordinal); } $sql .= "END WHERE id IN ($ids)"; echo $sql;

Method 2 is not recommended.

Method three:

/* * @param $saveWhere ：想要更新主键ID数组 * @param $saveData ：想要更新的ID数组所对应的数据 * @param $tableName : 想要更新的表明 * @param $saveWhere : 返回更新成功后的主键ID数组 * */ public function saveAll($saveWhere,&$saveData,$tableName){ if($saveWhere==null||$tableName==null) return false; //获取更新的主键id名称 $key = array_keys($saveWhere)[0]; //获取更新列表的长度 $len = count($saveWhere[$key]); $flag=true; $model = isset($model)?$model:M($tableName); //开启事务处理机制 $model->startTrans(); //记录更新失败ID $error=[]; for($i=0;$i<$len;$i++){ //预处理sql语句 $isRight=$model->where($key.'='.$saveWhere[$key][$i])->save($saveData[$i]); if($isRight==0){ //将更新失败的记录下来 $error[]=$i; $flag=false; } //$flag=$flag&&$isRight; } if($flag ){ //如果都成立就提交 $model->commit(); return $saveWhere; }elseif(count($error)>0&count($error)<$len){ //先将原先的预处理进行回滚 $model->rollback(); for($i=0;$isaveAll($saveWhere,$saveData,$tableName); return $saveWhere; } else{ //如果都更新就回滚 $model->rollback(); return false; } }

Call in the test method:

public function test(){ //要更新的数据表的主键数组 $where['ID']=array(70,73,74,80,83); //ID主键数组对应的待更新数据 $save=array( array('School'=>'DK Univisity01','isExport'=>0), array('School'=>'DK Univisity02','isExport'=>0), array('School'=>'DK Univisity03','isExport'=>0), array('School'=>'DK Univisity04','isExport'=>0), array('School'=>'','isExport'=>0), // array('School'=>' Univisity05','isExport'=>0), ); $f=$this->saveAll($where,$save,'want'); if(count($f['ID'])>0){ //返回更新成功的ID数组 echo "This is success :
"; dump($f); echo 'ok'; }else{ //更新失败操作 echo "This is failed :
"; dump($f); echo 'error'; } }

Related recommendations:

Method of implementing message board based on thinkPHP framework

ThinkPHP implements the attachment upload function

The above is the detailed content of Thinkphp method to update data in batches. For more information, please follow other related articles on the PHP Chinese website!