MySQL - Character Set, Encoding, collation

이전에 업데이트 작업에서 staging 과 production 환경에서 설정된 collation 이 달라 DB 동기화 작업하기 위해 테이블을 추가하는 과정에서 에러가 발생했을 때 정리했던 내용.

 

1. 문자 집합 & 인코딩

source -  https://kimwooseok.com/web/2021/06/01/encoding/

(1) 문자 집합(Character set)

• 컴퓨터가 인식하고 표현할 수 있는 문자의 모음
• UTF-8(Universal Coded Character Set + Transformation Format – 8-bit)
  • 유니코드를 위한 가변 길이 문자 인코딩 방식 중 하나
  • 유니코드 한 문자를 나타내기 위해 1바이트에서 4바이트 까지를 사용 (4byte encoding)
  • MySQL character-set 비교
    • utf8 : MySQL에서는 가변-3byte utf8을 지원하는 자료형 (emoji 저장 시, 데이터 유실 위험)
    • utf8mb4 : 가변-4byte utf8을 지원하는 자료형 (emoji 저장 가능)

(2) 문자 인코딩(Character encoding)

• 문자를 컴퓨터가 이해할 수 있도록 0,1 로 반환하는 과정 (문자 비트 →(문자 집합)→ 데이터 비트)

(3) 문자 디코딩(Character decoding)

• 기계어를 사람이 읽을 수 있는 문자로 변환하는 과정 (데이터 비트 →(문자 집합)→ 문자)

(4) 정렬 방식 (collation)

• utf8_bin : 바이너리 저장 값 그대로 정렬 (A(41) → B(42) → a(61) → b(62))
• utf8_general_ci : 라틴계열의 문자를 사람의 인식에 맞게 정렬 (A → a → B → b)
• utf8_unicode_ci : utf8_general_ci + 한국어, 영어, 중국어, 일본어과 같은 문자 정렬 순서 지원

 

2. MySQL Collation & 인코딩 방식 설정 방법

(1) collation 확인

-- (1) system 전체 collation 확인
SHOW VARIABLES LIKE 'collation%';

-- (2) database level collation 확인
SELECT 
    SCHEMA_NAME AS 'database',
    DEFAULT_CHARACTER_SET_NAME AS 'character_set',
    DEFAULT_COLLATION_NAME AS 'collation'
FROM INFORMATION_SCHEMA.SCHEMATA;

-- (3) runtime 에 변경 시
SET SESSION collation_database = utf8_unicode_ci;

-- (4) 서버 실행 시 server chracter set, collation 설정
mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci

-- (5) Database 생성 시점 설정
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_unicode_ci;

 

(2) collation 변경

-- (1) 가능한 character set 확인
SHOW CHARACTER SET;

-- (2) table 의 collation 변경
ALTER TABLE `my_test_table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

-- (3) database 의 collation 변경
ALTER DATABASE mydb CHARACTER SET utf8 COLLATE utf8_unicode_ci;