[MySQL] Character Set 불일치 문제

MySQL DB 에 한글 저장시 깨짐현상이 발생하여 트러블슈팅한 과정을 적어보고자 한다.

MySQL Character Set 불일치 문제 해결하기

1. 문제 상황

# 현상
- 한글 데이터 저장 시 깨짐 발생 (예: "안녕하세요" -> "안ë..."")
- 저장된 한글 데이터 조회 시 깨짐
- 개발 환경에서는 정상 동작하나 운영 환경에서 문제 발생

# 증상 확인 방법
- 데이터베이스에서 직접 조회했을 때의 데이터 상태
- 애플리케이션에서 조회했을 때의 데이터 상태

2. 원인 분석

-- 1. Character Set 레벨
Server > Database > Table > Column 순으로 적용

-- 2. Connection 설정 확인
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

-- 3. 설정 불일치 확인
character_set_client = latin1
character_set_connection = latin1
character_set_results = latin1
character_set_database = utf8mb4

2.1 Character Set과 Collation 이해하기

1. Character Set: 문자를 저장하는 인코딩 방식
   - utf8mb3: 기본 유니코드 문자 (3바이트)
   - utf8mb4: 이모지 포함 모든 유니코드 문자 (4바이트)
   - latin1: 서유럽 언어 전용

2. Collation: 문자 정렬과 비교 규칙
   - utf8mb4_general_ci: 빠르지만 덜 정확한 정렬
   - utf8mb4_unicode_ci: 정확한 유니코드 정렬
   - utf8mb4_bin: 바이너리 수준 비교

2.2 문제가 발생하는 과정

1. 클라이언트(PHP/Laravel) -> MySQL 서버 연결 시
   - 명시적 설정이 없으면 서버 기본값(latin1) 사용

2. 데이터 저장 과정
   [실제 발생하는 과정]
   클라이언트(UTF-8 데이터: "안녕") 
   -> 연결(latin1으로 잘못 해석: "안ë...") 
   -> DB에 깨진 상태 그대로 저장 ("안ë...")

   * DB의 character_set이 utf8mb4여도, 이미 깨진 형태로 전달된 데이터가 
     그대로 저장됨

3. 데이터 조회 과정
   DB(깨진 상태로 저장된 "안ë...") 
   -> 연결(latin1) 
   -> 클라이언트에 깨진 상태로 전달

주요 포인트
1. 문제는 데이터가 DB에 저장되기 전, 연결 단계에서 발생
2. DB의 character_set 설정은 이미 깨진 데이터를 받게 되므로 영향을 미치지 못함
3. 따라서 연결 시점의 character_set 설정이 매우 중요

3. 트러블슈팅

3.1 서버 설정 확인

-- 현재 설정 확인
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

-- 데이터베이스 설정 확인
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'your_database';

-- 테이블 설정 확인
SHOW CREATE TABLE your_table;

3.2 문제 해결 단계

-- 1. 서버 설정 변경 (my.cnf)
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

-- 2. 데이터베이스 설정 변경
ALTER DATABASE your_database 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 3. 테이블 설정 변경
ALTER TABLE your_table 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 4. 깨진 데이터 복구
UPDATE your_table 
SET column_name = CONVERT(
    BINARY(CONVERT(column_name USING latin1)) 
    USING utf8mb4
);

4. Laravel에서의 해결 방법

4.1 설정 파일 수정

// config/database.php
'connections' => [
    'mysql' => [
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        ...
    ],
],

4.2 연결된 connection 정보 확인

// 1. 현재 연결의 character set과 collation 확인
$connectionSettings = DB::select("
    SELECT @@character_set_client,
           @@character_set_connection,
           @@character_set_results,
           @@collation_connection
");

// 2. 전체 character set 변수 확인
$charsets = DB::select("SHOW VARIABLES LIKE '%character%'");

// 3. 전체 collation 변수 확인
$collations = DB::select("SHOW VARIABLES LIKE '%collation%'");

// 4. 데이터베이스 기본 설정 확인
$dbSettings = DB::select("
    SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
    FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = ?
", [config('database.connections.mysql.database')]);

5. Best Practice

5.1 개발/운영 환경 통일

1. Docker 사용 시
   - docker-compose.yml에 MySQL 설정 포함
   - custom my.cnf 마운트

2. AWS RDS 사용 시
   - Parameter Group 설정
   - 개발/운영 동일 Parameter Group 사용

5.2 모니터링 및 유지보수

1. 정기적인 인코딩 설정 확인
   - 서버 설정
   - 데이터베이스 설정
   - 테이블 설정
   - 컬럼 설정

2. 로깅 및 모니터링
   - 문자셋 변환 오류 로깅
   - 이상 문자 발견 시 알림

3. 백업 및 복구 계획
   - 인코딩 변경 전 백업
   - 복구 절차 문서화

5.3 신규 프로젝트 시작 시 체크리스트

1. 데이터베이스 설정
   - Character Set: utf8mb4
   - Collation: utf8mb4_unicode_ci

2. 애플리케이션 설정
   - Laravel config 설정