［PDO綁定參數］使用PHP的PDO擴充進行批次更新操作 php pdo sqlite php pdo odbc php開啟pd

最近有一個批量更新資料庫表中某幾個欄位的需求，在做這個需求的時候，使用了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?

原創文章，文筆有限，才疏學淺，文中若有不正之處，萬望告知。

如果本文對你有幫助，請點下推薦，寫文章不容易。

以上就介紹了［PDO綁定參數］使用PHP的PDO擴充進行批次更新操作，包括了php,pdo方面的內容，希望對PHP教程有興趣的朋友有所幫助。