php 使用 Laravel 的 Fluent 插入忽略

INSERT IGNORE using Laravel's Fluent

提问by Nyxynyx

Is there a quick way to modify a SQL query generated by Laravel's Fluentto have an INSERT IGNOREinstead of the usual INSERT?

有没有一种快速的方法来修改由Laravel 的 Fluent生成的 SQL 查询，INSERT IGNORE而不是通常的INSERT？

I'm trying to insert an array with fifty elements. Writing out the entire query manually will bloat up the code and make it more susceptible to human errors.

我正在尝试插入一个包含五十个元素的数组。手动写出整个查询会使代码膨胀并使其更容易受到人为错误的影响。

采纳答案by hakre

For the job you need to create a new Grammar that will have the right string in there:

对于这项工作，您需要创建一个新的 Grammar，其中将包含正确的字符串：

grammar.php⁽¹⁾

语法.php ⁽¹⁾

The grammar is a public property of the DBor in this case Databasestored connection. This is not really straight forward, but from the visibility of the properties you should be able to inject your special grammar into the database layer.

语法是DB或在这种情况下Database存储连接的公共属性。这不是很直接，但从属性的可见性来看，您应该能够将您的特殊语法注入数据库层。

I also suggest you bring the issue up with the project, they probably have got a better idea how to make that more flexible for cases like these.

我还建议你在项目中提出这个问题，他们可能已经有了更好的想法，如何让这种情况更灵活。

---

---

⁽¹⁾This was a former, to the date of the answer reference. If you see this today, you need to adopt to the Laravel version you use, e.g. Grammar.php for 4.0, these classes have moved into laravel/framework.

⁽¹⁾这是以前的，到答案参考的日期为止。如果你今天看到这个，你需要采用你使用的 Laravel 版本，例如Grammar.php 4.0，这些类已经移到laravel/framework.

回答by malhal

Try this magic, in your model:

在你的模型中试试这个魔法：

public static function insertIgnore($array){
    $a = new static();
    if($a->timestamps){
        $now = \Carbon\Carbon::now();
        $array['created_at'] = $now;
        $array['updated_at'] = $now;
    }
    DB::insert('INSERT IGNORE INTO '.$a->table.' ('.implode(',',array_keys($array)).
        ') values (?'.str_repeat(',?',count($array) - 1).')',array_values($array));
}

Use like this:

像这样使用：

Shop::insertIgnore(array('name' => 'myshop'));

This is a great way to prevent constraint violations that may occur with firstOrCreate in a multi-user environment, if that 'name' property was a unique key.

如果“名称”属性是唯一键，则这是防止在多用户环境中使用 firstOrCreate 可能发生的约束冲突的好方法。

回答by J. Bruni

I couldn't monkey patch as suggested in Rastislav's answer.

我无法按照 Rastislav 的回答中的建议进行猴子补丁。

This is what worked for me:

这对我有用：

1. Override compileInsertmethod in a custom Query Grammar class, which extends the framework's MySqlGrammar class.

2. Use an instance of this custom grammar class by calling the setQueryGrammarmethod from the DB connection instance.

1. 覆盖compileInsert自定义查询语法类中的方法，该类扩展了框架的 MySqlGrammar 类。

2. 通过setQueryGrammar从数据库连接实例调用方法来使用此自定义语法类的实例。

So, the class code is like this:

所以，类代码是这样的：

<?php

namespace My\Namespace;

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\MySqlGrammar;

/**
 * Changes "INSERT" to "INSERT IGNORE"
 */
