【MySQL】INSTRを使った部分一致検索

作成日 2019-08-12更新日 2019-08-12

MySQLでINSTRを使用した部分一致検索の方法を紹介したいと思います。

そもそも、INSTRとは部分文字列が最初に出現する位置のインデックスを返すMySQLの関数です。

紹介するのは下記の２つです。

下記のようなケースです。

SQLは下記のようになります。

SELECT * FROM テーブル WHERE INSTR(CAST(name AS BINARY), CAST('一' AS BINARY)) > 0;

CASTでBINARYを指定しているのは、INSTRの仕様上、1つ以上の引数がバイナリ文字列である場合にのみ大文字と小文字が区別するので、CASTしないと大文字、小文字を区別して検索できないためです。

下記のようなケースです。

SQLは下記のようになります。

SELECT * FROM テーブル WHERE INSTR(CAST('名前 田中二郎様' AS BINARY), CAST(name AS BINARY)) > 0;

INSTRを使用したのは、LIKEでの部分一致検索だと特殊文字を含む場合に意図した情報が取得できなかったためです。

例えば、名前に「_(アンダーバー)」を含むデータを検索する場合、SQLで書くと下記のようになると思っていました。

SELECT * FROM テーブル WHERE name LIKE '%_%';

これは、名前に_(アンダーバー)を含むデータを検索しようとしているのですが、実際に検索するとテーブルの全件が取得できてしまいます。

以下公式からの引用ですが、

SQL のパターンマッチングを使用すると、「_」で任意の単一の文字、「%」で任意の数の文字 (ゼロ文字を含む) に一致させることができます。

_(アンダーバー)は意味ある記号として処理されてしまうみたいです。

もし、LIKEを仕様するのであれば、エスケープする必要があるので下記のようになります。

SELECT * FROM テーブル WHERE name LIKE '%\_%';

ちなみに、INSTRを使用する場合は下記のようになります。

SELECT * FROM development.users WHERE INSTR(CAST(name AS BINARY), CAST('_' AS BINARY)) > 0;

INSTR、LIKEの部分一致検索について触れましたが、LIKEを使用する場合は、特殊文字を意識して使用する必要があることを忘れないようにしましょう。