Laravel中使用upsert后如何获取插入与更新的行？

问题背景 #

使用Laravel的upsert批量插入/更新MySQL数据后，需要获取被插入和更新的具体行数据，但直接使用模型查询后调用wasRecentlyCreated或wasChanged无法生效，示例代码如下：

Flight::upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);

尝试过的无效代码：

$dtbClass->upsert($this->items->toArray(), ['JAN'], $this->headings);

// 查询upsert后的记录
$result = $dtbClass->whereIn('JAN', $this->janList)->get();

$checkCreatedClass = false;
$checkUpdatedClass = false;
foreach ($result as $class) {
    if ($checkCreatedClass && $checkUpdatedClass) {
       break;
    }
    if ($class->wasRecentlyCreated) {
       $checkCreatedClass = true;
    } elseif ($class->wasChanged()) {
       $checkUpdatedClass = true;
    }
}

无效原因 #

wasRecentlyCreated仅针对通过create或刚完成首次保存的单个模型实例生效，批量查询返回的模型不会携带该状态；wasChanged则是针对模型实例修改后未保存的临时状态，查询出来的现有实例也不会触发该标记，因此上述方法无法区分插入/更新行。

可行方案 #

方案1：利用MySQL 8.0.19+的RETURNING特性（推荐）

MySQL 8.0.19及以上版本支持INSERT ... ON DUPLICATE KEY UPDATE语句添加RETURNING子句，直接返回所有受影响的行数据。需要自定义DB查询实现：

use Illuminate\Support\Facades\DB;

$values = [
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
];

// 提取字段列表
$columns = array_keys($values[0]);
// 生成插入值的占位符（避免参数冲突）
$placeholders = collect($values)->map(function ($item) {
    $uniqueSuffix = uniqid();
    return '(' . implode(', ', array_map(function ($col) use ($uniqueSuffix) {
        return ":{$col}_{$uniqueSuffix}";
    }, $columns)) . ')';
})->implode(', ');

// 生成更新字段语句（排除唯一键字段）
$updateFields = collect($columns)
    ->filter(fn($col) => !in_array($col, ['departure', 'destination']))
    ->map(fn($col) => "{$col} = VALUES({$col})")
    ->implode(', ');

// 绑定参数
$bindings = collect($values)->flatMap(function ($item) {
    $uniqueSuffix = uniqid();
    $params = [];
    foreach ($columns as $col) {
        $params[":{$col}_{$uniqueSuffix}"] = $item[$col];
    }
    return $params;
})->toArray();

// 执行查询并获取受影响的行
$affectedRows = DB::select(
    "INSERT INTO flights (" . implode(', ', $columns) . ")
     VALUES {$placeholders}
     ON DUPLICATE KEY UPDATE {$updateFields}
     RETURNING *",
    $bindings
);

// 区分插入/更新：可通过created_at与updated_at是否相等判断
$inserted = collect($affectedRows)->filter(fn($row) => $row->created_at == $row->updated_at);
$updated = collect($affectedRows)->filter(fn($row) => $row->created_at != $row->updated_at);

方案2：预查询现有记录（兼容低版本MySQL）

如果MySQL版本低于8.0.19，无法使用RETURNING，可以先查询出已存在的记录，再对比upsert后的结果区分插入/更新：

$values = [
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
];

// 提取唯一键组合，用于查询现有记录
$uniqueKeyGroups = collect($values)->map(fn($item) => [
    'departure' => $item['departure'],
    'destination' => $item['destination']
]);

// 查询已存在的记录，用唯一键组合作为标识键
$existingRecords = Flight::where(function ($query) use ($uniqueKeyGroups) {
    foreach ($uniqueKeyGroups as $keys) {
        $query->orWhere($keys);
    }
})->get()->keyBy(fn($flight) => "{$flight->departure}|{$flight->destination}");

// 执行upsert
Flight::upsert($values, ['departure', 'destination'], ['price']);

// 区分插入和更新的记录
$inserted = [];
$updated = [];

foreach ($values as $item) {
    $key = "{$item['departure']}|{$item['destination']}";
    $flight = Flight::where($item)->first();
    if ($existingRecords->has($key)) {
        $updated[] = $flight;
    } else {
        $inserted[] = $flight;
    }
}

内容的提问来源于stack exchange，提问作者Paos