class CustomMySqlGrammar extends MySqlGrammar
{
    /**
     * Compile an insert statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileInsert(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

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

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.
        $parameters = collect($values)->map(function ($record) {
            return '('.$this->parameterize($record).')';
        })->implode(', ');

        return "insert ignore into $table ($columns) values $parameters";
    }
}

I copied the compileInsertmethod from the framework's class and then, inside the method, I have only changed insertto insert ignore. Everything else has been kept the same.

我compileInsert从框架的类中复制了该方法，然后在方法内部，我只更改insert了insert ignore. 其他一切都保持不变。

Then, in the specific spot of code, in the application (a scheduled task), where I needed "insert ignore", I have simply done as follows:

然后，在特定的代码点，在应用程序（计划任务）中，我需要“插入忽略”，我简单地做了如下：

<?php

use DB;
use My\Namespace\CustomMySqlGrammar;

class SomeClass
{
    public function someMethod()
    {
        // Changes "INSERT" to "INSERT IGNORE"
        DB::connection()->setQueryGrammar(new CustomMySqlGrammar());

        // et cetera... for example:
        ModelClass::insert($data);
    }
}

回答by Ryan

Updated answer for Laravel Eloquent in 2018

2018 年 Laravel Eloquent 的更新答案

This also handles multiple simultaneous inserts (instead of one record at a time).

这也处理多个同时插入（而不是一次一个记录）。

Warning: Eric's comment belowis probably correct. This code worked for my past project, but before using this code again, I'd take a closer look at it and add test cases and adjust the function until it always works as intended. It might be as simple as moving the TODO line down outside the ifbraces.

警告：Eric 下面的评论可能是正确的。此代码适用于我过去的项目，但在再次使用此代码之前，我会仔细查看它并添加测试用例并调整功能，直到它始终按预期工作。这可能就像将 TODO 行向下移动到if大括号外一样简单。

Either put this in your model's class or in a BaseModel class that your model extends:

要么把它放在你模型的类中，要么放在你的模型扩展的 BaseModel 类中：

/**
 * @see https://stackoverflow.com/a/25472319/470749
 * 
 * @param array $arrayOfArrays
 * @return bool
 */
public static function insertIgnore($arrayOfArrays) {
    $static = new static();
    $table = with(new static)->getTable(); //https://github.com/laravel/framework/issues/1436#issuecomment-28985630
    $questionMarks = '';
    $values = [];
    foreach ($arrayOfArrays as $k => $array) {
        if ($static->timestamps) {
            $now = \Carbon\Carbon::now();
            $arrayOfArrays[$k]['created_at'] = $now;
            $arrayOfArrays[$k]['updated_at'] = $now;
            if ($k > 0) {
                $questionMarks .= ',';
            }
            $questionMarks .= '(?' . str_repeat(',?', count($array) - 1) . ')';
            $values = array_merge($values, array_values($array));//TODO
        }
    }
    $query = 'INSERT IGNORE INTO ' . $table . ' (' . implode(',', array_keys($array)) . ') VALUES ' . $questionMarks;
    return DB::insert($query, $values);
}

Use like this:

像这样使用：

Shop::insertIgnore([['name' => 'myShop'], ['name' => 'otherShop']]);

Shop::insertIgnore([['name' => 'myShop'], ['name' => 'otherShop']]);

回答by Rastislav Molnar

Not sure if helpful for anybody but recently I have adapted hakre's approach to Laravel 5:

不确定是否对任何人有帮助，但最近我已经将 hakre 的方法应用于 Laravel 5：

You have to change following 3 files to have your Insert Ignore working:

您必须更改以下 3 个文件才能使插入忽略工作：

1. In Builder.php (vendor/laravel/framework/src/illuminate/database/query/Builder.php) you have to clon the function insert, with the change in name to insertIgnore and change in the grammar call function to: $sql = $this->grammar->compileInsertIgnore($this, $values);)

2. In Grammar.php (vendor/laravel/framework/src/illuminate/database/query/grammars/Grammar.php) you have to clone the compileInsert function and rename it to compileInsertIgnore, where you change return to: return "insert ignore into $table ($columns) values $parameters";

3. In Connection.php (vendor/laravel/framework/src/illuminate/database/Connection.php) you have to simply clone the function insert and rename it to insertIgnore

1. 在 Builder.php (vendor/laravel/framework/src/illuminate/database/query/Builder.php) 中，您必须克隆函数 insert，将名称更改为 insertIgnore 并将语法调用函数更改为： $sql = $this->grammar->compileInsertIgnore($this, $values);)

2. 在 Grammar.php (vendor/laravel/framework/src/illuminate/database/query/grammars/Grammar.php) 中，您必须克隆 compileInsert 函数并将其重命名为 compileInsertIgnore，在此处更改返回： return "insert ignore into $table ($columns) values $parameters";

