0元购京东自营斐讯K2路由器返399元活动
置顶

如何在数据库中一次更新多行多字段

作者:     目录: 小代码     发表: 2010年03月10日

数据库操作中经常会遇到需要更新多个行、多个字段的操作,这里介绍一种结构清晰的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;

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

标签: .

1 个评论

  1. Industryidea says:

    很实用,先收藏了。

Industryidea进行回复 取消回复

电子邮件地址不会被公开。 必填项已用*标注