laravel bulk insertで気をつけること

# 追記 2020/11/26

Laravel 8.10以降でLaravelにてbulk upsertがサポートされたみたいです。
@kaya517さん情報ありがとうございます。

>Laravel 8.10.X くらいよりbulk upsertがサポートされました
詳しくはこの[PR](https://github.com/laravel/framework/pull/34698)を参照

以下の記事は内容が古くなっていますがいちおう残しておきます。

# はまりどころ

普通laravelでbulkinsertはこうしますね。

```php
$params = [
    ['first_name' => "tanaka", 'age' => 31, 'is_hungry' => 1 ],
    ['first_name' => "yamada", 'age' => 28, 'is_hungry' => 0 ]
]
DB::table('table-name')->insert($params);
```

しかし、ちょっと複雑な生SQLを書く時、例えばupsertしてcount+1したいときなど、``DB::insert('Insert into hogehoge (a, b, c) values (?,?,?)', $params)``を使うかもしれません。そんなときハマりました。

これは無理

```php5
  
  $params = [
      [ "tanaka", 31, 1 ],
      [ "yamada", 28, 0 ]
  ];

  DB::insert(
      '
      INSERT INTO hogehoge ( first_name, age, is_hungry  ) VALUES (?, ?, ?)
      ',
      $params
  );

```

# こうすれば通る

```php5

  DB::insert(
      '
      INSERT INTO hogehoge ( first_name, age, is_hungry  ) VALUES (?, ?, ?)
      ',
      [ "tanaka", 31, 1 ], [ "yamada", 28, 0 ]
  );

```
が罠である。

可変長引数かと思い冒頭の配列をunpackしてみた。
しかし繰り返すがこれは罠だった。

```php5

  $params = [[ "tanaka", 31, 1 ], [ "yamada", 28, 0 ]];

  DB::insert(
    '
    INSERT INTO hogehoge ( first_name, age, is_hungry  ) VALUES (?, ?, ?)
    ',
     ...$params  //...とつけることでunpack
  );
```

上2つともエラーは吐かないのだが、**配列の最初の値しか反映されない。**

# 先に言うと
``DB::table->insert($params)``は内部でparamsの値の分,

``"Insert into table-name (columns) values (?,?,?),(?,?,?),(?,?,?)"``を生成し、$paramsが2次元配列であれば1次元にflattenしたのちに

DB::insert(先ほどのsql文, flattenしたパラメータ)を実行している。

```php
DB::insert("Insert into table-name (firsta_name, age, is_hungry) values (?,?,?),(?,?,?)", ["tanaka", 31, 1, "yamada", 28, 0])
```

つまり、DB::insertやDB::statmentを使う場合、自分でsql文を書いて、第二引数にflattenした値を入れなければいけない。

# ゆっくり考察

どうやら
``DB::table('table-name')->insert(['a' =>1, 'b'=>2, 'c'=>3 ])``
と
``DB::insert('insert into table-name ( a, b, c) values ( ?, ?, ?)', [1,2,3])``
は違うらしい。
前者はIlluminate/Database/Query/Builderのinsertで、
後者はIlluminate/Database/Connectionのinsertだ。

```php:Illuminate/Database/Query/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))
        );
    }
```

とテーブル名を指定したのちにinsertするとこちらが呼ばれる。動作としては``compileInsert``で``"insert into $table ($columns) values $parameters"``を生成し結局``connection``に渡している。``clearBindings``のやっていることは2次元配列を1次元に平坦化し値から演算子を抜いている。おそらくSQLInjection対策か。

```php:Illuminate/Database/Query/Grammar
   /**
     * 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 into $table ($columns) values $parameters";
    }

```

ちなみにconnectionのinsertはこんな感じ

```php:Illuminate/Database/Connection
   /**
     * Run an insert statement against the database.
     *
     * @param  string  $query
     * @param  array   $bindings
     * @return bool
     */
    public function insert($query, $bindings = [])
    {
        return $this->statement($query, $bindings);
    }

   /**
     * Execute an SQL statement and return the boolean result.
     *
     * @param  string  $query
     * @param  array   $bindings
     * @return bool
     */
    public function statement($query, $bindings = [])
    {
        return $this->run($query, $bindings, function ($query, $bindings) {
            if ($this->pretending()) {
                return true;
            }

            $statement = $this->getPdo()->prepare($query);

            $this->bindValues($statement, $this->prepareBindings($bindings));

            $this->recordsHaveBeenModified();

            return $statement->execute();
        });
    }
```

#結論
laravelでbulk insetをする場合はtableを指定してから配列を渡すとうまくしょりしてくれる。しかし、bulk upsertなど普通でないデータベース依存な実装をしようとするともうsql文を最初から自分で作った方が早い。

こちらのサイトを参考にすると良いとおもます。
https://gist.github.com/RuGa/5354e44883c7651fd15c