3. 在 Connection.php (vendor/laravel/framework/src/illuminate/database/Connection.php) 中，您只需克隆函数 insert 并将其重命名为 insertIgnore

Now you should be done, connection is able to recognise the function insertIgnore, builder is able to point it to correct grammar and grammar includes 'ignore' in the statement. Please note this works well for MySQL, might not be this smooth for other databases.

现在你应该完成了，connection 能够识别函数 insertIgnore，builder 能够将它指向正确的语法，并且语法在语句中包含 'ignore'。请注意这对 MySQL 很有效，对于其他数据库可能不那么顺利。

回答by Marco Pedraza

Add the follow method insertIgnore to your Model

将以下方法 insertIgnore 添加到您的模型

<?php

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract;
use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract;

class User extends Model implements AuthenticatableContract,
                                    AuthorizableContract,
                                    CanResetPasswordContract
{
    use Authenticatable, Authorizable, CanResetPassword;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'users';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name', 'email', 'password'];

    /**
     * The attributes excluded from the model's JSON form.
     *
     * @var array
     */
    protected $hidden = ['password', 'remember_token'];


    public static function insertIgnore(array $attributes = [])
    {
        $model = new static($attributes);

        if ($model->usesTimestamps()) {
            $model->updateTimestamps();
        }

        $attributes = $model->getAttributes();

        $query = $model->newBaseQueryBuilder();
        $processor = $query->getProcessor();
        $grammar = $query->getGrammar();

        $table = $grammar->wrapTable($model->getTable());
        $keyName = $model->getKeyName();
        $columns = $grammar->columnize(array_keys($attributes));
        $values = $grammar->parameterize($attributes);

        $sql = "insert ignore into {$table} ({$columns}) values ({$values})";

        $id = $processor->processInsertGetId($query, $sql, array_values($attributes));

        $model->setAttribute($keyName, $id);

        return $model;
    }
}

You can use:

您可以使用：

App\User::insertIgnore([
    'name' => 'Marco Pedraza',
    'email' => '[email protected]'
]);

The next query it will be executed:

将执行下一个查询：

insert ignore into `users` (`name`, `email`, `updated_at`, `created_at`) values (?, ?, ?, ?)

This method automatically add/remove the Eloquent timestamps if you have enabled or disabled.

如果您已启用或禁用，此方法会自动添加/删除 Eloquent 时间戳。

回答by Gediminas

Option to avoid writing code is: https://github.com/guidocella/eloquent-insert-on-duplicate-key

避免编写代码的选项是：https: //github.com/guidocella/eloquent-insert-on-duplicate-key

I have tested it just now - it works with my 5000 inserts at a time sometimes with duplicates...

我刚刚测试过它 - 它一次与我的 5000 个插入一起工作，有时会重复......

With it you will get these functions:

有了它，您将获得以下功能：

User::insertOnDuplicateKey($data);
User::insertIgnore($data);

回答by Aammad Ullah

I lastly found this one https://github.com/yadakhov/insert-on-duplicate-keywhich helped me alot

我终于找到了这个https://github.com/yadakhov/insert-on-duplicate-key这对我有很大帮助

User::insertIgnore($users); this is the method I am using, giving array of rows to it and its returning effected rows

User::insertIgnore($users); 这是我正在使用的方法，为其提供行数组及其返回的受影响行

install it through composer: composer require yadakhov/insert-on-duplicate-key

通过 composer 安装它： composer require yadakhov/insert-on-duplicate-key

回答by wesside

$your_array = array('column' => 'value', 'second_column' => 'value');

DB::table('your_table')->insert($your_array);

Keep in mind, I don't know where your data is coming from, but you should sanitize it, always. If you have more than one record, just iterate over in a loop.

请记住，我不知道您的数据来自哪里，但您应该始终对其进行消毒。如果您有多个记录，只需在循环中迭代即可。

As far as the INSERT IGNORE, find the INSERTmethod in the fluent library, make a new method called insert_ignore the exact same way as insert and just modify with the IGNORE.

至于INSERT IGNORE，INSERT在 fluent 库中找到该方法，创建一个名为 insert_ignore 的新方法，方法与 insert 完全相同，只需使用IGNORE.