sql批量更新

/**
 * 数据用sql批量更新
 *
 * @param [type] $tableName     表名，包含表前缀
 * @param [type] $updateData	待更新数据列表
 * @param [type] $updateField  	更新字段数组列表，顺序需要与数据列表一致
 * @param [type] $whereId		更新条件，id、order_sn等
 * @return void
 */
public function sqlBatchUpdate($tableName, $updateData, $updateField, $whereId)
{
    $updateValueSql = '';
    $updateIdSql = '';
    $updateKey = $updateField;
    foreach ($updateKey as $key) {
        $n = 0;
        foreach ($updateData as $value) {
            $n++;
            if ($n == 1) {
                $updateValueSql .= $key . " = CASE $whereId ";
            }
            $updateValueSql .= 'WHEN \'' . $value[$whereId] . '\' THEN \'' . $value[$key] . '\'';
            if ($n != count($updateData)) {
                $updateValueSql .= " ";
            } else {
                $updateValueSql .= " END,";
            }
        }
    }
    $updateValueSql = substr($updateValueSql, 0, strlen($updateValueSql) - 1);
    $n = 0;
    foreach ($updateData as $value) {
        $n++;
        if ($n == 1) {
            $updateIdSql .= "(";
        }
        $updateIdSql .= ' \'' . $value[$whereId] . '\' ';
        if ($n != count($updateData)) {
            $updateIdSql .= ",";
        } else {
            $updateIdSql .= ")";
        }
    }
    if (count($updateData)) {
        $update_sql = 'UPDATE ' . $tableName . ' SET ' . $updateValueSql . ' WHERE ' . $whereId . ' IN ' . $updateIdSql . ';';
        Db::query($update_sql); // 执行更新语句
    }
}

　　

    // 根据ip查询省市区
    public function getIpArea()
    {
        $map[] = ['browse_ip', '<>', ''];
        $list = AppDownload::build()
            ->where($map)
            ->where('province',null)
            ->order('id desc')
            ->limit(20)
            ->field('id,browse_ip')
            ->select()
            ->toarray();

        $updateTriggerArray = [];
        foreach ($list as $key => $value) {
            $IpCity = getQqwryArea($value['browse_ip']);
            if ($IpCity) {
                $triggerData['id'] = $value['id'];
                $triggerData['province_id'] = $IpCity['province_id'];
                $triggerData['city_id'] = $IpCity['city_id'];
                $triggerData['district_id'] = $IpCity['district_id'];
                $triggerData['province'] = $IpCity['province'];
                $triggerData['city'] = $IpCity['city'];
                $triggerData['district'] = $IpCity['district'];
                $updateTriggerArray[] = $triggerData;
            }
        }

        // sql语句批量更新
        if ($updateTriggerArray) {
            $tableName = 'jy_app_download';
            $updateField = ['province_id', 'city_id', 'district_id', 'province', 'city', 'district'];
            $this->sqlBatchUpdate($tableName, $updateTriggerArray, $updateField, 'id');
        }
        return true;
    }