How to batch update multiple records in laravel (prevent SQL injection)

The following tutorial column of Laravel will introduce to you how to batch update multiple records in laravel, which can prevent SQL injection. I hope it will be helpful to friends in need!

laravel updates multiple records in batches to prevent SQL injection

Write in front

Everyone who is familiar with laravel knows that laravel has Multiple records are inserted in batches at one time, but multiple records are not updated conditionally at one time.

Do you envy thinkphp's saveAll, do you envy ci's update_batch, but why does such an elegant laravel not have a similar batch update method?

Experts Googled the folk

and found that someone had already written on stackoverflow (https://stackoverflow.com/questions/26133977/laravel-bulk-update) Okay, but it doesn't prevent sql injection.

This article has been adjusted in conjunction with laravel's Eloquent to effectively prevent sql injection.

<?php
namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

/**
 * 学生表模型
 */
class Students extends Model
{
    protected $table = &#39;students&#39;;

    //批量更新
    public function updateBatch($multipleData = [])
    {
        try {
            if (empty($multipleData)) {
                throw new \Exception("数据不能为空");
            }
            $tableName = DB::getTablePrefix() . $this->getTable(); // 表名
            $firstRow  = current($multipleData);

            $updateColumn = array_keys($firstRow);
            // 默认以id为条件更新，如果没有ID则以第一个字段为条件
            $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
            unset($updateColumn[0]);
            // 拼接sql语句
            $updateSql = "UPDATE " . $tableName . " SET ";
            $sets      = [];
            $bindings  = [];
            foreach ($updateColumn as $uColumn) {
                $setSql = "`" . $uColumn . "` = CASE ";
                foreach ($multipleData as $data) {
                    $setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
                    $bindings[] = $data[$referenceColumn];
                    $bindings[] = $data[$uColumn];
                }
                $setSql .= "ELSE `" . $uColumn . "` END ";
                $sets[] = $setSql;
            }
            $updateSql .= implode(', ', $sets);
            $whereIn   = collect($multipleData)->pluck($referenceColumn)->values()->all();
            $bindings  = array_merge($bindings, $whereIn);
            $whereIn   = rtrim(str_repeat('?,', count($whereIn)), ',');
            $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
            // 传入预处理sql语句和对应绑定数据
            return DB::update($updateSql, $bindings);
        } catch (\Exception $e) {
            return false;
        }
    }
}

You can make adjustments according to your own needs. The following is a usage example:

// 要批量更新的数组
$students = [
    ['id' => 1, 'name' => '张三', 'email' => 'zhansan@qq.com'],
    ['id' => 2, 'name' => '李四', 'email' => 'lisi@qq.com'],
];

// 批量更新
app(Students::class)->updateBatch($students);

The generated SQL statement is as follows:

UPDATE pre_students
SET NAME = CASE
WHEN id = 1 THEN
    '张三'
WHEN id = 2 THEN
    '李四'
ELSE
    NAME
END,
 email = CASE
WHEN id = 1 THEN
    'zhansan@qq.com'
WHEN id = 2 THEN
    'lisi@qq.com'
ELSE
    email
END
WHERE
    id IN (1, 2)

Is the efficiency improved a lot? Well~

Related recommendations:The latest five Laravel video tutorials

The above is the detailed content of How to batch update multiple records in laravel (prevent SQL injection). For more information, please follow other related articles on the PHP Chinese website!