MySQL で文字列が特定のデータを含むかどうかをチェックする

MySQLで文字列に部分文字列を含む行を探すには、WHERE LOCATE('widget', product_name) > 0、WHERE INSTR(product_name, 'widget') > 0、または WHERE product_name LIKE '%widget%' を使って絞り込みます。LOCATE() と INSTR() は文字列をそのまま検索し、LIKE はワイルドカードを使った検索に便利です。以下では同じ小さな products テーブルを使い、クエリに合った方法を選べるように説明します。

部分文字列検索用のサンプルテーブルを準備する

識別子と商品名を持つテーブルを作成し、検索語を含む行と含まない行をいくつか追加します。MySQLのセッションでは、CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(255)); を実行し、続けて INSERT INTO products (product_id, product_name) VALUES (1, 'Gadget Widget'), (2, 'Electronic Widget'), (3, 'Toy Widget'), (4, 'Tool Set'); を実行します。

このデータセットでは、3つの商品名に Widget が含まれ、1つには含まれていません。後続のクエリでは最初の3行が選択されます。SQLの例はMySQLで実行することを想定していますが、このタスクにはMySQLサービスで実行した証拠が含まれていないため、結果は実際に観測したMySQLの出力としてではなく、説明として示します。検索を試す前に、ご自身のデータベースでテーブル定義とデータ挿入を一度実行してください。すでに products テーブルがある場合は、別のテーブル名を使うか、スキーマに合わせてセットアップを変更してください。

次のローカルPythonチェックは、サンプルで期待される一致を再現するもので、MySQLを実行するものではありません。Widget を含む商品名を出力します。

products = [
    (1, "Gadget Widget"),
    (2, "Electronic Widget"),
    (3, "Toy Widget"),
    (4, "Tool Set"),
]

for product_id, product_name in products:
    if "Widget" in product_name:
        print(product_id, product_name)

1 Gadget Widget
2 Electronic Widget
3 Toy Widget

LOCATE() と INSTR() で部分文字列を確認する

検索語を第1引数、列の値を第2引数に指定する場合は LOCATE() を使います。書式は LOCATE(substring, string) です。最初に一致した位置を1から数えて返し、一致しない場合は 0 を返します。WHERE 条件で戻り値を > 0 と比較すると、部分文字列を含む行だけが残ります。このテーブルでは、クエリは SELECT product_id, product_name FROM products WHERE LOCATE('Widget', product_name) > 0; となり、ID 1、2、3の行に一致します。

INSTR() も同じ値を検索しますが、引数の順序は逆です。書式は INSTR(string, substring) です。こちらも最初に一致した位置を返し、部分文字列がない場合は 0 を返します。同等のクエリは SELECT product_id, product_name FROM products WHERE INSTR(product_name, 'Widget') > 0; です。こちらも同じ3行を返します。この2つの関数を使い分ける際の主な違いは、どちらの引数を先に置くかです。

どちらの関数も、列の値の中から指定した部分文字列を検索します。すでに決まっている文字列をそのまま検索する場合に便利です。戻り値は行ではなく位置を表す数値であり、WHERE 述語によってその数値を行の絞り込みに使います。結果を扱いやすくするため、* の代わりにアプリケーションで必要な列だけを選択してください。

大文字と小文字を区別するかどうかは、列または式に適用される照合順序（collation）によって決まります。サンプルのクエリでは、サンプルの表記に合わせて Widget を使っています。期待した結果と大文字・小文字の扱いが異なる場合は、列の照合順序を確認してください。MySQLの比較がすべて同じ大文字・小文字の扱いをするとは限りません。

LIKE で部分文字列やパターンに一致させる

LIKE 演算子は、列の値をパターンと比較します。ワイルドカード文字がない場合、パターンは値全体と比較されます。product_name LIKE 'Widget' は、長い商品名のどこかにその文字が含まれるかを探すのではなく、値が Widget に一致するかを調べます。値のどこかに検索語があるか調べるには、両側に % を付けます。たとえば SELECT product_id, product_name FROM products WHERE product_name LIKE '%Widget%'; とします。この包含パターンは、サンプルテーブルのID 1、2、3に一致します。

% ワイルドカードは、0文字以上の任意の文字列を表します。検索語の前に置くと、product_name LIKE '%Widget' のように、その語で終わる値に一致します。検索語の後に置くと、product_name LIKE 'Toy%' のように、その語で始まる値に一致します。前後の両方に % を置けば、検索語の前後に文字があっても一致します。このように配置を変えると、周囲の文字をすべて指定せずに、検索語が現れる位置を表せます。

_ ワイルドカードは、ちょうど1文字を表します。たとえば product_name LIKE 'Gadget_Widget' は、Gadget の後に1文字、その後に Widget が続く値に一致します。このデータセットでは、間の文字が空白である Gadget Widget に一致します。% と _ の役割は異なり、前者は長さを問わない文字列を許容し、後者はその位置に1文字だけを許容します。

必要な検索をパターンで表せる場合は LIKE を使い、部分文字列をそのまま調べる場合は LOCATE() または INSTR() を使います。% や _ をワイルドカードではなく通常の文字として検索するには、パターン文字として残さず、MySQLの LIKE のエスケープ規則に従ってエスケープしてください。これらの述語は行を絞り込むもので、ユーザー入力を検証するものではありません。検索語がユーザーから渡される場合は、データベースクライアントを通じてパラメーターとして渡し、そのクライアントのパラメーター化に関するガイドラインに従って処理してください。