データベースのあいまい検索を理解する 【32日目】

データベース内のアイテムを検索する際に必要な、『あいまい検索』についての小まとめです。

LIKEの挙動

SQLでは「LIKE句」を使うことで、その文字を含んだカラムを取得することができます。

SELECT * FROM users WHERE furigana LIKE '%あ%';    // 部分一致

「%」は0文字以上の任意の文字列を表すワイルドカードです。

「%」をつける位置によって、そのワードをどこに含んでいるかで検索を分けることができます。

SELECT * FROM users WHERE furigana LIKE 'あ%';    // 前方一致
SELECT * FROM users WHERE furigana LIKE '%あ';    // 後方一致

また、「%」自体を文字として検索する場合は、前に「\」をつけてワイルドカードの機能を無効にできます。
この「\」をエスケープ記号と呼ぶので覚えておきましょう。

文字数を指定してデータ検索

任意の1文字を意味するワイルドカード「_」を使うことで、文字数を指定してデータを検索することもできます。

SELECT * FROM users WHERE furigana LIKE '___文字列';    // 3文字の後に「文字列」がある

指定した文字列を含まないデータ検索

指定した文字列を含まない、いわゆるNOT検索を実装することもできます。

SELECT * FROM users WHERE furigana NOT LIKE '%あ%';

NOT LIKEに続けて文字列を指定することで、指定した文字列を含むレコードが全て除外されます。

プレースホルダーでワイルドカードを使う時の注意点

PHP側でステートメントオブジェクトを使った処理を行うときは、ワイルドカードの扱いに気をつける必要があります。

たとえば、次のように後から値をバインドするために使用される記号（?）の前後に、直接「%」をつけてもうまく機能しません。

$table = ' product ';
$col = ' product_name, price, image ';
$where = ($word !== '') ? " product_name LIKE '%?%' " : '';
$arrVal = ($word !== '') ? [$word] : [];

$sql = " SELECT " . $col . " FROM " . $table . $where;

$stmt = $this->dbh->prepare($sql);
$res = $stmt->execute($arrVal);

これは、「?」自体をシングルクォートで囲ってしまうと、プレースホルダーがリテラルの一部として扱われ、正しく機能しないためです。

そのため、後から「%」を結合する形でexecuteに渡す必要があります。

$table = ' product ';
$col = ' product_name, price, image ';
$where = ($word !== '') ? " product_name LIKE ? " : '';
$arrVal = ($word !== '') ? ['%'.$word.'%'] : [];

$sql = " SELECT " . $col . " FROM " . $table . $where;

$stmt = $this->dbh->prepare($sql);
$res = $stmt->execute($arrVal);

これでうまく機能します。

以上です。

---

参考文献

https://tech.pjin.jp/blog/developer/php_advanced_3_3_13