Laravel原生SQL批量插入优化及避免重复参数写法咨询

嘿，这个批量插入更新的需求Laravel其实已经有非常优雅的解决方案了，不用手动拼接那些烦人的(?, ?, ?), (?, ?, ?)格式，我来一步步给你讲清楚：

核心方案：使用Laravel的upsert方法（推荐，Laravel 8.x+支持） #

这个方法就是专门为批量插入+重复键更新的场景设计的，完全符合你的需求，而且自动处理参数绑定，避免SQL注入风险。

步骤1：准备批量数据数组

先把你要插入的多行数据整理成二维数组，每个子数组对应一行employee的数据。注意你原来SQL里的(SELECT id FROM city WHERE city.id = ?)其实等价于直接传入city的ID（因为子查询的结果就是你传入的那个值），所以可以直接用fk_city_id => $cityId；如果需要确保city存在，建议提前批量验证所有city ID的有效性，避免插入无效的外键值。

示例数据：

use Illuminate\Support\Carbon;

$batchEmployees = [
    [
        'fk_country_id' => 1,
        'employee_id' => 'EMP001',
        'fk_city_id' => 5, // 直接传入city的ID，替代子查询
        'password' => bcrypt('emp_pass_001'),
        'role' => 'staff',
        'email' => 'emp001@example.com',
        'joined_at' => Carbon::parse('2023-01-15'),
        'resigned_at' => null,
        'created_at' => Carbon::now(),
        'updated_at' => Carbon::now(),
    ],
    [
        'fk_country_id' => 2,
        'employee_id' => 'EMP002',
        'fk_city_id' => 8,
        'password' => bcrypt('emp_pass_002'),
        'role' => 'manager',
        'email' => 'emp002@example.com',
        'joined_at' => Carbon::parse('2022-09-01'),
        'resigned_at' => Carbon::parse('2024-03-01'),
        'created_at' => Carbon::now(),
        'updated_at' => Carbon::now(),
    ],
    // 更多行...
];

步骤2：执行批量插入更新

直接调用upsert方法，需要传入三个参数：

1. 批量数据数组
2. 唯一键字段：用来判断是否重复的字段（对应ON DUPLICATE KEY UPDATE的触发条件，比如你的employee_id应该是唯一索引）
3. 需要更新的字段列表：当重复时要更新的字段

代码示例：

use Illuminate\Support\Facades\DB;

DB::table('employee')->upsert(
    $batchEmployees,
    ['employee_id'], // 唯一键，必须有对应的唯一索引
    ['email', 'joined_at', 'resigned_at', 'updated_at'] // 重复时更新的字段
);

如果你用Eloquent模型的话，写法更简洁：

Employee::upsert(
    $batchEmployees,
    ['employee_id'],
    ['email', 'joined_at', 'resigned_at', 'updated_at']
);

处理超大数据量：分批次插入 #

如果数据量极大（比如几万甚至几十万条），直接一次性插入可能会超过数据库的max_allowed_packet限制，这时候可以用Laravel的chunk方法分批次处理：

use Illuminate\Support\Collection;

// 假设$allEmployees是你的全部数据
Collection::make($allEmployees)->chunk(1000)->each(function ($chunk) {
    $preparedChunk = $chunk->map(function ($emp) {
        // 这里做数据格式化，比如日期转换、密码加密等
        return [
            'fk_country_id' => $emp['country_id'],
            'employee_id' => $emp['emp_id'],
            'fk_city_id' => $emp['city_id'],
            'password' => bcrypt($emp['password']),
            'role' => $emp['role'],
            'email' => $emp['email'],
            'joined_at' => Carbon::parse($emp['joined_at']),
            'resigned_at' => $emp['resigned_at'] ? Carbon::parse($emp['resigned_at']) : null,
            'created_at' => Carbon::now(),
            'updated_at' => Carbon::now(),
        ];
    })->toArray();

    Employee::upsert($preparedChunk, ['employee_id'], ['email', 'joined_at', 'resigned_at', 'updated_at']);
});

注意事项 #

1. 确保唯一索引存在：upsert依赖数据库的唯一索引来触发更新，所以你的employee表必须给employee_id（或者你指定的唯一键字段）创建唯一索引，否则不会触发更新逻辑。
2. 子查询的替代方案：如果你坚持要用原来的子查询(SELECT id FROM city WHERE city.id = ?)，可以把fk_city_id的值写成DB::raw('(SELECT id FROM city WHERE id = ?)', [$emp['city_id']])，Laravel会自动帮你绑定参数。
3. 性能优化：分批次的大小可以根据你的数据库配置调整，一般1000-2000条一批比较合适，避免占用过多数据库资源。

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