MySQL 대소문자 구별

✔️ MySQL 5.5, Collation: utf8_general_ci에서 테스트하였습니다.

MySQL case sensitive query
MySQL 대소문자 구별

1 문제상황[ | ]

MySQL은 기본적으로 대소문자 구별을 하지 않는다.

mysql> SELECT * FROM ip2nationCountries WHERE country LIKE '%Uni%';
+------+------------+------------+----------------------+----------------------+--------+-------+
| code | iso_code_2 | iso_code_3 | iso_country          | country              | lat    | lon   |
+------+------------+------------+----------------------+----------------------+--------+-------+
| ae   | AR         | ARE        | United Arab Emirates | United Arab Emirates |     24 |    54 |
| re   | RE         | REU        | Reunion              | Reunion              | -21.06 | 55.36 |
| tn   | TU         | TUN        | Tunisia              | Tunisia              |     34 |     9 |
| uk   | GB         | GBR        | United Kingdom       | United Kingdom       |     54 |    -2 |
| us   | US         | USA        | United States        | United States        |     38 |   -97 |
+------+------------+------------+----------------------+----------------------+--------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM ip2nationCountries WHERE country LIKE '%uni%';
+------+------------+------------+----------------------+----------------------+--------+-------+
| code | iso_code_2 | iso_code_3 | iso_country          | country              | lat    | lon   |
+------+------------+------------+----------------------+----------------------+--------+-------+
| ae   | AR         | ARE        | United Arab Emirates | United Arab Emirates |     24 |    54 |
| re   | RE         | REU        | Reunion              | Reunion              | -21.06 | 55.36 |
| tn   | TU         | TUN        | Tunisia              | Tunisia              |     34 |     9 |
| uk   | GB         | GBR        | United Kingdom       | United Kingdom       |     54 |    -2 |
| us   | US         | USA        | United States        | United States        |     38 |   -97 |
+------+------------+------------+----------------------+----------------------+--------+-------+
5 rows in set (0.00 sec)

→ 대문자로 검색하든, 소문자로 검색하든 상관없이 5건 나옴

2 해결방법[ | ]

WHERE 절에 BINARY를 추가해주면 된다.

mysql> SELECT * FROM ip2nationCountries WHERE BINARY country LIKE '%Uni%';
+------+------------+------------+----------------------+----------------------+-----+-----+
| code | iso_code_2 | iso_code_3 | iso_country          | country              | lat | lon |
+------+------------+------------+----------------------+----------------------+-----+-----+
| ae   | AR         | ARE        | United Arab Emirates | United Arab Emirates |  24 |  54 |
| uk   | GB         | GBR        | United Kingdom       | United Kingdom       |  54 |  -2 |
| us   | US         | USA        | United States        | United States        |  38 | -97 |
+------+------------+------------+----------------------+----------------------+-----+-----+
3 rows in set (0.06 sec)

→ 대문자 U가 있는 3건만 나옴

mysql> SELECT * FROM ip2nationCountries WHERE BINARY country LIKE '%uni%';
+------+------------+------------+-------------+---------+--------+-------+
| code | iso_code_2 | iso_code_3 | iso_country | country | lat    | lon   |
+------+------------+------------+-------------+---------+--------+-------+
| re   | RE         | REU        | Reunion     | Reunion | -21.06 | 55.36 |
| tn   | TU         | TUN        | Tunisia     | Tunisia |     34 |     9 |
+------+------------+------------+-------------+---------+--------+-------+
2 rows in set (0.00 sec)

→ 소문자 u가 있는 2건만 나옴

3 같이 보기[ | ]

4 참고[ | ]

http://stackoverflow.com/questions/7857669/mysql-case-sensitive-query