如何在 Laravel 中使用一个查询保存多条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32575038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to save multiple records with one query in Laravel?
提问by Mahmoud Zalt
I have over 100,000 records in my hand, as Array of STD Objects(can be converted to Array of Arraysor Array of Eloquent Models).
我手头有超过 100,000 条记录， as Array of STD Objects（可以转换为Array of Arrays或Array of Eloquent Models）。
How to store all of them in the database with 1 query.
如何使用1 个查询将所有这些存储在数据库中。
If you are wondering I got these data from another database connection to save them into my database.
如果您想知道我从另一个数据库连接获取这些数据以将它们保存到我的数据库中。
Of course I can build each Eloquent model and call the save()function but this will take a lot of time and is not recommended anyway.
I tried the insert()on the model passing to it an Array of Eloquent Models, but didn't worked for some reasons!!
当然，我可以构建每个 Eloquent 模型并调用该save()函数，但这会花费很多时间，无论如何都不推荐。我尝试insert()在模型上传递一个 Eloquent 模型数组，但由于某些原因没有奏效！！
So what options do I have, other than generating the raw SQL query?
那么除了生成原始 SQL 查询之外，我还有哪些选择？
I am using Laravel 4.2 here.
我在这里使用 Laravel 4.2。
回答by patricus
You can use the insert()method on the Model, but it must be an array of arrays. You also need to make sure that all your array entries have the same keys. If an entry is missing a key, the inserted values for that entry may be off.
可以insert()在Model上使用该方法，但必须是数组数组。您还需要确保所有数组条目都具有相同的键。如果条目缺少键，则该条目的插入值可能会关闭。
You mentioned you're starting with an array of objects. You can convert this array of objects to an array of arrays, and then use the insert statement.
你提到你从一组对象开始。您可以将这个对象数组转换为数组数组，然后使用插入语句。
Assuming you have a Bookmodel:
假设你有一个Book模型：
$data = [
(object) [
'title' => 'Title 1',
'author' => 'Author 1',
'publisher' => 'Publisher 1',
],
(object) [
'title' => 'Title 2',
'author' => 'Author 2',
'publisher' => null, // make sure key exists; use null value;
],
];
// Convert your objects to arrays. You can use array_walk instead,
// if you prefer to just modify the original $data
$arrayData = array_map(function($value) {
return (array) $value;
}, $data);
// do the insert
$success = Book::insert($arrayData);
// view last query run (to confirm one bulk insert statement)
$log = DB::getQueryLog();
print_r(end($log));
I don't know the details about your existing data, so you may need some more complicated logic to ensure the arrays are correct, but the above is a gist of what should work.
我不知道有关您现有数据的详细信息，因此您可能需要一些更复杂的逻辑来确保数组正确，但以上是应该工作的要点。
Edit
编辑
When doing a bulk insert like this, you also need to pay attention to the max_allowed_packetMySQL variable. The default value for this variable is 1 MB, so if you generate a SQL statement larger than 1 MB, you will get an error. max_allowed_packet documentation
像这样进行批量插入时，还需要注意max_allowed_packetMySQL变量。此变量的默认值为 1 MB，因此如果生成大于 1 MB 的 SQL 语句，则会出错。max_allowed_packet 文档
So, if this is your issue, you can either up the max_allowed_packetMySQL variable so that it is large enough to contain your entire SQL statement, or you can break your insert into chunks small enough to fit under the current max_allowed_packetsize (default 1 MB).
因此，如果这是您的问题，您可以增加max_allowed_packetMySQL 变量以使其足够大以包含整个 SQL 语句，或者您可以将插入分成足够小的块以适应当前max_allowed_packet大小（默认为 1 MB）。
An example of chunking your inserts:
分块插入的示例：
// assume $arrayData is your array of arrays
// you can come up with some fancy algorithm to determine your optimal
// chunk size if you want.
$size = 100;
$chunks = array_chunk($arrayData, $size);
foreach($chunks as $chunk) {
Book::insert($chunk);
}
