Summary of methods for batch updating data in Thinkphp

The following editor lists three ways to implement thinkphp batch update data. Please forgive me if the writing is not good. If you have any opinions, please feel free to 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.&#39; end),&#39;;
}elseif($k==$key&&$key==$num){
$sql.=$value.$v.&#39; end)&#39;;
}
}
}
$str=implode(&#39;,&#39;,$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[&#39;id&#39;]=$i;
$data[&#39;memeber_type&#39;]=2;
$data[&#39;memeber_type_state&#39;]=1;
$update_array[]=$data;
}
$res=$this->batch_update(&#39;yl_member&#39;,$update_array,id);
var_dump($res);
}

Method 2:

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.&#39;=&#39;.$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;$i<count($error);$i++){
//删除更新失败的ID和Data
unset($saveWhere[$key][$error[$i]]);
unset($saveData[$error[$i]]);
}
//重新将数组下标进行排序
$saveWhere[$key]=array_merge($saveWhere[$key]);
$saveData=array_merge($saveData);
//进行第二次递归更新
$this->saveAll($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 :</br>";
dump($f);
echo &#39;ok&#39;;
}else{
//更新失败操作
echo "This is failed :</br>";
dump($f);
echo &#39;error&#39;;
}
}

The above is a summary of the Thinkphp batch update data method introduced by the editor. I hope it will be useful to everyone. It's helpful. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank you all for your support of the PHP Chinese website!

For more Thinkphp methods to update data in batches, please pay attention to the PHP Chinese website for related articles!