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

本文链接：https://blog.csdn.net/szuaudi/article/details/105575139

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

简介
insertOrUpdate方法
使用方法
- $values参数
- $value参数
代码详解
laravel源码

在laravel数据库的查询构造器中， insert方法可以进行批量插入，数据库ORM中提供了 updateOrCreate方法支持插入/更新，但 updateOrCreate不能批量处理。并且 updateOrCreate要先查询再更新，一次 updaeOrCreate要执行两次SQL命令。

/**
 * @mixin \Illuminate\Database\Query\Builder
 */
class Builder
{
	...
	
    /**
     * Create or update a record matching the attributes, and fill it with values.
     *
     * @param  array  $attributes
     * @param  array  $values
     * @return \Illuminate\Database\Eloquent\Model
     */
    public function updateOrCreate(array $attributes, array $values = [])
    {
        return tap($this->firstOrNew($attributes), function ($instance) use ($values) {
            $instance->fill($values)->save();
        });
    }
    
	...
}

使用mysql提供的on duplicate key update方法显示具有很大优势。

简介

使用on duplicate key update肯定要拼接sql语句。可以通过foreach循环$values数组来直接拼接sql语句，这种方式过于简单粗暴，扩展性不好且可能存在SQL注入的风险。在本文中，采用laravel提供的语法器Grammar类进行sql命令的编译。

insertOrUpdate方法

在数据库模型Model类中添加如下代码：

	/**
     * insert or update a record
     *
     * @param array $values
     * @param array $value
     * @return bool
     */
    public function insertOrUpdate(array $values, array $value)
    {
        $connection = $this->getConnection();   // 数据库连接
        $builder = $this->newQuery()->getQuery();   // 查询构造器
        $grammar = $builder->getGrammar();  // 语法器
        // 编译插入语句
        $insert = $grammar->compileInsert($builder, $values);
        // 编译重复后更新列语句。
        $update = $this->compileUpdateColumns($grammar, $value);
        // 构造查询语句
        $query = $insert.' on duplicate key update '.$update;
        // 组装sql绑定参数
        $bindings = $this->prepareBindingsForInsertOrUpdate($values, $value);
        // 执行数据库查询
        return $connection->insert($query, $bindings);
    }
    
	/**
     * Compile all of the columns for an update statement.
     *
     * @param Grammar $grammar
     * @param array $values
     * @return string
     */
    private function compileUpdateColumns($grammar, $values)
    {
        return collect($values)->map(function ($value, $key) use ($grammar) {
            return $grammar->wrap($key).' = '.$grammar->parameter($value);
        })->implode(', ');
    }

    /**
     * Prepare the bindings for an insert or update statement.
     *
     * @param array $values
     * @param array $value
     * @return array
     */
    private function prepareBindingsForInsertOrUpdate(array $values, array $value)
    {
        // Merge array of bindings
        $bindings = array_merge_recursive($values, [$value]);
        // Remove all of the expressions from a list of bindings.
        return array_values(array_filter(array_flatten($bindings, 1), function ($binding) {
            return ! $binding instanceof \Illuminate\Database\Query\Expression;
        }));
    }

使用方法

创建一个model的实例，在实例对象上调用该方法。例如：

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

$values参数

插入或更新一条记录，直接将一维数组作为insertOrUpdate方法的第一个参数进行调用。

	$user = ['username' => '用户名称', 'tel' => '用户电话号码'];
	$model->insertOrUpdate($user, ['tel' => DB::raw('values(`tel`)')]);

因为insertOrCreate方法中使用Illuminate\Database\Query\Builder::insert()方法进行插入，所以兼容一条或多条记录。
insert方法的介绍参见查询构造器 |《Laravel 5.5 中文文档 5.5》| Laravel China 社区

插入多条记录，用数组包裹多条记录的数组为二维数组作为insertOrUpdate方法的第一个参数进行调用。

	$user = ['username' => '用户名称', 'tel' => '用户电话号码'];
    $users = [$user, $user];
	$model->insertOrUpdateinsertOrUpdate($users, ['username' => DB::raw('values(`username`)'), 'tel' => '常量']);

其实，即使是使用一维数组作为insertOrUpdate的参数，在执行insert操作时，依然会包裹成一个二维数组。在\Illuminate\Database\Query\Builder类中，可以看到调用insert方法进行的操作。

class Builder
{
	...
	
    /**
     * Insert a new record into the database.
     *
     * @param  array  $values
     * @return bool
     */
    public function insert(array $values)
    {
        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient when building these
        // inserts statements by verifying these elements are actually an array.
        if (empty($values)) {
            return true;
        }

        if (! is_array(reset($values))) {
            $values = [$values];
        }
        ...
    }
}

$value参数

$value参数指定了on duplicate key update后要执行的语句。$value数组中key即下标会被设置为表的待更新字段名，$value数组中的value会被设置为表的待更新字段的值。
$value的value可以有两种类型。一是常量，二是Illuminate\Database\Query\Expression类的对象。
对于常量类型的value值，会被当做PDO预编译语句的绑定参数处理。
对于Illuminate\Database\Query\Expression类型的value值，会被当做sql命令拼接到sql语句中。可以通过DB::raw()方法创建该对象。在自定义方法prepareBindingsForInsertOrUpdate中会在格式化绑定参数时过滤掉该类型的$value数组的value。

	/**
     * Prepare the bindings for an insert or update statement.
     *
     * @param array $values
     * @param array $value
     * @return array
     */
    private function prepareBindingsForInsertOrUpdate(array $values, array $value)
    {
        // Merge array of bindings
        $bindings = array_merge_recursive($values, $value);
        // Remove all of the expressions from a list of bindings.
        return array_values(array_filter(array_flatten($bindings, 1), function ($binding) {
            return ! $binding instanceof Expression;
        }));
    }

