[MYSQL] Illegal mix of collations utf8mb4_general_ci 에러

새로운 프로젝트를 진행하게되면 이전 프로젝트를 가지고 와서 다시 시작하는 경우가 많습니다.

이것저것 추가하고 제거하고 버전 업데이트하고 그러다보면 때때로 이러한 에러들이 발생합니다.

"Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='  등

원인

 

두 개의 문자열을 비교하거나 조작할 때 발생하는 에러로, 두 문자열의 정렬 방식(collation)이 서로 다를 때 나타납니다. Collation은 문자열을 정렬하고 비교하는 방법을 정의하는 규칙 세트입니다.

 

이를 해결하기 위해 동일한 collation을 사용하도록 문자열을 변환할 수 있습니다. 다음은 몇 가지 해결 방법입니다:

 

해결방법

1. 데이터베이스의 기본 character set과 collation을 변경

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 

※  데이터베이스의 collation을 변경하는 것은 새로운 테이블과 컬럼에 대한 기본 collation을 설정하지만, 이미 존재하는 테이블과 컬럼의 collation을 자동으로 변경하지는 않습니다. 따라서 기존 테이블과 컬럼의 collation을 수동으로 변경해야 합니다.

2. 각 테이블의 collation을 변경하는 쿼리 생성 및 실행

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DB_NM' AND TABLE_TYPE = 'BASE TABLE';

 

DB_NM에 올바른 이름을 넣고 쿼리를 실행하면 존재하는 모든 테이블을 변경하는 쿼리를 생성해줍니다.

그렇다면 아래처럼 쿼리들을 생성해주는데 CSV형태로 변환해서  복사해서 실행하던 본인만의 방식으로 진행하면 됩니다.

 

ALTER TABLE 테이블1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE 테이블2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;