MYSQL 处理批量更新数据的一些经验

dwd4 9年前

摘要 很多人都会碰到有一堆数据需要更新,但是他们所需要更新的数值往往都不尽相同,貌似就只能用FOR循环去更新了吗?我就不信......

首先,我们需要了解下MYSQL CASE EXPRESSION 语法。

手册传送门:http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

有关这个的讨论的stackoverflow:http://stackoverflow.com/questions/29205842/can-mysql-case-expression-running-well-with-the-same-when-condition


看完以上,你就会少走很多弯路。

我有很多条记录需要更新不同的值怎么办呢?

我们可以这样:

UPDATE activity  SET number = CASE  WHEN aid = 45 THEN      number + 1   WHEN aid = 43 THEN      number + 1   END  WHERE      aid IN (45, 43)


或者这样:

UPDATE activity  SET number = CASE aid   WHEN 45 THEN      number + 1   WHEN 43 THEN      number + 1   END  WHERE      aid IN (45, 43)


这个语法和程序语言中的switch case控制流差不多意思,但是有几点需要注意:

ex1:

UPDATE activity  SET number = CASE  WHEN aid = 45 THEN      number + 1   WHEN aid = 43 THEN      number + 1   WHEN aid = 45 THEN      number + 3  WHEN aid = 49 THEN      number + 1  WHEN aid = 45 THEN      number + 1  END  WHERE      aid IN (45, 43,49)


看到了吗?会不会有人一开始像我一样认为aid=45 的number+5的?放心....aid=45 的number就仅仅加了1.换句话说也就是第一个when aid=45 then number+1执行了!第二次再碰到when aid=45的时候,系统就认为这个条件已经执行过了,后门的aid=45就直接跳过不执行的了,但是中间的aid=43 aid=49的还是可以正常执行的!

也就是说,所有相同的when条件就只执行第一次!


ex2:

UPDATE activity  SET number = CASE aid   WHEN 45 THEN      number + 1   WHEN 43 THEN      number + 1   END


这个你觉得会怎么样?貌似和上面的没啥不同啊?这个就糟糕了.....如果没有约束条件,那么aid=45 aid=43的number+1以外,别的aid都会set number=null.....这就好像你用简单版的update set没有写where条件来约束范围的情况一样。小心小心.....


这里只讨论了如何使用CASE EXPRESSION 来进行批量更新.....

无论是insert 或者update,批量处理总是要比一个个来的要效率,当然这里指的是一般情况下的使用。最后来看看在PHP下使用批量更新的处理方案:

/*  $aids is an array which the key is the activity's primary key and the   value is the activity's number.  example $aids = array('45'=>4,'43'=>1...)  In the array,i combine all the same aid to one element.so one aid can   not be used twice by the case expression.And  all the number are total  number.  */  $sql = 'update huodong_activity set applynumber = case ';  $aids_str = implode(',',array_keys($aids));   foreach($aids as $k=>$v){        $sql .= sprintf(' when aid = %d then applynumber-%d',$k,$v);  }  $sql .= ' END WHERE aid IN ('.$aids_str.') ';  $db->query($sql);


注意语句链接要适当给予空格....