Laravel批量更新 [英] Laravel Bulk UPDATE
问题描述
$ vouchers =凭单 - >得到(); //假设10K,例如
foreach($ vouchers as $ voucher){
$ q。=UPDATE凭证设置slug ='。 Str :: random(32)。 'WHERE id =。 $ voucher-> id。 ;;
}
DB :: statement($ q);
有大约200万条记录,所以我需要大量执行。将其作为单独的记录进行时间太长。我似乎找不到一种批量运行它们的方法,用10K或以上的组来表示。
尝试了一大堆 > update()
和 DB :: statement
,但似乎无法让它去。
我已经在 CodeIgniter >中创建了我的自定义函数,如
update_batch
code>。
只需将此函数放在任何模型中,或者您可以创建帮助类,并将该函数放在该类中:
//测试数据
/ *
$ multipleData = array(
array(
'title' =''我的标题',
'name'=>'我的名字2',
'date'=>'我的日期2'
),
数组(
'title'=>'另一个标题',
'name'=>'另一个名称2',
'date'=>'另一个日期2'
)
)
* /
/ *
* ---------------------- ----- -------
*更新批次
* ------------------------------- ---
*
*一次查询中的多个更新
*
* tablename(必需|字符串)
* multipleData(必需的数组)
* /
static function updateBatch($ tableName =,$ multipleData = array()){
if($ tableName&!empty($ multipleData)){
//要更新的列或字段
$ updateColumn = array_keys($ multipleData [0]);
$ referenceColumn = $ updateColumn [0]; //e.g id
unset($ updateColumn [0]);
$ whereIn =;
$ q =UPDATE$ tableNameSET;
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,',')
//更新
返回DB :: update(DB :: raw($ q));
} else {
return false;
}
}
它会生成:
更新`mytable` SET`name` = CASE
WHEN`title` ='我的标题'THEN'我的名字2'
WHEN`title` ='另一个标题'THEN'另一个名称2'
ELSE`name` END,
`date` = CASE
WHEN`title` ='我的标题'THEN'我的日期2'
WHEN`title` ='另一个标题'THEN'另一个日期2'
ELSE`date` END
WHERE`title` IN('我的标题','另一个标题' )
I'm trying to update a table containing a slug value with random slugs for each record.
$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.
Tried a bunch of variations of ->update()
and DB::statement
but can't seem to get it to go.
I have created My Custom function for Multiple Update like update_batch
in 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')
这篇关于Laravel批量更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!