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
13.7.6.3 SET NAMES Statement SET NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT} This statement sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setti
dev.mysql.com
workbench 사용하시는 분은 참고해 주세요
테이블 문자셋 에러 해결