UPDATE yoiurtable
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
这句sql 的意思是,更新dingdan 字段,如果id=1 则dingdan 的值为3,如果id=2 则dingdan 的值为4……
where部分不影响代码的执行,但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。
例子:
UPDATE book
SET Author = CASE id
WHEN 1 THEN '黄飞鸿'
WHEN 2 THEN '方世玉'
WHEN 3 THEN '洪熙官'
END
WHERE id IN (1,2,3)
如果更新多个值的话,只需要稍加修改:
UPDATE categories
SET dingdan = 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'
END
WHERE id IN (1,2,3)
但这还不够,如果我还要增加条件,那么就在后面加上 新的条件 :
UPDATE categories
SET dingdan = 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'
END
WHERE id IN (1,2,3) AND race_id = 532 AND item_id =10
例子:
先有一个需要更新的数组:
$data;//['abc1' => '01:02:09','abc2' => '01:03:09','abc3' => '01:4:09',...]
if (count($data) > 0) {
//把大数组分成块
$chunk_datas = array_chunk($data, 200, true);
foreach ($chunk_datas as $chunk_data) {
$abcs = '"' . implode('","', array_keys($chunk_data)) . '"';
$sql = 'UPDATE users SET ' . $score . '=CASE abe ';
foreach ($chunk_data as $key => $value) {
$sql .= ' WHEN "' . $key . '" THEN "' . $value . '"';
}
$sql .= " END WHERE abc IN ( $abcs) AND race_id = $race_id ";
if($item_id){
$sql .= " AND item_id = $item_id";
}
\DB::update($sql);
}
}
实测可行。