Laravel 批量更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26133977/
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
Laravel Bulk UPDATE
提问by Rob
I'm trying to update a table containing a slug value with random slugs for each record.
我正在尝试使用每个记录的随机 slug 更新包含一个 slug 值的表。
$vouchers = Voucher->get(); // assume 10K for example
foreach ($vouchers as $voucher) {
$q .= "UPDATE vouchers set slug = '" . Str::random(32) . "' WHERE id = " . $voucher->id . ";";
}
DB::statement($q);
There are about 2 million records so I need to perform this as a bulk. Doing it as separate records is taking way too long. I can't seem to find a way to bulk run them, say in groups of 10K or something.
大约有 200 万条记录，因此我需要批量执行此操作。将其作为单独的记录进行花费的时间太长了。我似乎无法找到批量运行它们的方法，比如以 10K 为一组。
Tried a bunch of variations of ->update()and DB::statementbut can't seem to get it to go.
尝试了一堆的变化->update()和DB::statement，但似乎无法得到它去。
回答by akash varlani
I have created My Custom function for Multiple Update like update_batchin CodeIgniter.
我创建我的自定义功能，像多更新update_batch在CodeIgniter。
Just place this function in any of your model or you can create helper class and place this function in that class:
只需将此函数放置在您的任何模型中，或者您可以创建辅助类并将此函数放置在该类中：
//test data
/*
$multipleData = array(
array(
'title' => 'My title' ,
'name' => 'My Name 2' ,
'date' => 'My date 2'
),
array(
'title' => 'Another title' ,
'name' => 'Another Name 2' ,
'date' => 'Another date 2'
)
)
*/
/*
* ----------------------------------
* update batch
* ----------------------------------
*
* multiple update in one query
*
* tablename( required | string )
* multipleData ( required | array of array )
*/
static function updateBatch($tableName = "", $multipleData = array()){
if( $tableName && !empty($multipleData) ) {
// column or fields to update
$updateColumn = array_keys($multipleData[0]);
$referenceColumn = $updateColumn[0]; //e.g id
unset($updateColumn[0]);
$whereIn = "";
$q = "UPDATE ".$tableName." SET ";
foreach ( $updateColumn as $uColumn ) {
$q .= $uColumn." = CASE ";
foreach( $multipleData as $data ) {
$q .= "WHEN ".$referenceColumn." = ".$data[$referenceColumn]." THEN '".$data[$uColumn]."' ";
}
$q .= "ELSE ".$uColumn." END, ";
}
foreach( $multipleData as $data ) {
$whereIn .= "'".$data[$referenceColumn]."', ";
}
$q = rtrim($q, ", ")." WHERE ".$referenceColumn." IN (". rtrim($whereIn, ', ').")";
// Update
return DB::update(DB::raw($q));
} else {
return false;
}
}
It will Produces:
它将产生：
UPDATE `mytable` SET `name` = CASE
WHEN `title` = 'My title' THEN 'My Name 2'
WHEN `title` = 'Another title' THEN 'Another Name 2'
ELSE `name` END,
`date` = CASE
WHEN `title` = 'My title' THEN 'My date 2'
WHEN `title` = 'Another title' THEN 'Another date 2'
ELSE `date` END
WHERE `title` IN ('My title','Another title')
回答by dinnouti
In case someone land in this page like me, laravel allows a bulk update as:
如果有人像我一样登陆这个页面，laravel 允许批量更新为：
$affectedRows = Voucher::where('id', '=', $voucher->id)->update(array('slug' => Str::random(32)));
$affectedRows = Voucher::where('id', '=', $voucher->id)->update(array('slug' => Str::random(32)));
See "Updating A Retrieved Model" under http://laravel.com/docs/4.2/eloquent#insert-update-delete
请参阅http://laravel.com/docs/4.2/eloquent#insert-update-delete下的“更新检索到的模型”
回答by Anam
Chunking results is the best way to do this kind of stuff without eating all of your RAM and Laravel support chunking results out of the box.
分块结果是在不吃掉所有 RAM 的情况下执行此类操作的最佳方式，并且 Laravel 支持开箱即用的分块结果。
For example:
例如：
Voucher::chunk(2000, function($vouchers)
{
foreach ($vouchers as $voucher)
{
//
}
});
回答by Khalil Kamran
I made a bulk update function to use in my Laravel projects. It may be useful for anyone who wants to use the batch update query in Laravel. Its first parameter is the table name string, second is the key name string based on which you want to update the row or rows and most of the times it will be the 'id' and the third parameter is a data array in the following format:
我制作了一个批量更新功能以在我的 Laravel 项目中使用。对于想要在 Laravel 中使用批量更新查询的任何人来说，它可能很有用。它的第一个参数是表名字符串，第二个是您想要更新行的键名字符串，大多数情况下它将是“id”，第三个参数是以下格式的数据数组：
array(
array(
'id' => 1,
'col_1_name' => 'col_1_val',
'col_2_name' => 'col_2_val',
//...
),
array(
'id' => 2,
'col_1_name' => 'col_1_val',
'col_2_name' => 'col_2_val',
//...
),
//...
);
The function will return the number of affected rows. Function definition:
该函数将返回受影响的行数。函数定义：
private function custom_batch_update(string $table_name = '', string $key = '', Array $update_arr = array()) {
if(!$table_name || !$key || !$update_arr){
return false;
}
$update_keys = array_keys($update_arr[0]);
$update_keys_count = count($update_keys);
for ($i = 0; $i < $update_keys_count; $i++) {
$key_name = $update_keys[$i];
if($key === $key_name){
continue;
}
$when_{$key_name} = $key_name . ' = CASE';
}
$length = count($update_arr);
$index = 0;
$query_str = 'UPDATE ' . $table_name . ' SET ';
$when_str = '';
$where_str = ' WHERE ' . $key . ' IN(';
while ($index < $length) {
$when_str = " WHEN $key = '{$update_arr[$index][$key]}' THEN";
$where_str .= "'{$update_arr[$index][$key]}',";
for ($i = 0; $i < $update_keys_count; $i++) {
$key_name = $update_keys[$i];
if($key === $key_name){
continue;
}
$when_{$key_name} .= $when_str . " '{$update_arr[$index][$key_name]}'";
}
$index++;
}
for ($i = 0; $i < $update_keys_count; $i++) {
$key_name = $update_keys[$i];
if($key === $key_name){
continue;
}
$when_{$key_name} .= ' ELSE ' . $key_name . ' END, ';
$query_str .= $when_{$key_name};
}
$query_str = rtrim($query_str, ', ');
$where_str = rtrim($where_str, ',') . ')';
$query_str .= $where_str;
$affected = DB::update($query_str);
return $affected;
}
It will produce and execute the query string like this:
它将生成并执行这样的查询字符串：
UPDATE table_name SET col_1_name = CASE
WHEN id = '1' THEN 'col_1_value'
WHEN id = '2' THEN 'col_1_value'
ELSE col_1_name END,
col_2_name = CASE
WHEN id = '1' THEN 'col_2_value'
WHEN id = '2' THEN 'col_2_value'
ELSE col_2_name END
WHERE id IN('1','2')
