[PDO绑定参数]使用PHP的PDO扩展进行批量更新操作 - hoohack
最近有一个批量更新数据库表中某几个字段的需求,在做这个需求的时候,使用了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语句里面做参数绑定时应该一个一个的绑定。
参考链接:
Can I bind an array to an IN() condition?
原创文章,文笔有限,才疏学浅,文中若有不正之处,万望告知。
如果本文对你有帮助,请点下推荐,写文章不容易。

핫 AI 도구

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

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

Undress AI Tool
무료로 이미지를 벗다

Clothoff.io
AI 옷 제거제

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

인기 기사

뜨거운 도구

메모장++7.3.1
사용하기 쉬운 무료 코드 편집기

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

스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경

드림위버 CS6
시각적 웹 개발 도구

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

뜨거운 주제









