>php教程 >php手册 >[PDO绑定参数]使用PHP的PDO扩展进行批量更新操作,pdo绑定

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

WBOY
WBOY원래의
2016-06-13 08:41:18867검색

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

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

方案选择

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

1、逐条更新

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

2、CASE WHEN

  类似如下的语句

<span>UPDATE</span> tbl_test <span>SET</span> val <span>=</span> <span>CASE</span> id <span>WHEN</span> <span>1</span> <span>THEN</span> <span>2</span> <span>WHEN</span> <span>2</span> <span>THEN</span> <span>3</span> <span>END</span> <span>WHERE</span> id <span>IN</span>(<span>1</span>, <span>2</span>);

PDO绑定参数

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

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

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

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

修改后的写法:

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

 

总结

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

 

参考链接:

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

 

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

 

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

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

 

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