MySQL에서 대소문자를 구분하는 데이터 정렬(COLLATE)로 열 변환하는 방법

MySQL에서 문자열을 비교할 때 기본 데이터 정렬(collation)은 대소문자를 구분하지 않는 경우가 많습니다. 하지만 COLLATE 절과 utf8_bin 데이터 정렬을 함께 사용하면 대소문자를 엄격하게 구분하는 비교가 가능합니다.

기본 문법

select * from yourTableName where yourColumnName LIKE yourValue COLLATE utf8_bin;

예제 테이블 생성

먼저 예제에 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable
    (
    LastName varchar(100)
    );
Query OK, 0 rows affected (0.51 sec)

데이터 삽입

insert 명령을 사용하여 대소문자 조합이 다른 여러 레코드를 삽입합니다.

mysql> insert into DemoTable values('Brown');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('BROWN');
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable values('brown');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('BRoWN');
Query OK, 1 row affected (0.14 sec)

전체 레코드 조회

select 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select *from DemoTable;

출력 결과

+----------+
| LastName |
+----------+
| Brown    |
| BROWN    |
| brown    |
| BRoWN    |
+----------+
4 rows in set (0.00 sec)

COLLATE를 활용한 대소문자 구분 검색

이제 COLLATE utf8_bin을 적용한 쿼리를 실행합니다. 이진(binary) 데이터 정렬은 각 문자의 바이트 값을 그대로 비교하므로 대소문자 차이를 완전히 구분합니다.

mysql> select *from DemoTable where LastName LIKE 'BROWN' COLLATE utf8_bin;

출력 결과

+----------+
| LastName |
+----------+
| BROWN    |
+----------+
1 row in set (0.00 sec)

위 출력 결과를 보면 'Brown', 'brown', 'BRoWN'은 제외되고 정확히 일치하는 'BROWN'만 반환된 것을 확인할 수 있습니다. 이처럼 COLLATE utf8_bin을 사용하면 별도의 함수나 인덱스 변경 없이도 손쉽게 대소문자를 구분하는 검색을 구현할 수 있습니다.