SQL・テストデータ
一覧のIDや名前をSQLのIN句にまとめたい
使うツール
一覧にある商品コードだけを調べたいとき、各行へ引用符とカンマを付ける作業は単純に見えます。しかし、001というコードを数値へ変えると1になり、NULLを引用するかどうかでも意味が変わります。SQL IN句生成はこの作業を整理できますが、列の型や目的を推測してはくれません。まず小さな例で、元の値がどのようなSQLとして表されるかを確かめます。
商品コードは、数字だけでも文字列かもしれない
入力へ001、010、O'Reillyを一行ずつ入れ、型を文字列、列名をcodeにします。PostgreSQL向けの例では、codeのIN条件に'001'、'010'、'O''Reilly'が入ります。先頭ゼロを持つコードは文字列として残り、値の中の一重引用符は二つへ重ねられます。数字しかないからと数値を選ばず、比較先の列が何を表すかで決めます。
"code" IN ('001', '010', 'O''Reilly')列名を空にすると値の括弧部分だけを作れますが、それがどの列へ使われるかは出力だけでは分かりません。レビュー用には対象列も付けた方が意図を伝えやすくなります。対応する識別子の名前には制限があるため、任意のSQL式を列名へ入れて通す用途ではありません。複雑な式を使うなら、生成部分をどこへ組み込むか別に確認します。
この画面では、空行と重複をどう扱うか
入力は一行一値です。前後の空白は取り除かれ、空行は使われず、同じ文字列の重複は一つになります。したがって、前後の空白そのものが意味を持つ文字列や、空文字を値として含めたい一覧を、そのまま正確に表す用途には向きません。CSVの引用符や区切りを解析する入力欄でもないので、カンマ区切りの一行を貼る前に形式を確認します。
重複削除は文字列が同じかで行われます。数値として同じ意味になる1と01などを、DBと同じ比較規則で統合する処理ではありません。大文字小文字を区別するかもDBの照合条件によります。入力リストで違う値が、実行先では同じと判定される場合があります。生成件数と検索結果件数が必ず一致するわけではない理由の一つです。
NULLをINへ入れるだけでは、未設定行を取れない
NULLという四文字を検索したいなら、文字列として'NULL'を扱います。一方、SQLの未設定値を含めたい場合は別です。NULL方針を別条件にし、列名を指定すると、通常の値のINに加えてIS NULLが生成されます。NULLを通常の等号比較で探すのではなく、未設定の条件を別にするためです。どちらを意図しているか曖昧なら、拒否の設定で止めて確認します。
("code" IN ('alpha') OR "code" IS NULL)この例ではOR全体を括弧で囲んでいます。他のAND条件へ組み込むとき、括弧を外すと対象が広がる場合があるためです。また、INをNOT INへ文字列置換するだけで、欠損値も含めた反対条件になるとは限りません。NULLの比較は三値論理の影響を受けます。否定条件が必要な場合も、含めたい行と除きたい行を具体例で確認してから式を決めます。
アプリからの検索は、生成文字列の連結とは分ける
この画面で引用符を処理できることは、アプリケーションが利用者入力をSQL文字列へ直接連結してよい理由にはなりません。実装ではDBドライバーが提供するパラメーター化を使い、値とSQL構造を分けます。プレースホルダーの書式を調べたい場合はSQLプレースホルダー変換も使えますが、DB接続や実行の安全性をここで検証するものではありません。
特にMySQLでは設定や文字列の内容による引用の扱いも確認が必要です。すべての実行設定へ適合するSQLを保証するツールとは考えず、生成した文を実行先の規則でレビューします。大きな一覧ならDBやドライバーの引数上限、文の長さ、実行計画も別の問題です。長いINを作れたことと、その検索が適切で速いことは同じではありません。
実行前に、元の一覧へ戻って確認する
確認するのは、対象列、文字列か数値か、先頭ゼロ、引用符、空行、重複、NULLの方針です。出力をSQL整形へ渡して周囲の条件と合わせて読むと、括弧の範囲も確認しやすくなります。手元にある一覧が対象全体なのか、一部だけを切り出したものなのかも明示します。入力漏れは、引用符を正しく付けても直りません。
ブラウザー内での生成はDBに何も変更しませんが、コピー後に別の場所で実行する文は別です。特にUPDATEやDELETEの条件へ組み込む場合は、実行対象と承認手順をそちらで確認してください。この記事の目的は、行リストを意味の分かる条件へ整理することです。小さな架空例で値の扱いを確認してから、必要な範囲の作業へ進みます。
一覧の順序は、検索結果の順序にはならない
IN句へB、A、Cの順で入れても、DBの結果がB、A、Cの順に返るとは限りません。INは値が集合に含まれるかを条件にするもので、表示順を指定する句ではないからです。結果に順序が必要なら、対象DBと列に合わせてORDER BYなどを別に考えます。この生成ツールが元の行順を保つことと、DBが同じ順で行を返すことは別です。コピーした一覧と検索結果を目視で行番号ごとに比べる前に、どのキーで照合するかを決めておきます。
同じ値を二回INへ書いても、その値に一致する行を必ず二回返すという意味にはなりません。一方、表の側に同じ値を持つ複数行があれば、それらが条件に一致する場合があります。入力一覧の件数と返る行数が一致しない理由は、重複削除だけではありません。存在しないコード、DBの照合規則、列の型、元データの重複も関係します。この画面はDBへ接続しないので、生成した条件が何件に一致するかを事前に保証しません。
大量の一覧では、正しいSQL文字列を作れても、DBのパラメーター数や文の長さの上限へ達する場合があります。どこまで一度に渡せるかは実行先の条件なので、生成画面の上限をDBの上限と同一視しません。アプリから処理する場合は、値をパラメーターとして渡す方式や、必要に応じた別の照合方法を実装側で選びます。このツールは短い調査用一覧を正しく引用する助けとして使い、任意のユーザー入力をSQLへ連結してよい根拠にはしないでください。