最近公司项目在使用队列任务更新数据的时候,经常会出现运行队列超时,经过排查原因发现是数据量太大,导致频繁连接操作数据库上百条数据,导致运行非常缓慢。后来经过google一番,找到如下方法,记录一下。经过测试,未优化之前更新500条以上数据需要60s以上,使用如下方法优化后插入数据只需要1s左右。


/**
 * 生成批量更新sql语句
 * @param string $table_name 数据表名称
 * @param string $key 更新依据的键,一般填id
 * @param array $update_arr 更新数据的二维数组,数组中要包含参数二的值
 * @return bool
 */
 
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);
//    \Log::debug($query_str);

    return $affected;
}
最后修改:2020 年 07 月 01 日 12 : 01 PM
如果觉得我的文章对你有用,请随意赞赏