MySQLで大文字小文字を区別する一意性と、区別しない検索を両立させる方法

MySQLでは、カラムの値に対して「大文字と小文字を区別する一意性（ユニーク制約）」を持たせながら、検索時には「大文字と小文字を区別しない」動作を実現できます。主な方法は次の2つです。

• VARBINARYデータ型を使う
• _bin照合順序（COLLATE）を使う

方法1：VARBINARYデータ型を使用する

まずは、VARBINARY型を使ったテーブル作成の例から見ていきましょう。

mysql> create table SearchingDemo2
    -> (
    -> UserId VARBINARY(128) NOT NULL,
    -> UNIQUE KEY index_on_UserId2(UserId)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 1 warning (0.99 sec)

ポイントは、UserIdカラムをVARBINARY(128)として定義し、そのカラムにユニークインデックス（index_on_UserId2）を作成している点です。VARBINARY型はデータをバイナリとして扱うため、「ABC」と「abc」は異なる値として認識されます。その結果、一意制約は大文字と小文字を区別して機能します。

方法2：_bin照合順序（COLLATE）を使用する

次に、utf8_bin照合順序を指定したテーブル作成の例です。

mysql> create table SearchingDemo
    -> (
    -> UserId varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    -> UNIQUE KEY index_on_UserId(UserId)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.88 sec)

こちらの例では、UserIdカラムはvarchar(128)ですが、カラム単位でCOLLATE utf8_binを指定しています。これにより、このカラムに対する比較や一意制約はバイナリ方式（大文字と小文字を区別する方式）で行われます。

検索時の挙動について

テーブル全体のデフォルト照合順序はutf8_unicode_ci（ci＝case insensitive、つまり大文字小文字を区別しない）に設定されているため、WHERE句やLIKEによる検索では大文字と小文字が区別されません。つまり、登録時には「John」と「john」を別々のレコードとして保存できる一方、検索時にはどちらも同じようにヒットさせることができます。

このように、上記のどちらの方法を採用しても、「大文字小文字を区別する一意性」と「大文字小文字を区別しない検索」をMySQLで同時に実現可能です。用途に応じて、バイナリ型を使うか、照合順序だけを変更するかを選択するとよいでしょう。