[Database] MySQL 문자열 대소문자 구분
사용자의 검색 키워드와 그에 따른 결과를 보기 위해서 검색 기능을 개발하는 중 이상한 점을 발견했다.
검색 키워드를 넣고 검색을 하게 되면 아래와 같이 대소문자가 구분이 되지 않는 상태로 결과가 조회된다. MySQL 세팅 시, 대소문자 구분하지 않는 설정을 안했는데 왜 'apple'을 검색키워드로 넣었는데 왜, 'Apple', 'APPLE333'이 조회가 되는걸까?
1. Application Level 분석 (Querydsl)
먼저 대소문자 구분 문제를 확인할 때, 데이터베이스 레벨보다는 애플리케이션 레벨에서의 쿼리 및 ORM(Querydsl) 설정을 살펴보았다.
애플리케이션 레벨에서 문제를 수정하는 것이 일반적으로 더 간단하고 빠르고, 쿼리나 조건을 수정하는 것이 데이터베이스 설정을 변경하는 것보다 용이 하다. 또한, 데이터베이스 설정을 변경하면 다른 애플리케이션이나 쿼리에 영향을 줄 수 있기에 이렇게 문제 파악 순서를 정했다.
private BooleanBuilder searchExpression(List<String> searchKeywords) {
BooleanBuilder builder = new BooleanBuilder();
if (searchKeywords != null) {
for (String search : searchKeywords) {
if (!ObjectUtils.isEmpty(search)) {
builder.or(comment.textOriginal.contains(search));
builder.or(JPAExpressions.selectOne()
.from(reply)
.where(reply.textOriginal.contains(search)
.and(reply.comment.id.eq(comment.id))
)
.exists());
}
}
}
return builder;
}
searchKeyword를 분석할 때, contains (또는 %like%) 구문에서 대소문자를 설정을 위해 컬럼 생성 또는 수정 시 옵션을 설정하지 않더라도 문자열 SELECT 쿼리를 실행할 때의 지정하는 방법을 생각해내었다.
애플리케이션 레벨에서 문제를 분석하고 수정하는 것은 운영 중인 DB의 리스크를 줄이고 안정성을 유지하는 데 유리하다고 판단하였다.
하지만, Querydsl에서 대소문자 구분을 명시적으로 처리하는 기능은 제공하지 않는다. Querydsl에서는 COLLATE를 직접 사용할 수 있는 기능이 제한적이라고 한다. 만약 대소문자 구분을 위해 COLLATE를 사용하고 싶다면, SQL을 직접 작성하거나, QueryDSL의 Expressions를 활용하여 커스텀 SQL을 작성해야 한다. (native 쿼리는 최대한 피하고 싶었다. JPA를 사용하여 애플리케이션 전반에 걸쳐 데이터 액세스 방식이 일관되게 유지)
2. DataBase Level 분석 (MySQL)
Database의 테이블의 컬럼이 어떤 문자 집합과 정렬 방식인지 확인하기 위해 SHOW FULL COLUMNS 명령을 사용하였다.
Type이 longtext인 필드의 Collation은 utf8mb4_unicode_ci로 설정이 되어있었다.
utf8mb4_0900_ai_ci 정렬 방식에서는 대소문자를 구분하지 않는다. 이 정렬 방식은 악센트에 대해 구분하지 않으며, 대소문자도 구분하지 않기 때문에 대소문자 구분을 위한 COLLATE 변경해야 한다.
대소문자를 구분하려면 utf8mb4_bin 정렬 방식을 사용할 수 있다. utf8mb4_bin은 이진 비교를 사용하여 대소문자를 구분한다.
ALTER TABLE reply
CHANGE text_original text_original LONGTEXT COLLATE utf8mb4_bin null;
그럼 여기서 utf8mb4 뒤에 붙는 값들은 뭘까?
- 0900
버전 정보: 0900은 MySQL 8.0에서 도입된 유니코드 버전인 Unicode 9.0을 나타낸다. 이 정렬 방식이 Unicode 9.0 기준에 따라 문자 정렬 및 비교를 수행한다는 것을 의미한다. - ai
ai는 "Accent Insensitive"의 약자로, 악센트를 구분하지 않는다는 의미다. 예를 들어, é와 e는 동일한 것으로 간주된다. 이 정렬 방식에서는 악센트가 있는 문자와 없는 문자를 동일한 것으로 처리하여 비교한다. - ci
ci는 "Case Insensitive"의 약자로, 대소문자를 구분하지 않는다는 의미다. 예를 들어, A와 a는 동일한 것으로 간주된다. 이 정렬 방식에서는 대소문자에 관계없이 비교한다. - _bin은 문자 집합에 대한 이진 비교 방식으로 _bin이 없으면 대소문자를 구분하지 않고, 악센트에 대해서도 구분하지 않는다.
또한 댓글 또는 답글에는 이모지(이모티콘)도 텍스트에 포함될 수 있기 때문에 이모지와 대소문자 구분이 모두 필요하다면, utf8mb4_bin 정렬 방식을 사용하는 것이 최적의 선택인 것으로 보인다.
재시도
정렬방식이 변경되었다. 다시 시도해보자.
정상적으로 결과가 조회된다.
되돌아보기
문제를 해결하면서 생길 수 있는 의문점에 대해서 점검해보자.
- UTF-8과 이모지(🤨) 저장
MySQL에서 utf8로 알려진 문자 집합은 3바이트 문자를 지원한다. 따라서 이모지와 같은 4바이트 문자는 저장할 수 없기 때문에 utf8mb4를 사용하였다. (정확하게는 utf8mb4_bin) - utf8mb4의 mb4
mb4는 multi-byte 4 bytes의 약자로, 최대 4바이트까지 지원하는 문자 집합을 의미한다. 즉, utf8mb4는 4바이트 문자를 포함하여 모든 유니코드 문자를 저장할 수 있다. 이모지와 같은 4바이트 문자가 포함된 다양한 문자를 지원한다. - COLLATE
Collation은 문자열 비교 및 정렬 방식에 대한 규칙을 정의한다. 즉, 문자열을 비교할 때 대소문자 구분 여부, 악센트 구분 여부 등을 설정한다.
utf8mb4_general_ci: 대소문자와 악센트를 구분
utf8mb4_bin: 대소문자와 악센트를 구분 (이진 비교).
utf8mb4_0900_ai_ci: 대소문자는 구분하지 않지만, 악센트는 구분 - Longtext vs. Binary Type
MySQL에서 LONGTEXT는 대용량 문자열을 저장하기 위한 데이터 타입이다. 이모지와 같은 다양한 문자를 저장할 수 있지만, 대소문자 비교는 collation에 따라 다르다.
필자가 Longtext를 사용한 이유는 비즈니스적인 측면에서의 이유이다. 댓글 또는 답글은 텍스트의 길이에 제한을 두지 않기 위해 char 또는 varchar을 사용하지 않았다.
BINARY 또는 VARBINARY 데이터 타입은 이진 데이터를 저장하는 데 사용된다. 이진 데이터는 문자 집합과 관계없이 항상 정확하게 비교되므로, 대소문자 구분이 필요한 경우에 사용된다. 하지만, BINARY는 문자셋을 사용하지 않으므로, 텍스트 데이터를 저장할 경우 문자 인코딩 문제가 발생할 수 있다.