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)