MySQL의 collation이라는 마법

문제상황

회사에서 법+수사기관 관련된 요건이 들어왔다. 예상한대로 요건은 굉장히 복잡했고, entity 하나에 30개에 육박하는 필드를 가진 무시무시한 녀석이 탄생했다. 여기서 문제는 10개 이상의 필드에 대해서 대소문자 구분없는 검색 을 할 수 있도록 해달라는 것이였다. 나는 필수값이 없으니 index를 잡기도 어렵고, 심지어 대소문자 구분은 lower() 함수를 사용해서 모두 구분해야해서, index를 잡을수도 없다고 생각했다. 그래서 대소문자 구분없이 검색하는 것이 정말로 유용한 필드에서만 적용하는게 어떻겠냐고 제안했었다.

개발 완료 후 QA 중, "다른 필드는 안해주셔도 되는데 대소문자 구분없이 검색 가능하도록 모든 필드에 적용해주셨네요~" 라고 하셨다.

읭?? 나는 그런적이 없는데...? 해서 DB에 직접 sql을 쳐보니... 이게 웬걸!? 너무나 잘되었다...!
나는 또다시 나의 무지함에 감탄하며 어떻게 대소문자 구분없이 동작하는지 찾아보았다.

MySQL의 마법

mysql에서는 기본적으로 CHAR, VARCHAR, TEXT 자료형의 경우 대소문자 구분이 없다고 한다. 대소문자를 구분하고 싶으면 BINARY, VARBINARY, BLOB 자료형을 사용하거나, collation을 변경하라고 합니다. 관련 stackoverflow

음... 기본적으로 대소문자를 구분하지 않는다면, binary 데이터는 분명 값이 다를테니까 BINARY 자료형을 쓰라는거군! 여기까진 이해했다. 그럼 왜 mysql은 기본적으로 대소문자를 구분하지 않는것일까?

• Collation

mysql 공식문서를 확인해보면, CHAR, VARCHAR, TEXT 자료형은 검색 시 collation을 사용한다고 나와있다. collation이 뭔데?

 

mysql collation 공식문서에서, "collation은 character string을 비교, 정렬하는 rule의 집합이다" 라고 설명하고 있다.
mysql에서 collation 설정으로 인해 문자열 비교/정렬 기준이 정해지는 것이고, 이로 인해 대소문자 구분을 하지 않는거구나! 그렇다면 collation의 종류는 어떤게 있을까?

 

collation은 아래 SQL을 실행해보면 어떤 종류가 있는지 알 수 있다.

-- 모든 collation 확인
SHOW COLLATION;

-- utf8mb4의 charset의 collation 확인
SHOW COLLATION LIKE 'utf8mb4%';

이름이 너무 어려운데, 어떻게 구분하면 될까?

뒤에 _ci 가 붙는 애들은, case-insentive 즉 대소문자 구분하지 않는 얘들이다.
자세한건 나중에 더 찾아봐서 추가로 설명하겠다.

 

그럼 mysql에서는 어떤 설정이 기본일까?
mysql은 8.0부터는 charset은 utf8mb4, collation은 utf8mb4_0900_ai_ci 가 기본 설정이다. 그래서 내가 의도하지 않는 대소문자 구분 기능개발이 됐었던 것이다...ㅎ

 

여기서 궁금한점이 생겼다.
mysql이 비교 연산에 쓰이는 로직을 구분하고 있다는 것인데, 그럼 collation별 성능 차이가 존재하지 않을까? 어느정도 차이가 날까?

 

https://www.percona.com/blog/charset-and-collation-settings-impact-on-mysql-performance/
이 블로그를 확인하면 collation과 charset 별, 그리고 mysql 버전에 따른 성능 비교를 확인할 수 있다.

후기

팀원들에게 나의 삽질을 공유했다. 그 때 들어온 질문은 "실제로 모두 소문자가 기록되는건가요?" 였다. db에서 실제로 데이터를 조회해보면 대소문자 구분 있이 주기 때문에 실제 저장의 변화는 없고, 비교/정렬 등의 연산에만 사용되는 기준! 이라고 보면 될 것 같다. (소문자로 저장이 되었다면, db입장에서는 어떤게 대문자였는지 구분조차 하기 어려웠을 것이다)