如何在Yii2中批量更新PostgreSQL表中的多行数据

背景信息 #

现有$campaigns数据数组结构示例：

[
    // ...
    27 => [
        "name" => "0ca9b377dd066958_ru_acc9_map86",
        "cid" => "53099327454745088",
        "status" => 2,
        "id" => 36842
    ],
    // ...
]

预处理代码（已生成待更新的$updateValues数组，注意修正了原代码中campaign_id的字段匹配问题）：

foreach ($campaigns as $campaign) {
    $id = $campaign['id'];
    $name = $campaign['name'];
    $status = $campaign['status'];
    $index = Campaign::getIndex($name);
    $aSource = Campaign::aSource($name, $status);
    $streamId = Campaign::getStreamIdByName($name);

    $updateValues[] = [
        'id' => $id,
        'name' => $name,
        'status' => $status,
        'index' => $index,
        'a_source' => $aSource,
        'stream_id' => $streamId,
        'updated_at' => new Expression('NOW()')
    ];
}

需求 #

根据$campaigns数组中的id字段，批量更新对应数据库行的name、status、index、a_source、stream_id和updated_at字段。此前尝试过ON DUPLICATE KEY UPDATE和ON CONFLICT (id) DO UPDATE SET的批量插入式更新方案，未解决问题。

解决方案 #

方法1：使用CASE语句构建高效批量更新SQL #

此方法适合MySQL、PostgreSQL等关系型数据库，避免循环执行单条更新语句，性能更优：

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\Expression;

if (empty($updateValues)) {
    return;
}

// 拆解待更新数据，构建CASE分支与参数
$cases = [];
$ids = [];
$params = [];

foreach ($updateValues as $value) {
    $ids[] = $value['id'];
    
    // 为每个字段生成WHEN分支
    $cases['name'][] = "WHEN id = ? THEN ?";
    $cases['status'][] = "WHEN id = ? THEN ?";
    $cases['index'][] = "WHEN id = ? THEN ?";
    $cases['a_source'][] = "WHEN id = ? THEN ?";
    $cases['stream_id'][] = "WHEN id = ? THEN ?";
    
    // 绑定参数（每个WHEN对应两个参数：id、字段值）
    $params = array_merge($params, [
        $value['id'], $value['name'],
        $value['id'], $value['status'],
        $value['id'], $value['index'],
        $value['id'], $value['a_source'],
        $value['id'], $value['stream_id'],
    ]);
}

// 拼接完整SQL语句
$sql = "UPDATE campaigns SET ";
$sql .= "name = CASE " . implode(' ', $cases['name']) . " ELSE name END, ";
$sql .= "status = CASE " . implode(' ', $cases['status']) . " ELSE status END, ";
$sql .= "`index` = CASE " . implode(' ', $cases['index']) . " ELSE `index` END, "; // index为SQL关键字，需转义
$sql .= "a_source = CASE " . implode(' ', $cases['a_source']) . " ELSE a_source END, ";
$sql .= "stream_id = CASE " . implode(' ', $cases['stream_id']) . " ELSE stream_id END, ";
$sql .= "updated_at = NOW() ";
$sql .= "WHERE id IN (" . implode(',', array_fill(0, count($ids), '?')) . ")";

// 合并WHERE条件的ID参数
$params = array_merge($params, $ids);

// 执行批量更新
DB::update($sql, $params);

方法2：Laravel 8+ 用upsert实现（需id为唯一键） #

若id是数据库主键或唯一键，可使用upsert方法，同时过滤掉不存在的ID避免插入新记录：

// 先查询出数据库中已存在的ID
$existingIds = Campaign::whereIn('id', collect($updateValues)->pluck('id'))->pluck('id');
// 过滤待更新数组，仅保留已存在的记录
$filteredValues = collect($updateValues)->whereIn('id', $existingIds)->toArray();

if (!empty($filteredValues)) {
    Campaign::upsert(
        $filteredValues,
        ['id'], // 唯一匹配字段
        ['name', 'status', 'index', 'a_source', 'stream_id', 'updated_at'] // 需要更新的字段列表
    );
}

关键注意事项 #

• 原代码中$id = $campaign['campaign_id'];需修正为$id = $campaign['id'];，匹配数组实际字段。
• 若使用MySQL，index是保留关键字，需用反引号包裹避免语法错误。
• 单次批量更新的行数建议控制在1000行以内，避免长时间锁表影响数据库性能。

内容的提问来源于stack exchange，提问作者FeR-S