SQL语句：
UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END, title = CASE id WHEN 1 THEN 'New Title 1' WHEN 2 THEN 'New Title 2' WHEN 3 THEN 'New Title 3' ENDWHERE id IN (1,2,3)
批量更新只适用于一个字段的更新,原理是用自定义函数拼接sql语句,然后再执行sql语句.
//数据
$data[] = array('id'=>1,'value'=>value1);
$data[] = array('id'=>2,'value'=>value2);
$data[] = array('id'=>3,'value'=>value3);
$this->saveAll($data,表名);
//生成的sql语句结构
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
//批量更新
public function saveAll($datas,$model){
$model || $model=$this->name;
$sql = ''; //Sql
$lists = []; //记录集$lists
$pk = $this->getPk();//获取主键
foreach ($datas as $data) {
foreach ($data as $key=>$value) {
if($pk===$key){
$ids[]=$value;
}else{
$lists[$key].= sprintf("WHEN %u THEN '%s' ",$data[$pk],$value);
}
}
}
foreach ($lists as $key => $value) {
$sql.= sprintf("`%s` = CASE `%s` %s END,",$key,$pk,$value);
}
$sql = sprintf('UPDATE __%s__ SET %s WHERE %s IN ( %s )',strtoupper($model),rtrim($sql,','),$pk,implode(',',$ids));
return M()->execute($sql);
}
