MySQL에서 대소문자 구분 고유성과 대소문자 무관 검색을 동시에 구현하는 방법

MySQL에서는 대소문자를 구분하는 고유성(Unique) 제약과 대소문자를 구분하지 않는 검색을 동시에 만족시켜야 하는 경우가 종종 있습니다. 예를 들어 'John'과 'john'은 서로 다른 사용자로 저장하되, 검색 시에는 같은 결과로 조회하고 싶은 상황입니다.

이러한 요구사항은 다음 두 가지 방법으로 해결할 수 있습니다.

VARBINARY 데이터 타입 사용
_bin 콜레이션(Collation) 사용

방법 1: VARBINARY 데이터 타입

VARBINARY는 바이트 단위로 값을 비교하기 때문에 자동으로 대소문자를 구분하여 저장합니다. 먼저 테이블을 생성해 보겠습니다.

mysql> create table SearchingDemo2
   -> (
   -> UserId VARBINARY(128) NOT NULL,
   -> UNIQUE KEY index_on_UserId2(UserId)
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 1 warning (0.99 sec)

여기서 주목할 점은 UserId 컬럼의 데이터 타입이 VARBINARY(128)라는 것이며, 해당 컬럼에 고유 인덱스 'index_on_UserId2'가 설정되어 있다는 것입니다. 바이트 단위 비교 방식 덕분에 'John'과 'john'은 서로 다른 값으로 인식되어 중복 저장이 허용됩니다.

방법 2: _bin 콜레이션

두 번째 방법은 컬럼 수준에서 이진(binary) 콜레이션인 utf8_bin을 지정하는 것입니다. 새로운 테이블을 생성해 보겠습니다.

mysql> create table SearchingDemo
   -> (
   -> UserId varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   -> UNIQUE KEY index_on_UserId(UserId)
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.88 sec)

UserId 컬럼은 varchar(128) 타입이면서 CHARACTER SET과 COLLATE 옵션으로 utf8_bin이 적용되어 있고, 고유 인덱스 'index_on_UserId'가 설정되어 있습니다. utf8_bin 콜레이션은 문자를 이진값으로 비교하므로 대소문자가 다른 값들을 서로 다른 값으로 처리합니다.

대소문자 무관 검색 수행하기

위 두 방식으로 저장된 데이터는 대소문자를 구분하지만, 검색 시에는 LOWER() 함수나 UPPER() 함수를 활용하면 됩니다.

SELECT * FROM SearchingDemo WHERE LOWER(UserId) = LOWER('John');

또는 테이블 전체가 utf8_unicode_ci처럼 대소문자를 구분하지 않는 콜레이션을 사용하는 경우, 일반적인 WHERE 조건 검색은 기본적으로 대소문자를 구분하지 않고 동작합니다.

결론

위에서 소개한 두 가지 방법, 즉 VARBINARY 데이터 타입과 _bin 콜레이션을 활용하면 MySQL에서 대소문자를 구분하는 고유성 제약과 대소문자를 구분하지 않는 유연한 검색 기능을 동시에 구현할 수 있습니다. 상황에 따라 VARBINARY는 저장 공간과 성능 면에서, utf8_bin은 문자셋 호환성 면에서 각각 장점을 가지므로 프로젝트 요구사항에 맞게 선택하면 됩니다.