yc_douyin_capi/app/Libs/BatchUpdateTrait.php

<?php

namespace App\Libs;

use Illuminate\Support\Facades\DB;

trait BatchUpdateTrait
{
    /**
     * @param string $table
     * @param array  $list_data
     * @param string  $index
     * @param int    $chunk_size
     *
     * @return int
     */
    public function batchUpdateDb(string $table, array $list_data, string $index = 'id', int $chunk_size = 500): int
    {
        if (count($list_data) < 1 || $chunk_size < 1) {
            return 0;
        }

        $chunk_list = array_chunk($list_data, $chunk_size);
        $count = 0;
        foreach ($chunk_list as $list_item) {
            $first_row = current($list_item);
            $update_col = array_keys($first_row);
            // 默认以id为条件更新，如果没有ID则以第一个字段为条件
            $reference_col = isset($first_row[$index]) ? $index : current($update_col);
            unset($update_col[0]);
            // 拼接sql语句
            $update_sql = 'UPDATE ' . $table . ' SET ';
            $sets = [];
            $bindings = [];
            foreach ($update_col as $u_col) {
                $set_sql = '`' . $u_col . '` = CASE ';
                foreach ($list_item as $item) {
                    $set_sql .= 'WHEN `' . $reference_col . '` = ? THEN ';
                    $bindings[] = $item[$reference_col];
                    if ($item[$u_col] instanceof \Illuminate\Database\Query\Expression) {
                        $set_sql .= $item[$u_col]->getValue() . ' ';
                    } else {
                        $set_sql .= '? ';
                        $bindings[] = $item[$u_col];
                    }
                }
                $set_sql .= 'ELSE `' . $u_col . '` END ';
                $sets[] = $set_sql;
            }
            $update_sql .= implode(', ', $sets);
            $where_in = collect($list_item)->pluck($reference_col)->values()->all();
            $bindings = array_merge($bindings, $where_in);
            $where_in = rtrim(str_repeat('?,', count($where_in)), ',');
            $update_sql = rtrim($update_sql, ', ') . ' WHERE `' . $reference_col . '` IN (' . $where_in . ')';
            //
            $count += DB::update($update_sql, $bindings);
        }
        return $count;
    }
}