以下の記事に記載したように、MySQLの場合、英大文字小文字を区別しないで検索を行う。
今回は、SQL・テーブルの列で英大文字小文字を区別して検索するよう変更してみたので、その結果を共有する。
前提条件
下記記事の前提条件を満たしていること。
やってみたこと
SQLによる照合順序変更指定
SQL文で英大文字小文字を区別するよう変更するには、照合順序を変更したい条件句にBINARY演算子を付与する。その実行結果は以下の通りで、英大文字小文字を区別して検索することが確認できる。
1) 抽出対象となるテーブルのデータは、以下の通り。
1 | select * from user_data |
2) 照合順序を変更したい条件句にBINARY演算子を加えて実行した結果は、以下の通り。
1 | select * from user_data where memo like binary'TEST%' |
1 | select * from user_data where memo like binary'test%' |
1 | select * from user_data where memo like binary'Test%' |
テーブルの列による照合順序変更指定
テーブルの列定義で英大文字小文字を区別するよう変更するには、alter table文のmodify句の末尾に「 character set binary」を付与する。その実行結果は以下の通りで、英大文字小文字を区別して検索することが確認できる。なお、テーブルに対しては照合順序変更指定ができないので注意すること。
1) 抽出対象となるテーブルのデータは、以下の通り。
1 | select * from user_data |
2) テーブルの列定義で英大文字小文字を区別するよう変更するSQLの実行結果は、以下の通り。
1 2 | alter table user_data modify memo varchar(1024) character set utf8mb4 collate utf8mb4_bin |
なお、上記コマンドで指定する文字コード「utf8mb4_bin」は、以下のようにMySQLのコマンドラインを起動した後で、「SHOW VARIABLES LIKE ‘char%’」コマンドで「character_set_server」の設定値をみることで確認している。
1 | SHOW VARIABLES LIKE 'char%' |
3) テーブルの列定義変更後のSQL実行結果は、以下の通りで、英大文字小文字を区別して検索することが確認できる。
1 | select * from user_data where memo like 'TEST%' |
1 | select * from user_data where memo like 'test%' |
1 | select * from user_data where memo like 'Test%' |
なお、前述の2)で実行したコマンドは、alter table modify句の末尾に「binary」を付与することでも英大文字小文字の区別ができるようになるが、以下のように警告メッセージが表示される。
1 | alter table user_data modify memo varchar(1024) binary |
＜警告メッセージの内容＞
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」を付与し列定義を変更することで、英大文字小文字を区別して検索を行うようになる。
