sql like query할 때 특정 한글이 포함된 값들을 찾으려고 했다.
SET @keyword = CONCAT('%', '가나다', '%');
SELECT *
FROM a
WHERE name LIKE @keyword다음과 같은 에러가 나는 이유는 mysql에서 like 검색을 할 때 사용되는 두 문자 집합(collations)가 다를 때 발생한다. 문자 집합은 문자를 어떻게 비교할지에 대한 규칙을 정의한 것임. utf8mb4_unicode_ci와 utf8mb4_general_ci가 함께 사용되어 문제가 생겼다. DB connection을 할 때 DB url 뒤에 ?charset=utf8mb4 이 지정되어 있는 상태였다.
[HY000][1267] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'like';
utf8mb4_unicode_ci와 utf8mb4_general_ci는 utf8mb4 문자 인코딩을 사용하지만, 문자열 비교 시 대소문자 구분 여부나 문자 정렬 방식이 다르다.
utf8mb4 : mysql에서 utf8 인코딩을 지원하지만 최대 3바이트까지의 문자를 지원한다. 4바이트 문자를 지원하는 문자 인코딩 방식이다. UTF-8 multibyte 4를 의미함. 문자 하나를 표현하기 위해 최대 4바이트까지 사용할 수 있다. 이 인코딩 방식을 사용하면 mysql에서도 모든 유니코드 문자(이모지, 다양한 언어, 특수문자)를 제대로 저장하고 처리할 수 있다.
해결하기 위해선 name 컬럼과 @keyword 변수의 문자 집합(collation)을 일치시켜야 한다. COLLATE 키워드를 사용해서 collation을 명시적으로 지정한다.
SET @keyword = CONCAT('%', '가나다', '%');
SELECT *
FROM a
WHERE name COLLATE utf8mb4_unicode_ci LIKE @keyword;또는 DB/테이블/컬럼의 인코딩 방식을 수정해야 한다.
- 실행 전 반드시 데이터를 백업하고 데이터가 잘 유지되는지 테스트를 해야한다.
- 실행 시간이 오래 걸릴 수 있다.
- 앱에서도 테스트해보고 필요하다면 인코딩 방식을 수정해야 한다.
ALTER DATABASE 데이터베이스이름
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
ALTER TABLE 테이블이름
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
ALTER TABLE 테이블이름
CHANGE 컬럼이름 컬럼이름 데이터타입
COLLATE utf8mb4_unicode_ci;