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
);
}