Laravel Bulk UPDATE

问题:

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.

回答1:

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')

回答2:

In case someone land in this page like me, laravel allows a bulk update as:

$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

回答3:

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.

For example:

  Voucher::chunk(2000, function($vouchers)
  {
    foreach ($vouchers as $voucher)
    {
        //
    }
 });

回答4:

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:

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')