php批量更新修改数据库 · php常用实例

完整版 ```php /** * 批量更新函数 * @param $data array 待更新的数据，二维数组格式 * @param $table string 更新的表名 * @param string $field string 值不同的条件，默认为id * @param array $params array 值相同的条件，键值对应的一维数组 * @return bool|string */ public function batchUpdate($data=[],$table='user',$field="id") { if(count($data)==0 || empty($table)) return "" ; //获取所有的id $data_keys = array_keys($data); $col = Db::query("SHOW COLUMNS FROM `".$table."`"); $cols = []; array_map(function($value) use(&$cols){ $cols[] = $value['Field']; },$col); //拼接批量更新sql语句 $sql = "UPDATE {$table} SET "; //合成sql语句 foreach ($data[$data_keys[0]] as $key => $value) { if(!in_array($key,$cols)) continue; $sql .= "{$key} = CASE ".$field." "; foreach ($data as $k=>$v) { $str= $v[$key]; $needle= '+'; $pos = strpos($str, $needle); if($pos){ $z=explode("+",$v[$key]); $sql .= sprintf("WHEN %d THEN '%s'+ %e", $k, $z[0],floatval($z[1])); }else{ $sql .= sprintf("WHEN %d THEN '%s' ", $k, $v[$key]); } } $sql .= "END, "; } //把最后一个,去掉 $sql = substr($sql, 0, strrpos($sql,',')); //合并所有id $ids = implode(',', $data_keys); //拼接sql $sql .= " WHERE ".$field." IN ({$ids})"; return $sql; // dump($userModel->execute($sql)); } ``` 粗写 ```php /** * 批量更新函数 * @param $data array 待更新的数据，二维数组格式 * @param $table string 更新的表名 * @param string $field string 值不同的条件，默认为id * @param array $params array 值相同的条件，键值对应的一维数组 * @return bool|string */ public function batchUpdate($data=[],$table='user',$field="id") { if(count($data)==0 || empty($table)) return "" ; //获取所有的id $data_keys = array_keys($data); $col = Db::query("SHOW COLUMNS FROM `".$table."`"); $cols = []; array_map(function($value) use(&$cols){ $cols[] = $value['Field']; },$col); //拼接批量更新sql语句 $sql = "UPDATE {$table} SET "; //合成sql语句 foreach ($data[$data_keys[0]] as $key => $value) { if(!in_array($key,$cols)) continue; $sql .= "{$key} = CASE ".$field." "; foreach ($data as $k=>$v) { $z=explode("+",$v[$key]); $sql .= sprintf("WHEN %d THEN '%s'+ %e", $k, $z[0],floatval($z[1])); } $sql .= "END, "; } //把最后一个,去掉 $sql = substr($sql, 0, strrpos($sql,',')); //合并所有id $ids = implode(',', $data_keys); //拼接sql $sql .= " WHERE ".$field." IN ({$ids})"; return $sql; // dump($userModel->execute($sql)); } public function batchUpdate2($data=[],$table='user',$field="id") { if(count($data)==0 || empty($table)) return "" ; //获取所有的id $data_keys = array_keys($data); $col = Db::query("SHOW COLUMNS FROM `".$table."`"); $cols = []; array_map(function($value) use(&$cols){ $cols[] = $value['Field']; },$col); //拼接批量更新sql语句 $sql = "UPDATE {$table} SET "; //合成sql语句 foreach ($data[$data_keys[0]] as $key => $value) { if(!in_array($key,$cols)) continue; $sql .= "{$key} = CASE ".$field." "; foreach ($data as $k=>$v) { $sql .= sprintf("WHEN %d THEN '%s' ", $k, $v[$key]); } $sql .= "END, "; } //把最后一个,去掉 $sql = substr($sql, 0, strrpos($sql,',')); //合并所有id $ids = implode(',', $data_keys); //拼接sql $sql .= " WHERE ".$field." IN ({$ids})"; return $sql; // dump($userModel->execute($sql)); } ``` 批量修改方式1 ```sql UPDATE `chaifenpan`.`fa_article` SET `id`='1', `updatetime`='1586507681', `createtime`='1586507681', `cat_id`='16', `content`='技术支持', `short_title`='阿瑟东', `title`='阿瑟东' WHERE (`id`='1'); UPDATE `chaifenpan`.`fa_article` SET `id`='2', `updatetime`='1586507713', `createtime`='1586507713', `cat_id`='15', `content`='行业资讯', `short_title`='啊士大夫', `title`='啊士大夫' WHERE (`id`='2'); UPDATE `chaifenpan`.`fa_article` SET `id`='3', `updatetime`='1589705606', `createtime`='1586507727', `cat_id`='16', `content`='欢迎加鲁', `short_title`=' 简介简介简介简介简介简介简介简介简介', `title`=' 简介简介简介简介' WHERE (`id`='3'); ``` 批量修改方式2 ```sql UPDATE `xuer_info` SET userlevel = ( CASE id WHEN 1 THEN '低级' WHEN 2 THEN '中级' WHEN 3 THEN '高级' WHEN 4 THEN '特级' WHEN 5 THEN 'laji' ELSE '无级' END ), username = ( CASE id WHEN 1 THEN '邓' WHEN 2 THEN '沙' WHEN 3 THEN '利' WHEN 4 THEN '文' WHEN 5 THEN '亨' ELSE username END ), usertype = ( CASE id WHEN 1 THEN 'A1' WHEN 2 THEN 'A2' WHEN 3 THEN 'A3' WHEN 4 THEN 'A4' WHEN 5 THEN 'A5' ELSE usertype END ) ``` <br> --- 程序员交流qq群：782974737 [点击加入](https://jq.qq.com/?_wv=1027&k=5eeinSn)