Laravel中如何实现批量insertOrUpdate？应对6万条及增长数据

Hey there! I’ve run into this exact problem before—looping through 60k+ records for insert/update in Laravel is a total performance killer, especially as your dataset grows. Let’s cover the best, fastest ways to handle bulk upserts in Laravel.

Laravel 8 introduced the upsert() method specifically for this use case. It’s clean, efficient, and leverages your database’s native bulk upsert functionality (like MySQL’s INSERT ... ON DUPLICATE KEY UPDATE or PostgreSQL’s INSERT ... ON CONFLICT DO UPDATE), so it only hits the database once per batch.

用法示例 #

Suppose you’re working with a products table where sku is your unique identifier (must have a unique index or primary key in the database):

// 你的批量数据数组（60k条都可以放这里，或者分批次）
$products = [
    ['sku' => 'PROD-001', 'name' => '15" Laptop', 'price' => 999.99],
    ['sku' => 'PROD-002', 'name' => 'Wireless Headphones', 'price' => 199.99],
    // ... thousands more records
];

// 执行批量upsert
DB::table('products')->upsert(
    $products,
    ['sku'], // 用来判断记录是否存在的唯一键字段
    ['name', 'price'] // 当记录存在时需要更新的字段
);

关键注意点 #

• Database Unique Constraint: The field(s) you pass as the second argument must have a unique index or primary key in your database. Without this, the upsert won’t work correctly.
• Eloquent Support: You can also use this on Eloquent models directly: Product::upsert($products, ['sku'], ['name', 'price']);

If you’re stuck on Laravel 7 or earlier, you can manually build a bulk upsert SQL statement. This gives you full control and still performs way better than looping.

MySQL示例 #

$products = [
    ['sku' => 'PROD-001', 'name' => '15" Laptop', 'price' => 999.99],
    // ... your bulk data
];

// 从第一条记录中提取字段名
$columns = array_keys($products[0]);

// 安全格式化值（防止SQL注入）
$values = collect($products)->map(function ($item) use ($columns) {
    return '(' . implode(', ', array_map(function ($col) use ($item) {
        return DB::getPdo()->quote($item[$col]);
    }, $columns)) . ')';
})->implode(', ');

// 构建更新子句（仅更新非唯一字段）
$updateFields = array_diff($columns, ['sku']);
$updateClause = implode(', ', array_map(function ($col) {
    return "`$col` = VALUES(`$col`)";
}, $updateFields));

// 执行SQL语句
DB::statement("
    INSERT INTO products (" . implode(', ', $columns) . ")
    VALUES $values
    ON DUPLICATE KEY UPDATE $updateClause
");

Even with bulk methods, handling 60k+ records needs some tweaks to avoid memory or timeout issues:

• Chunk Your Data: Split your 60k records into smaller batches (e.g., 1000 records per batch) using Laravel’s chunk() method:

  collect($products)->chunk(1000)->each(function ($batch) {
      DB::table('products')->upsert($batch, ['sku'], ['name', 'price']);
  });
  

• Disable Model Events: If using Eloquent, disable model events (like creating, updating) during bulk operations—they add unnecessary overhead:

  Product::withoutEvents(function () use ($products) {
      Product::upsert($products, ['sku'], ['name', 'price']);
  });
  

• Use a Single Transaction: Wrap your batch operations in a transaction to ensure atomicity and improve speed:

  DB::transaction(function () use ($products) {
      collect($products)->chunk(1000)->each(function ($batch) {
          DB::table('products')->upsert($batch, ['sku'], ['name', 'price']);
      });
  });
  

• Verify Indexes: Double-check that your unique key field (like sku) has a unique index—without it, the database will do a full table scan for each check, killing performance.

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