MySQLでプレースホルダ使いすぎて怒られるのを動的プレースホルダを使って回避する

雑に数万件ぐらいのidを突っ込んでMySQLへselectクエリ投げたらエラーになったので調べた。

ざっくりまとめ

MySQLでプリペアドステートメントを使う場合、65536個以上のプレースホルダを含めることができない
先の制約はシステム変数で緩和できない (変更できない)
ハックとして、プリペアドステートメントを使わない "動的プレースホルダ" を使うことで先の制約を回避することができる

エラーメッセージ

前提: MySQL 5.7

SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders (SQL select * from iikanji_no_table where id in (1, 2, 3, ....)

MySQLではプレースホルダを65536個以上含められない (らしい)

stackoverflow.com

There is limit 65,535 (2¹⁶-1) place holders in MariaDB 5.5 which is supposed to have identical behaviour as MySQL 5.5.

正確な件数については公式ドキュメントからは見つけられなかった。ソースコード漁ったら見つけられそうなので元気が出たときにでも…。

プレースホルダ数の上限は引き上げられない

システム開発において、チューニングやハックといった "技術で殴る" 戦略よりも "お金で殴る"戦略（e.x. リソース割当量を引き上げる、マシンスペックを上げる、等）のほうが結果的に安上がりになることは良くある。

上限を引き上げられれば暫定対応としては良いんじゃないかなーと思って調べたが、結論引き上げられなさそう。軽くドキュメントを探した限り明確な記述はないものの、変更可能であればシステム変数として記載されているはずなので記載されていないということはそういうことだと思われる。

dev.mysql.com

正攻法はクエリ分割

1クエリで数万のidを突っ込むのは、まあ普通に考えてヤンキー的発想。

例えば合計1万件取りたいなら、1,000件づつなクエリを10回投げるほうがDBに優しい。

それでも俺は1クエリで取りたい

プレースホルダを65536個以上含められないという制約はプリペアドステートメントを使う場合に適用される制約なので、逆に言えばプリペアドステートメントを使わなければこの制約を回避できる。つまり、動的プレースホルダを使えば良い。

例えばPHPでPDOを使う場合、オプションで ATTR_EMULATE_PREPARES = true とすれば動的プレースホルダを使うようになる。

PHP: PDO::setAttribute - Manual

PDO::ATTR_EMULATE_PREPARES プリペアドステートメントのエミュレーションを有効または無効にする。ドライバによってはネイティブのプリペアドステートメントをサポートしていなかったり完全には対応していなかったりするものがある。この設定を使うと、常にプリペアドステートメントをエミュレートする (TRUE およびプリペアのエミュレートがドライバでサポートされている場合) か、ネイティブのプリペアドステートメントを使おうとする (FALSE の場合) かを設定できる。現在のクエリを正しく準備できなかった場合は、常にエミュレート方式を使う。 bool で指定する。

動的プレースホルダは、ざっくり言うとエスケープ処理をDBエンジンに任せず、アプリ側のライブラリでやる方法。当然、利用したライブラリが脆弱な実装だとSQLiの危険があるが、細かいことはIPAが出している「安全なSQLの呼び出し方」PDFや徳丸本を読んでほしい。

www.ipa.go.jp

安全なSQLの呼び出し方: https://www.ipa.go.jp/files/000017320.pdf