讨论数量:
有一个依赖包laravelBatch,可以优雅的批量更新
use App\Models\User;
$userInstance = new User;
$value = [
[
'id' => 1,
'status' => 'active',
'nickname' => 'Mohammad'
] ,
[
'id' => 5,
'status' => 'deactive',
'nickname' => 'Ghanbari'
] ,
];
$index = 'id';
Batch::update($userInstance, $value, $index);
$tableName = DB::getTablePrefix() . $this->getTable(); // 表名
$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 = [];
$bindings = [];
foreach ($updateColumn as $uColumn) {
$setSql = "`" . $uColumn . "` = CASE ";
foreach ($multipleData as $data) {
$setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
$bindings[] = $data[$referenceColumn];
$bindings[] = $data[$uColumn];
}
$setSql .= "ELSE `" . $uColumn . "` END ";
$sets[] = $setSql;
}
$updateSql .= implode(', ', $sets);
$whereIn = collect($multipleData)
->pluck($referenceColumn)
->values()
->all();
$bindings = array_merge($bindings, $whereIn);
$whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
$updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
// 传入预处理sql语句和对应绑定数据
return DB::update($updateSql, $bindings);
用我的吧,用过很多年了,绝对没问题,还支持自动维护时间字段。
<?php
namespace App;
use Illuminate\Support\Facades\DB;
/**
* 模型基类
*
* @package App\Models
* @author brad <brader.wen@gmail.com>
* @date 2020-08-28
*/
class Model extends \Illuminate\Database\Eloquent\Model
{
/**
* @var string
*/
protected $connection = 'mysql';
/**
* 创建时间
*/
const CREATED_AT = 'create_time';
/**
* 更新时间
*/
const UPDATED_AT = 'update_time';
/**
* @var string 日期格式
*/
protected $dateFormat = 'U';
/**
* @var array 不可被批量赋值的属性
*/
protected $guarded = ['id'];
/**
* @var string
*/
protected $table = '';
/**
* 存储
*
* @param array $args
* @return Mixed
*/
public function store(array $args)
{
if (empty($args)) {
return false;
}
$model = new static($args);
$model->save();
return $model;
}
/**
* 存储
*
* @param array $args
* @return Mixed
*/
protected function _insert(array $args)
{
if (empty($args)) {
return false;
}
return DB::table($this->table)->insert($args);
}
/**
* 批量更新
*
* @param array $inputs
* @param string $where_field
* @param string $when_field
* @return mixed
*
* [['id' => 1, 'status' => 1], ['id' => 2, 'status' => 1]]
*
* update users set name =
* case
* when id = 1 then 'a'
* when id = 2 then 'b'
* where id in (1,2);
*/
public function updateBatch(array $inputs, $where_field = 'id', $when_field = 'id')
{
if (empty($inputs)) {
throw new \InvalidArgumentException('The update data is empty.');
}
if (!($where = array_pluck($inputs, $where_field)) || !($when = array_pluck($inputs, $when_field))) {
throw new \InvalidArgumentException('Missing update condition');
}
$when_arr = [];
foreach ($inputs as $k => $input) {
if (true == $this->timestamps && !is_null(static::UPDATED_AT)) {
$input[static::UPDATED_AT] = time();
}
$when_val = $input[$when_field] ?? '';
if (1 > strlen($when_val)) throw new \InvalidArgumentException('inputs when field must be require');
foreach ($input as $key => $value) {
if ($key == $when_field) continue;
$when_arr[$key][] = "when {$when_field} = '{$when_val}' then '{$value}'";
}
}
$build = DB::connection($this->getConnectionName())->table($this->getTable())->whereIn($where_field, $where);
foreach ($when_arr as $key => &$item) {
$item = DB::raw('case ' . implode(' ', $item) . ' end ');
}
return $build->update($when_arr);
}
/**
* 批量创建
*
* @param array $inputs
* @return bool
*/
public function storeBatch(array $inputs)
{
if (true == $this->timestamps) {
foreach ($inputs as $key => $input) {
$time = time();
if (!is_null(static::CREATED_AT)) {
$inputs[$key][static::CREATED_AT] = $time;
}
if (!is_null(static::UPDATED_AT)) {
$inputs[$key][static::UPDATED_AT] = $time;
}
}
}
$query = DB::connection($this->getConnectionName())->table($this->getTable());
return $query->insert($inputs);
}
}
推荐文章: