baoshi-was/app/Services/common/BatchUpdateService.php

<?php


namespace App\Services\common;


use App\Services\LogService;
use Illuminate\Support\Facades\DB;
use App\Traits\ServiceAppAop;


class BatchUpdateService
{
    use ServiceAppAop;
    public function batchUpdate($tableName = '', $multipleData = array(), $connection = 'mysql')
    {
        if (count($multipleData) == 1) {
            return false;
        }
        if ($tableName ?? false && !empty($multipleData)) {
            // column of fields to update
            $updateColumns = $multipleData[0];
            // table primary key
            $referenceColumn = $updateColumns[0];
            unset($multipleData[0]);

            if (count($multipleData) <= 200) {
                return $this->batchHandle($tableName, $updateColumns, $referenceColumn, $multipleData, $connection);
            } else {
                $chunk_datas = array_chunk($multipleData, 200, true);
                $bool = true;
                foreach ($chunk_datas as $chunk_data) {
                    $is_successful = $this->batchHandle($tableName, $updateColumns, $referenceColumn, $chunk_data, $connection);
                    $bool = $is_successful && $bool;
                }
                return $bool;
            }
        } else {
            return false;
        }
    }

    private function batchHandle($tableName, $updateColumns, $referenceColumn, $multipleData, $connection = 'mysql')
    {
        $updateSql = "UPDATE " . $tableName . " SET ";
        $sets = [];
        $bindings = [];
        foreach ($updateColumns as $updateColumn) {
            $setSql = ' `' . $updateColumn . '` = CASE';
            foreach ($multipleData as $multipleDatum) {
                $setSql .= ' WHEN ' . $referenceColumn . ' =  ? THEN ? ';
                $bindings[] = $multipleDatum[$referenceColumn];
                $bindings[] = $multipleDatum[$updateColumn];
            }
            $setSql .= ' ELSE `' . $updateColumn . "` END";
            $sets[] = $setSql;
        }
        $updateSql .= implode(',', $sets);
        $whereIn = data_get($multipleData, '*.' . $referenceColumn);
        $bindings = array_merge($bindings, $whereIn);
        $whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
        $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
        try {
            // DB::update
            $bool = DB::connection($connection)->update($updateSql, $bindings);
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量更新' . count($multipleData) . $tableName . $updateSql . json_encode($bindings));
            return $bool;
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量更新失败' . count($multipleData) . $tableName . $updateSql . json_encode($bindings) . $e->getMessage() . $e->getTraceAsString());
            return false;
        }
    }

    public function batchUpdateItself($tableName, $column, array $params, $connection = 'mysql')
    {
        $sql = "UPDATE ".$tableName;
        $sql .= " SET ".$column." = CASE ".$column;
        foreach ($params as $key => $value){
            $sql .= " WHEN ".$key." THEN ".$value;
        }
        $sql .= " END WHERE ".$column." IN (".implode(',',array_keys($params)).")";
        try {
            $bool = DB::connection($connection)->update($sql);
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量更新本身' .$sql);
            return $bool;
        }catch (\Exception $e){
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量更新失败 SQL:(' . $sql .") 堆栈:". $e->getMessage() . $e->getTraceAsString());
            return false;
        }
    }
}