任鸟飞网站设计博客

数据库操作中经常会遇到需要更新多个行、多个字段的操作，这里介绍一种结构清晰的SQL语句，以期能提高数据库操作效率，并尽可能降低系统资源的消耗。

我们先看一个常见的SQL UPDATE语句的写法：

UPDATE mytable SET   myfield = 'value' WHERE other_field = 'other_value';

在更新多行操作，常见的SQL UPDATE写法可能如下所示（PHP环境下）：

foreach ($display_order   as $id => $ordinal) {
$sql = "UPDATE categories SET display_order = $ordinal   WHERE id = $id";
mysql_query($sql);
}

上述代码虽然也能达到我们期待的结果，但很显然，上述代码多次执行数据库更新（UPDATE）操作，当更新行数较多时，将严重占用系统资源。

改进后的数据库更新（UPDATE）操作

UPDATE mytable
 SET myfield = CASE   other_field
 WHEN 1 THEN 'value'
 WHEN 2 THEN 'value'
 WHEN 3 THEN 'value'
END
 WHERE id IN (1,2,3)

经过改进后的上述代码也能实现更新多行的目标，而且更新操作是在一条更新语句中完成，大大降低了数据库更新操作占用的系统资源。

代码说明：

SET myfield = CASE other_field – 使用CASE语句代替具体的字段值。
WHEN 1 THEN ‘value’ – 根据条件（在本例中的判断条件就是id）更新对应的字段.
WHERE id IN (1,2,3) – 指定查询范围，可省略（不推荐） 。

示例

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 UPDATE语句，只要相应的增加CASE语句即可。

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)

在实际应用中可以参考如下代码（PHP环境下）:

//新建一个数组display_order， 按键值的方式给数组初始化（category ids 作为键 ，赋予新的次序）
 $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;

运用上述方法，在更新成千上万行的数据时，将大大提高数据库操作效率，并降低系统资源消耗。