本文共 2628 字，大约阅读时间需要 8 分钟。

Laravel数据库——使用 on duplicate key update

在实际应用中，经常需要处理数据的导入需求。当导入的数据不存在时，需要进行添加操作；如果数据存在，则进行更新操作。在MySQL中，ON DUPLICATE KEY UPDATE 语句可以在一次操作中处理这些任务，非常方便。

语句工作原理

ON DUPLICATE KEY UPDATE 依赖于数据库表中的唯一索引或主键。例如，如果在表中定义了一个唯一索引 a，并且表中已经存在记录值为 1 的记录。此时，以下两个 SQL 语句的效果相同：

INSERT INTO table (a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1;UPDATE table SET c = c + 1 WHERE a = 1;

ON DUPLICATE KEY UPDATE 后面可以指定多个字段，使用逗号分隔。例如：

INSERT INTO table (a, b, c) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);

在执行上述插入语句时，如果原表中 a=1 或 a=4 已经存在 [a, b, c] 的记录，将为对应的记录更新 c 字段，将其值增加。

示例解析

对于前一个例子：

• 如果 a=1 有记录存在，c 将被更新为现有 c + 1。
• 如果 a=4 是否存在取决于表中现有数据。

c = VALUES(a) + VALUES(b)：在插入操作中，如果录制 a=1 或 a=4 的值将用于更新 c。

• 第一条记录插入时，VALUES(a) = 1，VALUES(b) = 2，因此 c 将变为 c + 1。
• 第二条记录插入时，VALUES(a) = 4，VALUES(b) = 5，因此 c 将变为 c + 5。

Laravel数据库操作

在 Laravel 中，可以通过查询构造器和模型的方法实现类似功能。updateOrCreate 方法可以支持插入或更新数据，但不能处理批量插入。为了处理批量插入和更新，可以使用 insert 方法和 on duplicate key update 结合。

在数据库模型类中添加方法：

public function insertOrUpdate(array $values, array $value){    $connection = $this->getConnection();    $builder = $this->newQuery();    $grammar = $builder->getGrammar();    // 编译插入语句    $insert = $grammar->compileInsert($builder, $values);    // 编译重复后更新列语句    $update = $this->compileUpdateColumns($grammar, $value);    // 拼接查询    $query = $insert . ' on duplicate key update ' . $update;    // 组装绑定参数    $bindings = $this->prepareBindingsForInsertOrUpdate($values, $value);    // 执行数据库查询    return $connection->insert($query, $bindings);}protected function compileUpdateColumns($grammar, array $values){    return collect($values)->map(function ($value, $key) use ($grammar) {        return $grammar->wrap($key) . ' = ' . $grammar->parameter($value);    })->implode(', ');}protected function prepareBindingsForInsertOrUpdate(array $values, array $value){    $bindings = array_merge_recursive($values, $value);    return array_values(array_filter(array_flatten($bindings, 1), function ($binding) {        return ! $binding instanceof Expression;    }));}

使用方法

在模型实例上调用方法：

$user = [    'username' => '用户名称',    'tel' => '用户电话号码',];$model = new \App\Models\User();// 处理单一记录$model->insertOrUpdate($user, [    'tel' => DB::raw('VALUES(`tel`)'),]);// 处理多条记录$users = [$user, $user];$model->insertOrUpdate($users, [    'username' => DB::raw('VALUES(`username`)'),    'tel' => '常量',]);

参数说明

• $values：插入或更新的值数组，可处理一条或多条记录。
• $value：on duplicate key update 后的更新操作，指定该参数可执行多字段更新。

注意事项

• 若 $value 中的值是常量，则会作为预编译绑定参数处理。
• 若 $value 中的值是 Expression 对象，需谨慎处理，以防 SQL 注入。
• 通过 prepareBindingsForInsertOrUpdate 方法进行绑定参数的过滤，确保安全性。

这一实现在提升数据库操作效率的同时，确保了应用安全，适合复杂的数据导入需求。