需求是这样的,根据uid,从一张表查出sum(num1),sum(num2)数据,然后插入这张表的num3,num4字段中。而且每天都需要定时执行一次,表大小还可以,原来我是通过foreach实现的,一条条update,发现等待时间太长,不友好。
因为mysql是没有批量update的,后来百度可以通过case when实现一条sql批量update
例子如下:
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行数据执行。
$sql = "select sum(num1) as num1,sum(num2) as num2,uid from table"; $data = $model->query($sql); foreach($data as $k=>$v){ $str1 .= "when " . $v['uid'] . " then " . $v['num1'] . " "; $str2 .= "when " . $v['uid'] . " then " . $v['num2'] . " "; $str3 .= $v['uid'] . ','; } $str3 = rtrim($str3,','); $sql = "UPDATE table SET num3 = CASE uid " . $str1 . " END,num4 = CASE uid " . $str2 . " END WHERE uid IN (" . $str3 . ")"; $model->query($sql);
这样就是实现了一句话批量执行update,你也可以自己封装成一个公用方法,以后直接调用
下面是我封装的一个
/** * 批量update,返回sql语句 * @DateTime:2016-11-04 17:20:21 * @Author: cc */ function updateAll($table = '', $data = array(), $field = '') { if (!$table || !$data || !$field) { return false; } else { $sql = 'UPDATE ' . $table; } $con = array(); $con_sql = array(); $fields = array(); foreach ($data as $key => $value) { $x = 0; foreach ($value as $k => $v) { if ($k != $field && !$con[$x] && $x == 0) { //需要更新的字段,第一个字段头 $con[$x] = " set {$k} = (CASE {$field} "; } elseif ($k != $field && !$con[$x] && $x > 0) { //第二个开始需要更新的字段头 $con[$x] = " {$k} = (CASE {$field} "; } if ($k != $field) { $temp = $value[$field]; //拼接when-then $con_sql[$x] .= " WHEN '{$temp}' THEN '{$v}' "; $x++; } } $temp = $value[$field]; if (!in_array($temp, $fields)) { //拼接in判断 $fields[] = $temp; } } $num = count($con) - 1; foreach ($con as $key => $value) { foreach ($con_sql as $k => $v) { if ($k == $key && $key < $num) { $sql .= $value . $v . ' end),'; } elseif ($k == $key && $key == $num) { //最后一条拼接 $sql .= $value . $v . ' end)'; } } } $str = implode(',', $fields); $sql .= " where {$field} in({$str})"; return $sql; }
以上就是我用case when组成的一条MYSQL语句实现批量更新数据。