DB

MySQL上で英大文字小文字を区別して検索できるようにしてみた

以下の記事に記載したように、MySQLの場合、英大文字小文字を区別しないで検索を行う。

デフォルト設定の各DB上で英大文字小文字を区別して検索するかどうか調べてみたデータベースの種類によっては、英大文字小文字を区別して検索するものもあれば、区別しないで検索するものもある。 今回は、Oracle...

今回は、SQL・テーブルの列で英大文字小文字を区別して検索するよう変更してみたので、その結果を共有する。

前提条件

下記記事の前提条件を満たしていること。

デフォルト設定の各DB上で英大文字小文字を区別して検索するかどうか調べてみたデータベースの種類によっては、英大文字小文字を区別して検索するものもあれば、区別しないで検索するものもある。 今回は、Oracle...

やってみたこと

  1. SQLによる照合順序変更指定
  2. テーブルの列による照合順序変更指定

SQLによる照合順序変更指定

SQL文で英大文字小文字を区別するよう変更するには、照合順序を変更したい条件句にBINARY演算子を付与する。その実行結果は以下の通りで、英大文字小文字を区別して検索することが確認できる。

1) 抽出対象となるテーブルのデータは、以下の通り。

SQLによる照合順序変更指定_1

2) 照合順序を変更したい条件句にBINARY演算子を加えて実行した結果は、以下の通り。

SQLによる照合順序変更指定_2_1

SQLによる照合順序変更指定_2_2

SQLによる照合順序変更指定_2_3



テーブルの列による照合順序変更指定

テーブルの列定義で英大文字小文字を区別するよう変更するには、alter table文のmodify句の末尾に「 character set binary」を付与する。その実行結果は以下の通りで、英大文字小文字を区別して検索することが確認できる。なお、テーブルに対しては照合順序変更指定ができないので注意すること。

1) 抽出対象となるテーブルのデータは、以下の通り。

テーブルの列による照合順序変更指定_1

2) テーブルの列定義で英大文字小文字を区別するよう変更するSQLの実行結果は、以下の通り。

テーブルの列による照合順序変更指定_2_1

なお、上記コマンドで指定する文字コード「utf8mb4_bin」は、以下のようにMySQLのコマンドラインを起動した後で、「SHOW VARIABLES LIKE ‘char%’」コマンドで「character_set_server」の設定値をみることで確認している。

テーブルの列による照合順序変更指定_2_2 テーブルの列による照合順序変更指定_2_3

3) テーブルの列定義変更後のSQL実行結果は、以下の通りで、英大文字小文字を区別して検索することが確認できる。

テーブルの列による照合順序変更指定_3_1

テーブルの列による照合順序変更指定_3_2

テーブルの列による照合順序変更指定_3_3

なお、前述の2)で実行したコマンドは、alter table modify句の末尾に「binary」を付与することでも英大文字小文字の区別ができるようになるが、以下のように警告メッセージが表示される。

テーブルの列による照合順序変更指定_3_4

<警告メッセージの内容>

3 row(s) affected, 1 warning(s): 1287 ‘BINARY as attribute of a type’ is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead Records: 3 Duplicates: 0 Warnings: 1

要点まとめ

  • MySQLでは英大文字小文字を区別しないで検索するが、SQLでBINARY演算子を付与するか、alter table文のmodify句の末尾に「character set binary」を付与し列定義を変更することで、英大文字小文字を区別して検索を行うようになる。