목차
方案选择
PDO绑定参数
总结
php教程 php手册 [PDO绑定参数]使用PHP的PDO扩展进行批量更新操作 - hoohack

[PDO绑定参数]使用PHP的PDO扩展进行批量更新操作 - hoohack

May 20, 2016 am 11:40 AM

最近有一个批量更新数据库表中某几个字段的需求,在做这个需求的时候,使用了PDO做参数绑定,其中遇到了一个坑。

方案选择

笔者已知的做批量更新有以下几种方案:

1、逐条更新

  这种是最简单的方案,但无疑也是效率最低的方案。

2、CASE WHEN

  类似如下的语句

<span style="color: #0000ff;">UPDATE</span> tbl_test <span style="color: #0000ff;">SET</span> val <span style="color: #808080;">=</span> <span style="color: #ff00ff;">CASE</span> id <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #0000ff;">END</span> <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">IN</span>(<span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #800000; font-weight: bold;">2</span>);
로그인 후 복사

PDO绑定参数

为了防止SQL注入,使用了PDO扩展绑定参数。上面的数字在一般情况下是变量,那么就需要做参数绑定。刚开始是想着在IN的时候将id组成的字符串作为变量绑定过去,第一次实现的代码如下:

<span style="color: #008080;"> 1</span> <span style="color: #000000;">php
</span><span style="color: #008080;"> 2</span>         <span style="color: #800080;">$data</span> = <span style="color: #0000ff;">array</span>(<span style="color: #0000ff;">array</span>('id' => 1, 'val' => 2), <span style="color: #0000ff;">array</span>('id' => 2, 'val' => 3<span style="color: #000000;">));
</span><span style="color: #008080;"> 3</span>         <span style="color: #800080;">$ids</span> = <span style="color: #008080;">implode</span>(',', <span style="color: #008080;">array_map</span>(<span style="color: #0000ff;">function</span>(<span style="color: #800080;">$v</span>) {<span style="color: #0000ff;">return</span> <span style="color: #800080;">$v</span>['id'];}, <span style="color: #800080;">$data</span>)); <span style="color: #008000;">//</span><span style="color: #008000;">获取ID数组</span>
<span style="color: #008080;"> 4</span>         <span style="color: #800080;">$update_sql</span> = 'UPDATE tbl_test SET val = CASE id'<span style="color: #000000;">;
</span><span style="color: #008080;"> 5</span>         <span style="color: #800080;">$params</span> = <span style="color: #0000ff;">array</span><span style="color: #000000;">();
</span><span style="color: #008080;"> 6</span>         <span style="color: #800080;">$params</span>[":ids"] = <span style="color: #800080;">$ids</span><span style="color: #000000;">;
</span><span style="color: #008080;"> 7</span>         <span style="color: #0000ff;">foreach</span>(<span style="color: #800080;">$data</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$key</span> => <span style="color: #800080;">$item</span><span style="color: #000000;">) {
</span><span style="color: #008080;"> 8</span>                 <span style="color: #800080;">$update_sql</span> .= "WHEN :id_" . <span style="color: #800080;">$key</span> . "THEN :val_" . <span style="color: #800080;">$key</span> . " "<span style="color: #000000;">;
</span><span style="color: #008080;"> 9</span>                 <span style="color: #800080;">$params</span>[":id_" . <span style="color: #800080;">$key</span>] = <span style="color: #800080;">$item</span>['id'<span style="color: #000000;">];
</span><span style="color: #008080;">10</span>                 <span style="color: #800080;">$params</span>[":val_" . <span style="color: #800080;">$key</span>] = <span style="color: #800080;">$item</span>['val'<span style="color: #000000;">];
</span><span style="color: #008080;">11</span> <span style="color: #000000;">        }
</span><span style="color: #008080;">12</span>         <span style="color: #800080;">$update_sql</span> .= "END WHERE id IN (:_ids)"<span style="color: #000000;">;
</span><span style="color: #008080;">13</span>         <span style="color: #800080;">TEST::</span>execute(<span style="color: #800080;">$update_sql</span>, <span style="color: #800080;">$params</span>);//此处会调用bindParam绑定参数
로그인 후 복사

