［PDO绑定参数］使用PHP的PDO扩展进行批量更新操作

最近有一个批量更新数据库表中某几个字段的需求，在做这个需求的时候，使用了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>

로그인 후 복사