laravel数据批量操作

1、批量更新
使用case when 拼sql

$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;
DB::update(DB::raw($sql));

eg:

public static function saveFkUserMsg2($iPostIds){
        $aStatus = Config::get('resume.resume_new_status');
        $oResumes = Vpostresume::whereNull('deleted_at')->whereIn('id',$iPostIds)
                                ->select('id','medlive_id','job_id', 'title', 'job_province', 'job_city', 'status')
                                    ->get();
        $pids = array();
        foreach($oResumes as $v){
            $pids[$v->id] = $v;
        }
        $pids2 = self::where('type', 1)->lists('post_id');
        $pids3 = array();
        $aInfos = array();
        $aInfos2 = array();
        for($i = 0; $i < count($iPostIds); $i++){
            $aInfo = array(
                'job_id' =>$pids[$iPostIds[$i]]->job_id,
                'title'=>$pids[$iPostIds[$i]]->title,
                'province'=>$pids[$iPostIds[$i]]->job_province,
                'city'=>$pids[$iPostIds[$i]]->job_city,
                'status'=>$aStatus[$pids[$iPostIds[$i]]->status]
            );
            if(in_array($iPostIds[$i], $pids2)){
                $pids3[] = $iPostIds[$i];
                $aInfos[$iPostIds[$i]] = json_encode($aInfo);
                //当每个字段值不同时
//              $aInfos[] = array(
//                  'post_id' => $iPostIds[$i],
//                  'msg_str' =>  json_encode($aInfo),
//                  'is_read' => 0,
//                  'updated_at' => date('Y-m-d H:i:s')
//              );
            }else{
                $aInfos2[] = array(
                    'medlive_id' => $pids[$iPostIds[$i]]->medlive_id,
                    'post_id' => $iPostIds[$i],
                    'type' => 1,
                    'msg_str' => json_encode($aInfo),
                    'is_read' => 0,
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s')
                );
            }
        }
        if(count($pids3)){//批量更新
        //当每个字段值不同时
//          $keys = array_keys($aInfos[0]);
//          $reference = $keys[0];
//          unset($keys[0]);
//          $sql = "UPDATE `ad_user_msg_log` SET ";
//          foreach($keys as $val){
//              $sql .= "`".$val."` = CASE `".$reference."` ";
//              if($val=="is_read"){
//                  foreach($aInfos as $v){
//                      $sql .= "WHEN ".$v[$reference]." THEN ".$v[$val]." ";
//                  }
//              }else{
//                  foreach($aInfos as $v){
//                      $sql .= "WHEN ".$v[$reference]." THEN '".$v[$val]."' ";
//                  }   
//              }
//              $sql .= "END, ";
//          }
//          $sql = rtrim($sql, ", ");


            $sql = "UPDATE ad_user_msg_log SET msg_str = CASE post_id ";
            foreach($aInfos as $k => $v){
                $sql .= sprintf("WHEN %d THEN '%s' ", $k, $v);
            }
            $sql .= "END, is_read = 0, updated_at = '".date('Y-m-d H:i:s')."'";
            $sql .= " WHERE type = 1 AND post_id IN (".implode(',', $pids3).");";
            DB::update($sql);//返回影响行数
        }
        if(count($aInfos2)){//批量添加
            self::insert($aInfos2);
        }
        return true;
    }

参照网址：
http://www.cnblogs.com/bruceleeliya/p/3310137.html

2、数据库操作三种方式
参照网址：
http://blog.csdn.net/zls986992484/article/details/52824962

3、效率

//参数 $users数组
$users = array (
    array (
      'code' => string '18971531588',
      'name' => string '大大',
      'manager_first' => string '北京',
      'manager_sec' => string '一线',
      'email' => string 'dada.wei@163.com',
      'hospital' => string '',
      'subject' => string '',
      'level' => string '',
      'province' => string '',
      'city' => string '',
    ),
    array (
      'code' => string 'A-1315-0656173',
      'name' => string '发发',
      'manager_first' => string '湖南',
      'manager_sec' => string '一线',
      'email' => string 'fafa2df@qq.com',
      'hospital' => string '协和医院',
      'subject' => string '内科',
      'level' => string '高级',
      'province' => string '湖南',
      'city' => string '长沙',
    )
);

（1）单次操作

$s = microtime();
foreach($users as $user){
        $oUser = User::where('code',$user['code'])->first();
        if($oUser){
            $oUser->update($user);
        }else{
            DB::table('users')->insert($user);
        }
}
$e = microtime();
echo $e-$s;die;

结果：0.25ms

（2）批量操作

$s = microtime();
$codes = array();
    $codes2 = array();
    $users1 = array();
    $users2 = array();
    $ucodes = User::lists('code');
    $keys = array_keys($users[0]);//dd($keys);
    array_shift($keys);
    foreach($users as $user){
        if(!in_array($user['code'], $ucodes)){
            $codes[]= $user['code'];
            $users1[]= $user;
        }else{
            $codes2[]= $user['code'];
            $users2[]= $user;
        }
    }
    if(count($codes)){
        DB::table('users')->insert($users1);
    }
    if(count($codes2)){
        $sql = 'update users set ';
        foreach($keys as $k){
        $sql .= '`'.$k.'` = case `code` ';
            foreach($users2 as $key => $user){
                $sql .= 'when \''.$user['code'].'\' then \''.$user[$k].'\' ';
            }
            $sql .= 'end, ';
    }
    $sql = rtrim($sql, ', ');
    $codes2 = implode(',',$codes2);
    $codes2 = preg_replace('/(\w+),(.+)/', '\'${1}\',\'$2\'', $codes2);
    $sql .= ' where `code` in ('.$codes2.')';
    DB::update($sql);
}
$e = microtime();
echo $e-$s;die;

结果：0.152ms