追記 2020/11/26
Laravel 8.10以降でLaravelにてbulk upsertがサポートされたみたいです。
@kaya517さん情報ありがとうございます。
Laravel 8.10.X くらいよりbulk upsertがサポートされました
詳しくはこのPRを参照
以下の記事は内容が古くなっていますがいちおう残しておきます。
はまりどころ
普通laravelでbulkinsertはこうしますね。
$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)を使うかもしれません。そんなときハマりました。
これは無理
$params = [
[ "tanaka", 31, 1 ],
[ "yamada", 28, 0 ]
];
DB::insert(
'
INSERT INTO hogehoge ( first_name, age, is_hungry ) VALUES (?, ?, ?)
',
$params
);
こうすれば通る
DB::insert(
'
INSERT INTO hogehoge ( first_name, age, is_hungry ) VALUES (?, ?, ?)
',
[ "tanaka", 31, 1 ], [ "yamada", 28, 0 ]
);
が罠である。
可変長引数かと思い冒頭の配列をunpackしてみた。
しかし繰り返すがこれは罠だった。
$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したパラメータ)を実行している。
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だ。
/**
* 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対策か。
/**
* 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はこんな感じ
/**
* 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