php+mysql实现多条记录批量更新多个字段

mysql批量更新利用case when语法

CASE [col_name] WHEN [value1] THEN [result1] ELSE [default] END

php组转sql代码

/**
 * 生成批量更新sql语句
 * 
 * @param string $table 表名
 * @param string $primary_key 主键名称
 * @param array  $datas 批量更新数据
 *                      
 * $datas[1]['city'] = 'test';
 * $datas[1]['city_id'] = '111';
 * $datas[2]['city'] = 'test222';
 * $datas[2]['city_id'] = '222';
 *
 * @return bool|string
 */
function generateBatchUpdateSql(string $table, string $primary_key, array $datas){
    if(empty($datas)){
        return false;
    }
    $sqlAll           = [];
    $sqlKey        = [];
    foreach($datas as $id => $data){
        foreach($data as $key=>$val){
            if(!isset($sqlKey[$key])){
                $sqlKey[$key][] = sprintf('`%s` = CASE `%s`', $key, $primary_key);
            }
            $sqlKey[$key][] =  sprintf(' WHEN \'%s\' THEN \'%s\' ', $id, $val);
        }
    }

    foreach($sqlKey as $set_sql){
        $sqlAll[]= implode('', $set_sql);
    }

    $id_string = '\'' . implode('\',\'', array_keys($datas)) . '\'';
    return sprintf(
	'update `%s` set %s END where `%s` in (%s)', 
	$table, 
	implode(' END,', $sqlAll) , 
	$primary_key, 
	$id_string
    );
}