转载别人的博客 作者和源文章已经找不到了 这篇文章帮助我很好的解决了现实的问题 感谢作者
Mysql 更新语句很简单 一般是如下抒写方式
UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';
如果跟新同一个字段
mysql也很简单,修改下
where
即可:
UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');
这里注意,other_values
是一个逗号,
分隔的字符串,如:1,2,3
1 常规方案
那如果更新多条数据为不同的值,可能很多人会这样写:foreach ($display_order as $id => $ordinal) {
$sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
mysql_query($sql);
}
即是循环一条一条的更新记录。一条记录update
一次,这样性能很差,也很容易造成阻塞。
2 高效方案
那么能不能一条sql语句实现批量更新呢?
2.1 CASE WHEN mysql并没有提供可以直接批量更新的方法,但是可以用点小技巧来实现。
UPDATE mytable
SET myfield = CASE id
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
WHERE id IN (1,2,3)
这里使用了case when
这个小技巧来实现批量更新。
举个例子:
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)
这句sql的意思是,更新display_order
字段:
- 如果
id=1
则display_order
的值为3
, - 如果
id=2
则display_order
的值为4
, - 如果
id=3
则display_order
的值为5
。
即是将条件语句写在了一起。
这里的where
部分不影响代码的执行,但是会提高sql执行的效率。
确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where
子句确保只有3行数据执行。
3.2 更新多值
如果更新多个值的话,只需要稍加修改:
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'
END
WHERE id IN (1,2,3)
到这里,已经完成一条mysql语句更新多条记录了。
但是要在业务中运用,需要结合服务端语言。
3.2 PHP实现
这里以php为例,构造这条mysql语句:
$display_order = array(
1 => 4,
2 => 1,
3 => 2,
4 => 3,
5 => 9,
6 => 5,
7 => 8,
8 => 9
);
$ids = implode(',', array_keys($display_order));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($display_order as $id => $ordinal) {
$sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;
这个例子,有8
条记录进行更新。
代码也很容易理解,你学会了吗?