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

［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?

 

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

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