Collation에 따라 LIKE와 '=' 검색결과가 달라질 수 있다

 지금 진행하고 있는 프로젝트에서 DB의 Collation 관련한 문제가 생겨서 정리해둔다.

---

문제상황

 현재 하고 있는 프로젝트는 고도화 프로젝트인데, 기존 테이블의 Collation은 utf8mb4_general_ci이고, 새로운 테이블의 Collation은 utf8mb4_unicode_ci이다. 두 테이블의 컬럼을 비교해야 하기도 하고 관리의 용이함을 위하여 기존 테이블의 Collation도 utf8mb4_unicode_ci로 변경하기로 했다.

 그래서 변경을 하던 중, 특수문자를 다루는 테이블에서 문제가 발생했다.

 문제가 발생한 컬럼은 Unique 설정이 되어 있었는데, 원래 utf8mb4_general_ci에서는 서로 다르다고 판단했던 데이터가 utf8mb4_unicode_ci로 변경하면서 같다고 판단되었기 때문이다. 문제의 데이터가 바로 '㏅'와 'cd'였다. 자세히 보면 좀 다른데, 앞의 ㏅는 완성형 기호이고, 뒤의 cd는 그냥 키보드로 입력한 cd이다.

 처음엔 검색 필터를 이용하는 기능에는 신기하게도(?) 문제가 없이 '㏅'로 검색하면 '㏅'의 데이터만 나오고, 'cd'로 검색하면 'cd'의 데이터만 나와서 이런 저런 검토 끝에 Unique 설정만 없애려고 했는데, 문제가 또 발생했다.

---

LIKE 검색과 '=' 검색 결과가 다르다

 제목 그대로 LIKE 검색과 '='검색의 결과가 다르다는 문제였다. 검색 필터를 이용하는 기능은 LIKE로 검색을 하고, 중복된 데이터가 있을 때는 '='로 검색을 하는데, 이 둘의 결과가 달랐다.

create database collation_test;
use collation_test;
create table collation_test(
    txt varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci
);

insert collation_test values ('㏅');
insert collation_test values ('cd');

 위와 같이 테스트용 테이블을 만들고 LIKE 검색을 하면 아래와 같은 결과가 나온다.

 아까와 같이 '='로 비교했을 때와는 다르게 오로지 자기자신과 같은 데이터만 검색결과로 나오는 것을 알 수 있다. 아래는 '='로 비교한 결과이다.

 LIKE 검색을 했을 때와는 전혀 다르게 두 데이터가 전부 나온다. (LIKE문과 '='의 차이겠지만 더 자세하게 왜 그런지는 아직 모르겠다.) 그리고 좀 더 살펴보니 알파벳으로 이루어진 완성형 기호(예를 들어, ㎉나 ㎜)는 전부 다 해당하는 것 같다.

---

결론

 특수문자를 꼭 정밀하게 다뤄야할 경우에는 binary로 비교할 수도 있다. 아래와 같이 binary로 비교하면 두 데이터가 다르다는 결과를 얻을 수 있다.

 그렇지만 가장 좋은 것은 DB를 만들 때 어떤 collation을 사용할지 잘 생각해보고 collation을 결정하는 것이다.

 위와 같은 경우도 utf8mb4_general_ci에서는 문제가 없었다.

원래 utfmb4_general_ci로 되어 있었던 테이블이고, utfmb4_unicode_ci로 변경할 경우 위와 같은 문제가 발생할 수 있음을 확인했다. 그래서 아무래도 저 테이블만 utfmb4_general_ci로 유지해야 할 것 같다.

 collation의 차이에 따라 같은 문자로 보기도 하고 다른 문자로 보기도 한다는 것은 알고 있었는데 이번 기회에 실제로 접해보며 확실히 배울 수 있었다.