【MySQL】大文字小文字を区別して検索したい

f:id:y_hakoiri:20191120232646j:plain

VARCHAR型のカラムについて、WHERE句での一致検索で大文字と小文字を区別してくれないことに気づいた。

mysql> SELECT id, name FROM clients;
+----+------+
| id | name |
+----+------+
|  1 | tesT |
|  2 | TEST |
|  3 | test |
+----+------+
3 rows in set (0.00 sec)

こんなレコードがあるときに、

mysql> SELECT id, name FROM clients WHERE name = "test";
+----+------+
| id | name |
+----+------+
|  1 | tesT |
|  2 | TEST |
|  3 | test |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT id, name FROM clients WHERE name LIKE "%test%";
+----+------+
| id | name |
+----+------+
|  1 | tesT |
|  2 | TEST |
|  3 | test |
+----+------+
3 rows in set (0.00 sec)

デフォルトでは大文字と小文字の違いを区別してくれない。

BINARY演算子

VARCHAR型のカラムはデフォルトでは与えられた文字セットでの比較を行う。

大文字と小文字の区別をして絞り込みをしたいときはBINARY演算子を使用する。

mysql> SELECT id, name FROM clients WHERE name = BINARY "test";
+----+------+
| id | name |
+----+------+
|  3 | test |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT id, name FROM clients WHERE name LIKE BINARY "test";
+----+------+
| id | name |
+----+------+
|  3 | test |
+----+------+
1 row in set (0.00 sec)

BINARY演算子はそれに続く文字列をバイナリ文字列にキャストする。比較を文字ごとでなくバイトごとに強制的に実行させるので大文字小文字の区別ができるようになる。

空白文字の比較

MySQL 内のすべての CHAR、VARCHAR、および TEXT 値が、末尾のスペースに関係なく比較される

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.1 CHAR および VARCHAR 型

バイナリではなく文字列での比較の場合、空白が含まれていても一致してしまうが、これもバイナリ文字列にキャストすれば一致しなくなる。

# 文字列での比較（空白文字含む）
mysql> SELECT id, name FROM clients WHERE name = "test ";
+----+------+
| id | name |
+----+------+
|  1 | tesT |
|  2 | TEST |
|  3 | test |
+----+------+
3 rows in set (0.00 sec)

# バイナリ文字列での比較（空白文字含む）
mysql> SELECT id, name FROM clients WHERE name = BINARY "test ";
Empty set (0.00 sec)

mysql> SELECT id, name FROM clients WHERE name = BINARY "test";
+----+------+
| id | name |
+----+------+
|  3 | test |
+----+------+
1 row in set (0.00 sec)