Laravel DB批量更新的方法

大家好，又见面了，我是你们的朋友全栈君。

1.1
事例数据：

$multipleData = array(
            array(
                'from_plat_skuid' => 1022016 ,
                'sku_storage_num' => 100
            ),
            array(
                'from_plat_skuid' => 1205019 ,
                'sku_storage_num' => 100
            )
        );

处理方法：

//同时更新多个记录
    public function updateBatch($tableName = "", $multipleData = array()){  
  
        if( $tableName && !empty($multipleData) ) {  
  
            // column or fields to update  
            $updateColumn = array_keys($multipleData[0]);  
            $referenceColumn = $updateColumn[0]; //e.g id  
            unset($updateColumn[0]);  
            $whereIn = "";  
  
            $q = "UPDATE ".$tableName." SET ";   
            foreach ( $updateColumn as $uColumn ) {  
                $q .=  $uColumn." = CASE ";  
  
                foreach( $multipleData as $data ) {  
                    $q .= "WHEN ".$referenceColumn." = ".$data[$referenceColumn]." THEN '".$data[$uColumn]."' ";  
                }  
                $q .= "ELSE ".$uColumn." END, ";  
            }  
            foreach( $multipleData as $data ) {  
                $whereIn .= "'".$data[$referenceColumn]."', ";  
            }  
            $q = rtrim($q, ", ")." WHERE ".$referenceColumn." IN (".  rtrim($whereIn, ', ').")";  
  
            // Update    
            return DB::update(DB::raw($q));  
  
        } else {  
            return false;  
        }  
  
    }

sql事例语句：

UPDATE table_name SET col_1_name = CASE 
WHEN id = '1' THEN 'col_1_value' 
WHEN id = '2' THEN 'col_1_value' 
ELSE col_1_name END, 
col_2_name = CASE 
WHEN id = '1' THEN 'col_2_value' 
WHEN id = '2' THEN 'col_2_value' 
ELSE col_2_name END 
WHERE id IN('1','2')

注：这样做效率会有一个大的提升。

2.1 replace into 批量更新

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个清空大量数据。