[MySQL] Character Set, Collation 확인(utf8mb4_general_ci로 바꿔라)

문자셋(Character set) 과 콜레이션(Collation)

CHAR, VARCHAR, TEXT 등의 문자열 Datatype에는 문자셋(Character set)과 콜레이션(Collation) 속성이 있다.

문자셋(Character set)은 각 문자가 컴퓨터에 저장될 때 어떠한 '코드'로 저장될지에 대한 규칙의 집합을 의미하며

콜레이션(Collation)은 특정 문자 셋(Character set)에 의해 데이터베이스에 저장된 값들을 비교 검색하거나 문자들을 서로 정렬(Order) 등의 작업을 위해 비교할때 사용하는 규칙들의 집합을 의미한다.

👀 차이점은?
같은 문자셋이라도 콜레이션에 따라 영어의 경우 대소문자의 구분 비교 여부, 일본어의 경우 히라가나와 카타카나의 구분 방법 등이 달라진다.

UTF8 문자셋을 사용하는 경우 utf8-general-ci 또는 utf8-unicode-ci 둘중 하나를 collation으로 지정하는 경우가 많은데 utf8-general-ci는 utf8-unicode-ci 를 사용해서 정렬할 때 보다 다소 정확도가 떨어지는 경향이 있으나 속도는 빠르다.

MySQL 8.0.1 버전부터 utf8mb4_0900_ai_ci이 기본값이다.

Collation utf8mb4_0900_ai_ci 문제

결론

utf8mb4_general_ci 로 바꿔라!

MySQL 8.0.1 버전부터 utf8mb4_0900_ai_ci를 기본값으로 적용이 되어 있다. 기존의 5.x버전대의 MySQL을 사용해 오던 유저분들이라면, 한글을 사용해야 하는 환경에서 대부분 utf8mb4_general_ci를 사용해왔을 것이다.

결론적으로 말하자면,

Collation은 단지 검색과 정렬에만 사용하는 것이 아니라 유니크 인덱스나 다른 부분에도 많이 사용되기 때문에 한글 데이터가 있으면 매우 주의해야 한다!

정확한 한글(및 동아시아 언어, 일본어, 중국어) 검색을 처리하기 위해서는 utf8mb4_general_ci 로 설정해야 합니다.

권장사항
1) MySQL 5.5.3 이전 = utf8 charset 에, utf8_general_ci collation 사용
2) MySQL 이 최신일 때 = utf8mb4 charset 에, utf8mb4_unicode_ci collation 사용.

COLLATION을 확인하는 sql 명령어

COLLATION 변경 방법은 아래 블로그를 참고하길 바란다.
https://bstar36.tistory.com/318

그전에 COLLATION을 확인하는 sql 명령어를 정리해 보았다.

1) 전체 database character set 확인

SELECT SCHEMA_NAME AS 'database', DEFAULT_CHARACTER_SET_NAME AS 'character_set', DEFAULT_COLLATION_NAME AS 'collation'
FROM information_schema.SCHEMATA;

2) 1개 database character set 확인

USE database_name;
SHOW VARIABLES LIKE 'character_set_database';

3) table collation 확인

SHOW TABLE STATUS WHERE NAME LIKE 'table_name';

4) column collation 확인

# SHOW FULL COLUMNS FROM table_name;
SHOW FULL COLUMNS FROM coupon;

=> 주로 여기서 많이 확인되었음 2022-08-30

COLLATION 변경 방법

💥 Error 사항

테이블의 COLLATION 즉, utf8_general_ci 또는 euckr 에서 맞지 않아 아래의 오류문구가 나올 수 있다.

오류 코드: 1267Illegal mix
of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for
operation '='

테이블의 COLLATION을 맞춰 주던지 , 쿼리 레벨에서 변경해 준다.

1) 데이터베이스 레벨

ALTER DATABASE cpp CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2) 테이블 레벨

ALTER TABLE t_bar CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;

3) 세션 레벨

SET collation_connection = 'utf8mb4_general_ci' ;

4) 쿼리 레벨

SELECT cash, in_cash FROM tb_cash where svc_inst_no = @tid and cash_attr_no = @cashAttrNo limit 1000 COLLATE utf8mb4_general_ci ;

---

출처

• https://hyorock.tistory.com/52
• https://rastalion.me/mysql-8-0-1-%EB%B2%84%EC%A0%84%EB%B6%80%ED%84%B0-%EC%B1%84%ED%83%9D%EB%90%9C-utf8mb4_0900_ai_ci%EC%9D%98-%ED%95%9C%EA%B8%80-%EC%82%AC%EC%9A%A9%EC%97%90-%EB%8C%80%ED%95%9C-%EB%AC%B8%EC%A0%9C%EC%A0%90/