Mysql - Procedure Error Code: 1267. Illegal mix of collations workbench

Workbench 를 사용하다 보면 collation 설정을 다 해줬는데도 에러가 발생하는 경우가 있습니다. 

이런경우 해결 방법입니다.

 

문자셋 에러를 위해서 아래의 데이터를 세팅합니다.

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;

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;

위처럼 테이블 내에서 문자셋이 맞지 않아서 에러가 나는 경우는 아래처럼 수정이 가능합니다.

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;

 

하지만 프로시저에서 에러가 나는 경우도 있습니다.

USE database1;
DROP PROCEDURE IF EXISTS sp_collation_test;

DELIMITER $$
CREATE PROCEDURE sp_collation_test ()
COMMENT 'procedure_comment'
BEGIN

	PROCEDURE_BODY:
	BEGIN
				
		SELECT	*
		FROM	tbl_collation_1 A
				INNER JOIN
				tbl_collation_2 B
				ON A.string1 = B.string2;
	
	END PROCEDURE_BODY;
END$$
DELIMITER ;

CALL sp_collation_test();

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

 

아래처럼 connection collation 이 안맞아서 종종 에러가 발생합니다.

 

이런경우에는 PROCEDURE 생성시 "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';" 를 추가해 주시면 됩니다.

 

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';
USE database1;
DROP PROCEDURE IF EXISTS sp_collation_test;

DELIMITER $$
CREATE PROCEDURE sp_collation_test ()
COMMENT 'procedure_comment'
BEGIN

	PROCEDURE_BODY:
	BEGIN
				
		SELECT	*
		FROM	tbl_collation_1 A
				INNER JOIN
				tbl_collation_2 B
				ON A.string1 = B.string2;
	
	END PROCEDURE_BODY;
END$$
DELIMITER ;


CALL sp_collation_test();

 

 

SET NAMES 상태는 아래와 같습니다.

collation_name 이 선언을 해주지 않으면 DEFAULT 로 선언됩니다.  

DB 가 utf8mb4_0900_ai_ci 라면 "SET NAMES 'utf8mb4'" 라고면 선언해주면  collation 은 DB 기본값인 utf8mb4_0900_ai_ci 으로 생성됩니다.

 

또한 connection_collation 은 COLLATE 를 따라 가기 때문에 "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';"로

전체 선언을 해줘야 일괄 적용 됩니다. 

 

https://dev.mysql.com/doc/refman/8.0/en/set-names.html

MySQL :: MySQL 8.0 Reference Manual :: 13.7.6.3 SET NAMES Statement

 

workbench 사용하시는 분은 참고해 주세요 

 

 

 

테이블 문자셋 에러 해결

https://nethunter.tistory.com/entry/Mysql-Error-Code-1267-Illegal-mix-of-collations-%EC%97%90%EB%9F%AC-%ED%95%B4%EA%B2%B0