PHPのPDOでIN句に配列を渡すときは要素数ぶんの?を作る 空配列のエラーとNOT INのNULLの落とし穴

Bynori

PDOでIN句に配列を渡すには、要素数ぶんのプレースホルダ(?)を自分で組み立てて、配列の中身をexecuteに1つずつ渡します。プレースホルダ1つに配列やカンマ区切り文字列は渡せず、空配列は別途ガードが必要です。

WHERE id IN (?) に [1,2,3] を渡せば3件取れる気がする、という発想は、一度は誰でも通る道だと思います。私も最初は何も疑わずに書いて、結果が0件で首をひねりました。エラーが出ないぶん、気づくまでに時間がかかるんですよね。

プレースホルダ1つに配列を渡すとどうなるのか

プレースホルダは「値1個」の入れ物です。配列をそのまま渡すと文字列”Array”に変換され(警告つき)、カンマ区切りの文字列を渡しても、”1,2,3″という1個の文字列として比較されるだけです。SQLiteで確かめました。

<?php
$st = $pdo->prepare('SELECT id FROM users WHERE id IN (?)');

$st->execute([[1, 2, 3]]);            // Warning: Array to string conversion
var_dump($st->fetchAll());            // array(0) {}

$st->execute([implode(',', [1, 2, 3])]);
var_dump($st->fetchAll());            // array(0) {}  ("1,2,3" という1個の文字列と比べている)

例外にならず「0件」で返ってくるのがいやらしいところで、テストデータが1件しかない開発環境だと、たまたま動いて見えることもあります。

要素数ぶんの?を作るにはどう書くのか

プレースホルダの個数はSQL文を組み立てる時点で決まるので、配列の要素数から「?,?,?」を作ります。array_fillとimplodeの組み合わせが定番です。SQL文に埋め込むのは「?」だけで、ユーザー入力は含まれないので、インジェクションの心配はありません。

<?php
function fetchUsersByIds(PDO $pdo, array $ids): array
{
    if ($ids === []) {
        return [];   // 空配列はここで打ち切る(理由は次の節)
    }

    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $st = $pdo->prepare(
        "SELECT id, name FROM users WHERE id IN ($placeholders) ORDER BY id"
    );
    $st->execute(array_values($ids));   // 位置パラメータは0始まりの連番で渡す

    return $st->fetchAll();
}

fetchUsersByIds($pdo, [3, 1, 4]);
// [{"id":1,"name":"a"},{"id":3,"name":"c"},{"id":4,"name":"d"}]

array_valuesを挟んでいるのは、[‘a’=>3, ‘b’=>1] のように文字列キーの配列が来ても、位置パラメータとして素直に束ねるためです。呼び出し側のキーに依存しない関数になるので、あとから楽になります。

他の条件と混ぜるときは?

位置パラメータ(?)なら、先頭から順番に並べて渡すだけです。スプレッド構文を使うと、固定の条件とIN句の値をひとつの配列にまとめられます。

<?php
$ids = [1, 2, 3, 4];
$placeholders = implode(',', array_fill(0, count($ids), '?'));

$st = $pdo->prepare(
    "SELECT id FROM users WHERE status = ? AND id IN ($placeholders)"
);
$st->execute(['active', ...$ids]);   // status → id群 の順で並べる

名前付きプレースホルダ(:status)にしたい場合は、:id0, :id1 …と連番の名前を自分で作ることになります。ここで気をつけたいのは、1つのSQL文の中で「?」と「:name」を混在させられない点です。どちらかに統一しておくほうが、あとで読み返したときに迷いません。

<?php
$params = ['status' => 'active'];
$names  = [];
foreach ($ids as $i => $id) {
    $names[] = ":id$i";
    $params["id$i"] = $id;
}

$st = $pdo->prepare(
    'SELECT id FROM users WHERE status = :status AND id IN (' . implode(',', $names) . ')'
);
$st->execute($params);

どちらの書き方でも、SQL文の見た目が毎回少し変わるぶん、件数が極端にばらつく場面ではプリペアドステートメントの使い回しの効果は薄れます。ただ、安全性を最優先にすれば、この形に落ち着くことがほとんどだと思います。

なぜ空配列はガードしておくのか

空配列のまま組み立てると、SQLは WHERE id IN () になります。MySQLやPostgreSQLではこれが構文エラーになります。一方、今回試したSQLiteは空のINリストを受け付けて、0件を返しました。つまり、手元のSQLiteで動いていたコードが、本番のMySQLで急に例外になる、ということが起こりえます。

「0件ならクエリを投げずに空を返す」と最初に書いておけば、DBの違いに引きずられません。個人的には、この1行のために関数の先頭を見る癖をつけておく価値はあると思っています。

NOT INとNULLの組み合わせにも注意が必要です

NOT INは、対象の列がNULLの行を結果に含めません。SQLでは NULL NOT IN (…) の結果が真ではなく「不明」になるためです。status列がNULLの行を足して確かめてみます。

<?php
$pdo->exec("INSERT INTO users VALUES (5, 'e', NULL)");

$st = $pdo->query("SELECT id FROM users WHERE status NOT IN ('banned')");
echo json_encode($st->fetchAll());
// [{"id":1},{"id":2},{"id":4}]   ← id=5(statusがNULL)は含まれない

「banned以外すべて」のつもりで書いても、NULLの行は消えます。拾いたいときは (status NOT IN (…) OR status IS NULL) と書くか、そもそも列をNOT NULLにしておくと安心です。

要素が多すぎるときの上限

MySQLのプリペアドステートメントには、プレースホルダ数の上限(65,535個)があり、超えるとエラー1390(Prepared statement contains too many placeholders)になります。数万件のIDを一度にINへ渡す設計は、そもそも見直したほうがよい合図でもあります。array_chunkで分割して複数回に分けるか、一時テーブルやJOINに寄せるのが現実的です。

まとめ

IN句の配列は、?を要素数ぶん作ってexecuteに1つずつ渡す、これだけです。ただ、空配列のガード、NOT INとNULL、件数の上限と、周辺に小さな落とし穴が並んでいます。どれもエラーにならず静かにずれるものが多いので、関数にひとまとめにして使い回すくらいが、ちょうどいい気がしています。

よくある質問

Q. bindValueで配列をバインドする方法はありませんか?
A. PDOには配列を1つのプレースホルダにバインドする仕組みはありません。要素ごとにプレースホルダを作って、それぞれに値を渡す形になります。

Q. 数値のIDなら、implodeでSQL文に直接埋め込んでも大丈夫ですか?
A. 事前にintへキャストしていれば動きはしますが、型の確認を忘れた瞬間にインジェクションの入口になります。プレースホルダで書いておくほうが、あとからの見直しも楽です。

Q. 空配列のときにWHERE句を作らず、全件を取ってしまうのは問題ですか?
A. 「指定なし=全件」という仕様なら問題ありませんが、「該当IDなし=0件」のつもりで書かれたコードだと、意図せず全件を返す事故になります。どちらの意味なのかを呼び出し側と揃えておくのが大事です。

類似投稿

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です