代码详解

insertOrUpdate方法

获取Builder::insert方法编译后的sql语句。

$insert = $grammar->compileInsert($builder, $values);

上面语句执行后，$insert的值为

insert into `user` (`username`, `tel`) values (?, ?), (?, ?)

编译update后面的列字段

$update = $this->compileUpdateColumns($grammar, $value);

上面的语句执行后，update的值为

`username` = values(`username`), `tel` = ?

拼接sql语句

$query = $insert.' on duplicate key update '.$update;

上面语句执行后，$query的值为

insert into `user` (`username`, `tel`) values (?, ?), (?, ?) on duplicate key update `username` = values(`username`), `tel` = ?

组装预编译sql语句的绑定参数

$bindings = $this->prepareBindingsForInsertOrUpdate($values, $value);

上面语句执行完后，$bindings值为

array:5 [▼
  0 => "用户名称"
  1 => "用户电话号码"
  2 => "用户名称"
  3 => "用户电话号码"
  4 => "常量"
]

执行sql命令

$connection->insert($query, $bindings);

compileUpdateColumns方法

该方法主要生成待更新表的字段语句。
对于$value数组中的下标$key，通过$grammar->wrap($key)，如果是字符串格式转换为` $key` ，如果Expression格式，使用$expression->getValue()方法获取到表达式里的字符串。

对于$value数组中的值，通过$grammer->parameter($value)，如果是字符串格式转换为?；如果是Expression格式，使用$expression->getValue()方法获取到表达式里的字符串。
上面语句会把$value数组中的key当做表的字段名，使用"`"符号包裹 $key，如果$key是Expression将直接取出$key中的字符串。

$this->compileUpdateColumns(['username' => DB::raw('values(`username`)'), 'tel' => '常量']);
// 返回值为：`username` = values(`username`), `tel` = ?

prepareBindingsForInsertOrUpdate方法

该方法生成sql语句的绑定参数。
$bindings = array_merge_recursive($values, $value);将$values和$value中的值合并。

	array_values(array_filter(array_flatten($bindings, 1), function ($binding) {
         return ! $binding instanceof Expression;
     }));

将bindings数组变为一维数组并过滤其中的Expression。

laravel源码

$bindings = $this->prepareBindingsForInsertOrUpdate(
	[
		['username' => '用户名称', 'tel' => '用户电话号码'],
		['username' => '用户名称', 'tel' => '用户电话号码']
	],
	['username' => DB::raw('values(`username`)'), 'tel' => '常量']
	);
// array: [0 => "用户名称", 1 => "用户电话号码", 2 => "用户名称", 3 => "用户电话号码", 4 => "常量"]

insertOrUpdate,compileUpdateColumns和prepareBindingsForInsertOrUpdate方法都能在laravel的Grammar类和Builder类中找到原型，只不过是受保护方法protected，不能在类外部直接使用，才移至Model类中。
insertOrUpdate方法参考了Builder类的insert方法：

<?php

namespace Illuminate\Database\Query;

class Builder
{
	...
	
	/**
     * Insert a new record into the database.
     *
     * @param  array  $values
     * @return bool
     */
    public function insert(array $values)
    {
        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient when building these
        // inserts statements by verifying these elements are actually an array.
        if (empty($values)) {
            return true;
        }

        if (! is_array(reset($values))) {
            $values = [$values];
        }

        // Here, we will sort the insert keys for every record so that each insert is
        // in the same order for the record. We need to make sure this is the case
        // so there are not any errors or problems when inserting these records.
        else {
            foreach ($values as $key => $value) {
                ksort($value);

                $values[$key] = $value;
            }
        }

        // Finally, we will run this query against the database connection and return
        // the results. We will need to also flatten these bindings before running
        // the query so they are all in one huge, flattened array for execution.
        return $this->connection->insert(
            $this->grammar->compileInsert($this, $values),
            $this->cleanBindings(Arr::flatten($values, 1))
        );
    }

	...
}

compileUpdateColumns源码为：

<?php

namespace Illuminate\Database\Query\Grammars

class MySqlGrammar extends Grammar
{
	...
	
	/**
     * Compile all of the columns for an update statement.
     *
     * @param  array  $values
     * @return string
     */
    protected function compileUpdateColumns($values)
    {
        return collect($values)->map(function ($value, $key) {
            if ($this->isJsonSelector($key)) {
                return $this->compileJsonUpdateColumn($key, new JsonExpression($value));
            }

            return $this->wrap($key).' = '.$this->parameter($value);
        })->implode(', ');
    }

	...
}

prepareBindingsForInsertOrUpdate方法参考了Builder的mergeBindings和cleanBindings方法：

<?php

namespace Illuminate\Database\Query;

class Builder
{
	...
    
	/**
     * Merge an array of bindings into our bindings.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @return $this
     */
    public function mergeBindings(self $query)
    {
        $this->bindings = array_merge_recursive($this->bindings, $query->bindings);

        return $this;
    }

    /**
     * Remove all of the expressions from a list of bindings.
     *
     * @param  array  $bindings
     * @return array
     */
    protected function cleanBindings(array $bindings)
    {
        return array_values(array_filter($bindings, function ($binding) {
            return ! $binding instanceof Expression;
        }));
    }
	
	...
}