Yii批量操作类

/**
 * 数据库批量操作
 * Class DbBatchHelper
 * @package common\helpers
 */
class DbBatchHelper
{
   /**
    * 批量插入
    * @param string $tableName 表名
    * @param array $fields 要插入的字段  ['name', 'gender', 'age']
    * @param array $data   要插入的字段值 [['name1', 1, 25], ['name2', 2, 30]]
    * @return int
    */
   public static function insertBatch(string $tableName, array $fields, array $data){
      try {
         // 成功返回记录数
         return \Yii::$app->db->createCommand()->batchInsert($tableName, $fields, $data)->execute();
      } catch (\Exception $e) {
//       print_r($e->getMessage()); exit;
         return false;
      }
   }

   /**
    * 批量更新 - 自定义
    * @param string $tableName 表名
    * @param array $multipleData  [
                           ['id' => 1, 'name' => 'name_1'],
                           ['id' => 2, 'name' => 'name_2'],
                        ]
    *              参数第一位id为主键（即：更新条件 where id = ...）
    * @return bool
    */
   public static function updateBatch(string $tableName, $multipleData = [])
   {
      try {
         if (empty($multipleData)) {
            throw new \Exception("数据不能为空");
         }
         $firstRow  = current($multipleData);
         $updateColumn = array_keys($firstRow);
         // 默认以id为条件更新，如果没有ID则以第一个字段为条件
         $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
         unset($updateColumn[0]);
         // 拼接sql语句
         $updateSql = "UPDATE " . $tableName . " SET ";
         $sets      = [];
         foreach ($updateColumn as $uColumn) {
            $setSql = "`" . $uColumn . "` = CASE ";
            foreach ($multipleData as $data) {
               $setSql .= "WHEN `" . $referenceColumn . "` = ". $data[$referenceColumn] ." THEN '". $data[$uColumn] ."' ";
            }
            $setSql .= "ELSE `" . $uColumn . "` END ";
            $sets[] = $setSql;
         }
         $updateSql .= implode(', ', $sets);
         $referenceValues = [];
         foreach ($multipleData as $k => $v){
            $referenceValues[] = $v[$referenceColumn];
         }
         $whereIn = $referenceValues;
         $whereIn = implode(',', $whereIn);
         $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
         // 返回成功记录数（如果没有要更新的数据，返回0）
         \Yii::$app->getDb()->createCommand($updateSql)->execute();
         return true;
      } catch (\Exception $e) {
//       return $e->getMessage();
         return false;
      }
   }

   /**
    * 批量更新 - 自定义
    * @param string $tableName 表名
    * @param array $multipleData  [
                           ['id' => 1, 'name' => 'name_1'],
                           ['id' => 2, 'name' => 'name_2'],
                        ]
    *              参数第一位id为主键（即：更新条件 where id = ...）
    * @param bool $upgrade 标识是否使用优化版本（批量更新字段不一样时使用 true）
    * @return bool
    */
   public static function updateBatchV2(string $tableName, $multipleData = [], $upgrade = false)
   {
      try {
         if (empty($multipleData)) {
            throw new \Exception("数据不能为空");
         }
         $firstRow  = current($multipleData);
         $updateColumn = array_keys($firstRow);
         // 优化 2019-11-27
         if($upgrade){
            $columns = count($updateColumn);
            foreach ($multipleData as $vdata){
               $_columns = count($vdata);
               if($_columns > $columns){
//                $updateColumn = array_keys($vdata);
                  // 兼容更新字段不一样的情况
//                $updateColumn = array_merge($updateColumn, array_keys($vdata)); // 合并一维数组不会去重
                  $updateColumn = array_keys(array_flip($updateColumn) + array_flip(array_keys($vdata))); // 合并一维数组去重
                  $columns = count($updateColumn);
               }
               continue;
            }
         }
         // 默认以id为条件更新，如果没有ID则以第一个字段为条件
         $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
         unset($updateColumn[0]);
         // 拼接sql语句
         $updateSql = "UPDATE " . $tableName . " SET ";
         $sets      = [];
         if($upgrade){
            // 更新字段不一样处理
            foreach ($updateColumn as $uColumn) {
               $setSql = "`" . $uColumn . "` = CASE ";
               $tmpSql = '';
               foreach ($multipleData as $data) {
                  if(!isset($data[$uColumn])) continue;
                  $_sql = "WHEN `" . $referenceColumn . "` = ". $data[$referenceColumn] ." THEN '". $data[$uColumn] ."' ";
                  $setSql .= $_sql;
                  $tmpSql .= $_sql;
               }
               $setSql .= "ELSE `" . $uColumn . "` END ";
               if($tmpSql) $sets[] = $setSql;
            }
         }else{
            foreach ($updateColumn as $uColumn) {
               $setSql = "`" . $uColumn . "` = CASE ";
               foreach ($multipleData as $data) {
                  $setSql .= "WHEN `" . $referenceColumn . "` = ". $data[$referenceColumn] ." THEN '". $data[$uColumn] ."' ";
               }
               $setSql .= "ELSE `" . $uColumn . "` END ";
               $sets[] = $setSql;
            }
         }
         $updateSql .= implode(', ', $sets);
         $referenceValues = [];
         foreach ($multipleData as $k => $v){
            $referenceValues[] = $v[$referenceColumn];
         }
         $whereIn = $referenceValues;
         $whereIn = implode(',', $whereIn);
         $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
         // 返回成功记录数（如果没有要更新的数据，返回0）
         \Yii::$app->getDb()->createCommand($updateSql)->execute();
         return true;
      } catch (\Exception $e) {
//       return $e->getMessage();
         return false;
      }
   }        
}