后来发现这样是行不通的,而且比较诡异的是这样只能更新第一条记录。查阅资料后,发现这样的绑定方式是不行的,IN语句的参数应该一个一个地绑定。看看文档中对bindParam函数的描述:

可以看到,说明里写的是会绑定一个PHP变量到占位符里,所以如果绑定了:ids为1, 2的字符串,那么MySQL解析语句的时候会将1,2解析为单个的变量,而不会当作一串。这也是PDO防SQL注入的原理,通过占位符的绑定,只将绑定的值当作一个值,而不是语句之类的其它东西,这样MySQL只会把传递过去的值当作一个变量的值。

修改后的写法:

<span style="color: #008080;"> 1</span> <span style="color: #000000;">php
</span><span style="color: #008080;"> 2</span>          <span style="color: #800080;">$data</span> = <span style="color: #0000ff;">array</span>(<span style="color: #0000ff;">array</span>('id' => 1, 'val' => 2), <span style="color: #0000ff;">array</span>('id' => 2, 'val' => 3<span style="color: #000000;">));
</span><span style="color: #008080;"> 3</span>          <span style="color: #800080;">$update_sql</span> = 'UPDATE tbl_test SET val = CASE id'<span style="color: #000000;">;
</span><span style="color: #008080;"> 4</span>          <span style="color: #800080;">$params</span> = <span style="color: #0000ff;">array</span><span style="color: #000000;">();
</span><span style="color: #008080;"> 5</span>          <span style="color: #800080;">$params</span>[":ids"] = <span style="color: #800080;">$ids</span><span style="color: #000000;">;
</span><span style="color: #008080;"> 6</span>          <span style="color: #800080;">$in_arr</span> = <span style="color: #0000ff;">array</span><span style="color: #000000;">();
</span><span style="color: #008080;"> 7</span> 
<span style="color: #008080;"> 8</span>          <span style="color: #0000ff;">foreach</span>(<span style="color: #800080;">$data</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$key</span> => <span style="color: #800080;">$item</span><span style="color: #000000;">) {
</span><span style="color: #008080;"> 9</span>                  <span style="color: #800080;">$update_sql</span> .= "WHEN :id_" . <span style="color: #800080;">$key</span> . "THEN :val_" . <span style="color: #800080;">$key</span> . " "<span style="color: #000000;">;
</span><span style="color: #008080;">10</span>                  <span style="color: #800080;">$params</span>[":id_" . <span style="color: #800080;">$key</span>] = <span style="color: #800080;">$item</span>['id'<span style="color: #000000;">];
</span><span style="color: #008080;">11</span>                  <span style="color: #800080;">$params</span>[":val_" . <span style="color: #800080;">$key</span>] = <span style="color: #800080;">$item</span>['val'<span style="color: #000000;">];
</span><span style="color: #008080;">12</span>                  <span style="color: #800080;">$params</span>[":ids_" . <span style="color: #800080;">$key</span>] = <span style="color: #800080;">$item</span>['id'<span style="color: #000000;">];
</span><span style="color: #008080;">13</span>                  <span style="color: #008080;">array_push</span>(<span style="color: #800080;">$in_arr</span>, ":id_" . <span style="color: #800080;">$key</span><span style="color: #000000;">);
</span><span style="color: #008080;">14</span> <span style="color: #000000;">         }
</span><span style="color: #008080;">15</span>          <span style="color: #800080;">$update_sql</span> .= "END WHERE id IN (" . <span style="color: #008080;">implode</span>(',' <span style="color: #800080;">$in_arr</span>) . ")"<span style="color: #000000;">;
</span><span style="color: #008080;">16</span>          TEST::execute(<span style="color: #800080;">$update_sql</span>, <span style="color: #800080;">$params</span>);<span style="color: #008000;">//</span><span style="color: #008000;">此处会调用bindParam绑定参数</span>
로그인 후 복사

 

总结

这是最近遇到的一个小问题,其实更多的是说明在MySQL的IN语句里面做参数绑定时应该一个一个的绑定。

 

参考链接:

mysql语句:批量更新多条记录的不同值

 

Can I bind an array to an IN() condition?

 

原创文章,文笔有限,才疏学浅,文中若有不正之处,万望告知。

如果本文对你有帮助,请点下推荐,写文章不容易。

 

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

뜨거운 도구

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)