PHP實現拼接SQL語句批次更新多個欄位

本文主要和大家分享PHP實現拼接SQL語句批次更新多個欄位，希望能協助到大家。

一、SQL語句

1、批次更新一個欄位：

UPDATE `user`
SET `name` = CASE id
WHEN 1 THEN '張三'
WHEN 2 THEN '李四'
WHEN 3 THEN '王五'
END
WHERE id IN (1,2,3)

這句sql 的意思是，更新user表的name欄位，如果id=1 則name的值為張三，如果id=2 則name的值為李四

2、批次更新多個欄位：

UPDATE `user`
SET
`name` = CASE id
WHEN 1 THEN '張三'
WHEN 2 THEN '李四'
WHEN 3 THEN '王五'
END,
age = CASE id
WHEN 1 THEN 16
WHEN 2 THEN 17
WHEN 3 THEN 18
END
WHERE id IN (1,2,3)

二、案例
/

/@todo 去除表中電話和傳真的 '-'//表的最小ID是1，最大ID是121329;fax和phone都是varchar類型//每次查詢100條資料，拼接sql語句批次更新public function updeAction(){    set_time_limit(0);    $mGather = new ClientGatherModel();    //$mGather->setDb('gather');    //讀線上gather庫[區分線上和線下資料庫]    $cnt = 0;    $minId = 1;    $maxId = 121329;    //每次最多插入100條資料，程式拼接    for ($x=$minId; $x<=$maxId; $x+=100){        $littleMax = $x+99;        $querySql = "select * from crm_client_gather where id>=$x and id <=$littleMax order by id asc";        $result = $mGather->query($querySql);        $data = [];        $sql = "";        if($result){            foreach ($result as $k => $v){                $fax = $v['fax'] ? $v['fax'] : '';                $phone = $v['phone'] ? $v['phone'] : '';                $id = $v['id'];                                    if($fax || $phone ){                    if($fax && strstr($fax, '-')){                        $fax = substr($fax,strpos($fax,'-')+1);    //截取第一個 - 後面的內容                    }                    if($phone && strstr($phone, '-')){                        $phone = substr($phone,strpos($phone,'-')+1);    //截取第一個 - 後面的內容                    }                }                                    $data[] = ['id'=>$id,'fax'=>$fax,'phone'=>$phone];            }            if($data){                $sql .= " UPDATE crm_client_gather SET fax = CASE id ";                $faxArr = array_column($data, 'fax', 'id');                foreach ($faxArr as $k => $v){                    $sql .= " WHEN {$k} THEN '{$v}' ";                }                $sql .= " END, ";                                    $phoneArr = array_column($data, 'phone','id');                $sql .= " phone = CASE id ";                foreach ( $phoneArr as $k => $v ){                    $sql .= " WHEN {$k} THEN '{$v}' ";                }                $sql .= ' END ';                                    $idArr = array_column($data, 'id');                $sql .= " WHERE id IN ( ";                foreach ($idArr as $k => $v){                    if($idArr[count($idArr)-1] == $v ){                        $sql .= $v;                    }else{                        $sql .= $v.',';                    }                }                $sql .=")";                                    $mGather->query($sql);                                    if( $littleMax > $maxId ){                    $biggerId = $maxId;                }else{                    $biggerId = $littleMax;                }                echo "ID:[{$x}]-[$biggerId] 更新了 [".count($data)."]條資料 ";//列印進度            }        }    }}

相關推薦：

php中mysql語句先行編譯理解

Mysql Sql語句注釋大全執行個體分享

關於多條sql語句的詳細介紹