PHP实现拼接SQL语句批量更新多个字段

来源：互联网 2024-10-07 05:18:59

本文主要和大家分享PHP实现拼接SQL语句批量更新多个字段，希望能帮助到大家。

一、SQL语句

1、批量更新一个字段：

ｕｐｄａｔｅ `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、批量更新多个字段：

ｕｐｄａｔｅ `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 去除表中电话和传真的 &#39;-&#39;//表的最小ID是1，最大ID是121329;fax和phone都是varchar类型//每次查询100条数据，拼接sql语句批量更新public function updeAction(){    set_time_limit(0);    $mGather = new ClientGatherModel();    //$mGather->setDb(&#39;gather&#39;);    //读线上gather库[区分线上和线下数据库]    $cnt = 0;    $minId = 1;    $maxId = 121329;    //每次最多插入100条数据，程序拼接    for ($x=$minId; $x<=$maxId; $x+=100){        $littleMax = $x+99;        $querySql = "ｓｅｌｅｃｔ * 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[&#39;fax&#39;] ? $v[&#39;fax&#39;] : &#39;&#39;;                $phone = $v[&#39;phone&#39;] ? $v[&#39;phone&#39;] : &#39;&#39;;                $id = $v[&#39;id&#39;];                                    if($fax || $phone ){                    if($fax && strstr($fax, &#39;-&#39;)){                        $fax = substr($fax,strpos($fax,&#39;-&#39;)+1);    //截取第一个 - 后面的内容                    }                    if($phone && strstr($phone, &#39;-&#39;)){                        $phone = substr($phone,strpos($phone,&#39;-&#39;)+1);    //截取第一个 - 后面的内容                    }                }                                    $data[] = [&#39;id&#39;=>$id,&#39;fax&#39;=>$fax,&#39;phone&#39;=>$phone];            }            if($data){                $sql .= " ｕｐｄａｔｅ crm_client_gather SET fax = CASE id ";                $faxArr = array_column($data, &#39;fax&#39;, &#39;id&#39;);                foreach ($faxArr as $k => $v){                    $sql .= " WHEN {$k} THEN &#39;{$v}&#39; ";                }                $sql .= " END, ";                                    $phoneArr = array_column($data, &#39;phone&#39;,&#39;id&#39;);                $sql .= " phone = CASE id ";                foreach ( $phoneArr as $k => $v ){                    $sql .= " WHEN {$k} THEN &#39;{$v}&#39; ";                }                $sql .= &#39; END &#39;;                                    $idArr = array_column($data, &#39;id&#39;);                $sql .= " WHERE id IN ( ";                foreach ($idArr as $k => $v){                    if($idArr[count($idArr)-1] == $v ){                        $sql .= $v;                    }else{                        $sql .= $v.&#39;,&#39;;                    }                }                $sql .=")";                                    $mGather->query($sql);                                    if( $littleMax > $maxId ){                    $biggerId = $maxId;                }else{                    $biggerId = $littleMax;                }                echo "ID:[{$x}]-[$biggerId] 更新了 [".count($data)."]条数据 ";//打印进度            }        }    }}