MySQLのREGEXP演算子でBINARYキーワードを使う理由とは？大文字小文字を区別するマッチングの解説

MySQLのREGEXP演算子にBINARYキーワードを付けると、対象の文字列がバイナリ文字列として扱われ、大文字と小文字を厳密に区別するマッチングが行われます。BINARYを指定しない場合は、照合順序（collation）に従うため、多くの場合アルファベットの大文字・小文字は区別されません。ここでは、両者の違いを具体的な例で確認していきましょう。

サンプルテーブルの作成

まず、以下のようにテーブルを作成します。

mysql> create table DemoTable
-> (
-> Name varchar(100)
-> );
Query OK, 0 rows affected (0.46 sec)

レコードの挿入

INSERT文を使い、大文字・小文字の組み合わせが異なる複数の名前を登録します。

mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoTable values('JOHN');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable values('john');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable values('JOhn');
Query OK, 1 row affected (0.17 sec)

登録データの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

出力結果

+------+
| Name |
+------+
| John |
| JOHN |
| john |
| JOhn |
+------+
4 rows in set (0.00 sec)

REGEXPとREGEXP BINARYの違いを検証する

次のクエリでは、同じカラムに対してBINARYキーワードなしのREGEXPと、BINARYキーワード付きのREGEXPを並べて実行し、結果を比較しています。

mysql> select Name REGEXP 'JOHN' AS ResultWithOutBinary,
    ->        Name REGEXP BINARY 'JOHN' AS ResultWithBinary
    -> from DemoTable;

出力結果

+---------------------+------------------+
| ResultWithOutBinary | ResultWithBinary |
+---------------------+------------------+
| 1                   | 0                |
| 1                   | 1                |
| 1                   | 0                |
| 1                   | 0                |
+---------------------+------------------+
4 rows in set (0.04 sec)

結果の解説

BINARYキーワードなしの場合：「John」「JOHN」「john」「JOhn」のすべてがパターン「JOHN」にマッチし、結果は「1」となります。これは、デフォルトの照合順序では大文字と小文字が区別されないためです。

BINARYキーワードありの場合：文字列がバイト単位のバイナリデータとして比較されるため、綴りだけでなく大文字・小文字まで完全に一致する「JOHN」だけが「1」を返し、それ以外は「0」になります。

このように、REGEXPで正規表現マッチングを行う際に大文字と小文字を厳密に区別したいケースでは、BINARYキーワードを指定することで期待どおりの結果を得られます。ユーザーIDやパスワードなど、ケースセンシティブな文字列を扱う場合には特に有用なテクニックです。