MySQL에서 문자열 검색시 대소문자 처리

MySQL에서 select 쿼리 수행시 신기(?)한 부분을 발견해서 정리해 본다.

들어가며...

(Ver. 5.6)

MySQL에서 테이블명 또는 컬럼명에서는 대소문자를 구별한다는 것은 잘 알려진 사실이다.

그 이유는 테이블 또는 컬럼명이 파일로 관리되기 때문인데, 윈도우에서는 이슈가 없으나 Unix 계열에서는 대소문자 구분이 필요하기 때문이다. 그렇다면 문자열 컬럼(VARCHAR, CHAR, TEXT 등)에 저장된 내용은 어떻게 될까?

문자열 컬럼에서 대소문자 처리

아래와 같이 id, name 컬럼을 가진 user 테이블이 있다고 가정하자.

CREATE TABLE `user` (   `id` varchar(20)DEFAULT NULL,   `name` varchar(100) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

데이터 입력

insert into user(id, name) values ('test', 'test'); insert into user(id, name) values ('TEST', 'TEST’);

id=‘test’인 row를 조회해 보자.

select * from user where id = 'test';

대문자 TEST도 함께 조회가 된다. 그럼 소문자 test만 조회하려면 어떻게 할까?

먼저 MySQL 문자열 타입에는 두 가지 카테고리로 구분할 수 있다.

• nonbinary: CHAR, VARCHAR, TEXT
• binary: BINARY, VARBINARY, BLOB

MySQL 공식 문서에서는 아래와 같이 언급하고 있다.

For nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands. For binary strings (BINARY,VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.

즉, 위에서 테이블을 생성할 때 VARCHAR로 생성했기 때문에 문자열 검색시 대소문자를 구분하지 않는다.

만약 구분하고자 한다면 binary 타입을 활용해야 한다.

BINARY 타입 테스트

CREATE TABLE `user2` (   `id` varbinary(20) DEFAULT NULL,   `name` varchar(100) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user2(id, name) values ('test', 'test'); insert into user2(id, name) values ('TEST', 'TEST’);

select * from user2 where id = 'test';

varbinary로 설정을 하면 대소문자를 구분하여 조회를 할 수 있다.

하지만 결과 캡쳐화면에서도 확인할 수 있듯이 BLOB으로 처리됨을 확인할 수 있다. BLOB을 일반적인 문자열로 변환하기에는 또 다른 작업이 필요하므로 불편할 수 있다.

이럴 때에는 컬럼에 binary 옵션을 주도록 하자.

CREATE TABLE `user3` (   `id` varchar(20) binary DEFAULT NULL,   `name` varchar(100) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user3(id, name) values ('test', 'test'); insert into user3(id, name) values ('TEST', 'TEST');

select * from user3 where id = 'test';

varchar 타입이면서 대소문자를 구분할 수 있게 된다.

꼭 컬럼 생성시 타입이나 옵션을 주지 않더라도 문자열 조회시 지정하는 방법도 있다.

select * from user where id = ‘test’ COLLATE utf8_bin;

위 방법은 문자열 비교 쿼리마다 다 수정해 주어야 하는 단점이 있지만 운영중인 DB의 컬럼 변경시 risk를 줄일 수 있다는 장점도 있다.(application 배포만 하면 적용됨) 

---

참고

• https://dev.mysql.com/doc/refman/5.6/en/case-sensitivity.html
  
• http://deadfile.tistory.com/entry/MySQL%EC%97%90%EC%84%9C%EC%9D%98-%EB%8C%80%EC%86%8C%EB%AC%B8%EC%9E%90-%EC%B2%98%EB%A6%AC