YII实现批量修改, 类似于batchInsert批量插入 [ 2.0 版本 ]

/**
 *  user: fang
 *  date: 2017-09-20
 *  封装一个方法 用于批量修改
 *  参数说明: 表名,要修改的字段数组,数据字典,条件或主键数组,条件或主键字段
 *  params: table_name,columns,rows,conditionArrs,condition_columns
 *  ```php
 *
$connection->createCommand()->batchUpdate('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
],[ 1, 2, 3],'id')->execute();
 *
 *

• UPDATE table_name SET

  columns = 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)
  */
  public function batchUpdate($table, $columns, $rows, $keyPrimaryArrs,$keyPrimaryColumn)
  {

  $sql  = '';
  $sql .= 'UPDATE '.$table.' SET ';
  

$rowsCount = count($rows);
$columnsCount = count($columns);
$columnName ='';
$rowFang = '';

for ( $i = 0; $i < $rowsCount; $i++ ){

    $columnName = isset($columns[$i])?$columns[$i]:$columnName;
    $sql .= $columnName.' = CASE '.$keyPrimaryColumn;

    for ( $j = 0; $j < $rowsCount; $j++ ){
        $rowFang = isset($rows[$j][$i])?$rows[$j][$i]:$rowFang;
        $keyPrimary = isset($keyPrimaryArrs[$j])?$keyPrimaryArrs[$j]:$keyPrimary;
        if (gettype($rowFang)=='integer'){
            $sql .= ' WHEN \''.$keyPrimary.'\' THEN '.$rowFang;
        }else{
            $sql .= ' WHEN \''.$keyPrimary.'\' THEN \''.$rowFang.'\'';
        }
    }

    if ($i === $rowsCount){
        $end = ' END ';
    }else{
        $end = ' END, ';
    }
    $sql .= $end;
}

$conditions = '(\''.implode('\',\'',$keyPrimaryArrs).'\')';

$sql .= ' WHERE '.$keyPrimaryColumn.' IN '.$conditions;
$sql  = str_replace('END,  WHERE','END  WHERE',$sql);

return $this->setSql($sql);

}

> 说明: 将该方法放在 yii\db\Command 类里面,调用的时候
 `\Yii::$app->db->createCommand()->batchUpdate('',[],[],[],'')`