Mysql - Error Code: 1267. Illegal mix of collations 에러 해결

 

Error Code: 1267. Illegal mix of collations  에러는 문자셋이 맞지 않아서 발생하는 에러 입니다. 

 

에러 발생하는 케이스를 만들어 봅니다.

 

USE database1;
DROP TABLE IF EXISTS tbl_collation_1;

CREATE TABLE tbl_collation_1 (
	seq_no	bigint 		NOT	NULL	AUTO_INCREMENT,
	string1	varchar(20)	NULL,
	PRIMARY KEY (seq_no)
)
ENGINE=InnoDB;

DROP TABLE IF EXISTS tbl_collation_2;
CREATE TABLE tbl_collation_2 (
	seq_no	bigint 		NOT	NULL	AUTO_INCREMENT,
	string2	varchar(20)	NULL,
	PRIMARY KEY (seq_no)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET utf8mb4	COLLATE utf8mb4_general_ci;

두개의 테이블을 만드는데 한개는 tbl_collation_1 은 DB 기본값으로 생성이 됩니다. 

두번째 테이블은 문자셋을 지정(DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci) 하여 만듭니다.

 

위처럼 문자셋이 다른 상태에서 비교 혹은 JOIN 을 하게 되면 해당 에러가 발생합니다.

INSERT INTO tbl_collation_1(string1) VALUES ('a');
INSERT INTO tbl_collation_1(string1) VALUES ('b');

INSERT INTO tbl_collation_2(string2) VALUES ('a');
INSERT INTO tbl_collation_2(string2) VALUES ('b');

SELECT	*
FROM	tbl_collation_1 A
		INNER JOIN
        tbl_collation_2 B
        ON A.string1 = B.string2;

Error Code: 1267. Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='	0.000 sec

 

위의 에러를 해결하려면 한쪽의 문자셋을 변경해 주면 됩니다.

SELECT	*
FROM	tbl_collation_1 A 
		INNER JOIN
        tbl_collation_2 B
        ON A.string1 collate utf8mb4_general_ci = B.string2;

처음부터 문자셋을 맞춰주고 하면 좋겠지만 무심코 그냥 생성을 하게 되면 DB 기본 설정값이 적용되기 때문에 서비스에 문제가 있을 수 있습니다.

 

 

workbench 의 경우 위의 세팅을 쉽게 확인 할 수 있는 기능이 있습니다.

 

두개 테이블의 collation 을 쉽게 확인 할 수 있습니다. 

현재 적용되어 있는 table, provedure 등의 문자셋을 확인하려면 아래 기능을 이용하면 쉽습니다.

 

위의 문제를 해결하려면 테이블과 컬럼의 문자셋을 수정하면 됩니다.

ALTER TABLE tbl_collation_1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tbl_collation_1 MODIFY string1 varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

 

이후 아래와 같이 수정된 내용을 볼 수 있습니다.

 

위의 내용을 직접 명령어로 확인하려면 아래와 같이 실행합니다.

 

 DB 확인

SELECT	SCHEMA_NAME, 
		DEFAULT_CHARACTER_SET_NAME, 
        DEFAULT_COLLATION_NAME
FROM 	information_schema.SCHEMATA
WHERE	SCHEMA_NAME = 'database1';

DB 기본이 utf8mb4_0900_ai_ci 이기 때문에 특별하게 선언해 주지 않으면 utf8mb4_0900_ai_ci 로 생성됩니다. 

 

테이블 확인

use database1;
SHOW TABLE STATUS WHERE NAME LIKE 'tbl_collation%';

 

DB 자체의 문자셋을 변경하려면 아래와 같이 합니다.

alter database database1 character set utf8mb4 collate utf8mb4_general_ci;

아래와 같이 변경이 되었습니다.

이 상태에서 테이블을 그냥 만들어 봅니다.

USE database1;
DROP TABLE IF EXISTS tbl_collation_3;

CREATE TABLE tbl_collation_3 (
	seq_no	bigint 		NOT	NULL	AUTO_INCREMENT,
	string3	varchar(20)	NULL,
	PRIMARY KEY (seq_no)
)
ENGINE=InnoDB;

tbl_collation_3 은 DB 의 기본 문자셋이 utf8mb4_general_ci 이기 때문에 문제 없이 통일 되서 만들어 졌습니다.

 

PROCEDURE 에서 아무 문제가 없는데 workbench 에서 에러가 발생하는 경우가 있습니다. 

이런 경우에는 connection collation 이 문제가 있습니다.

Procedure 만들때 "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';"  선언하고 생성하면 됩니다.