MySQL CharacterSet 설정 에러

Introduction

다국어 지원이 필요한 애플리케이션에서 MySQL 문자셋(Character Set) 설정은 매우 중요합니다. 잘못된 설정은 데이터 손실, 깨진 문자, 그리고 예상치 못한 쿼리 결과를 초래할 수 있습니다. 특히 UTF-8과 UTF-8MB4의 차이를 이해하지 못하면 이모지나 CJK 문자 처리에서 심각한 문제가 발생합니다. 이 글에서는 문자셋 관련 에러와 해결 방법을 다루겠습니다.

Environment

# MySQL 문자셋 관련 설정 확인
mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'collation%';"

-- 현재 데이터베이스 문자셋 확인
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mydb';

Problem

문자셋 설정 문제로 인한 에러들:

-- 이모지 삽입 시도
INSERT INTO users (username, bio) VALUES ('joel', '개발자입니다 🚀');
-- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x9A\x80' for column 'bio' at row 1

-- utf8mb4가 아닌 utf8 사용 시
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    content VARCHAR(255) CHARACTER SET utf8
);
-- utf8은 최대 3바이트까지만 지원 (이모지 저장 불가)

# 에러 로그 확인
tail -100 /var/log/mysql/error.log | grep -i charset
# [Warning] Incorrect string value: '\xF0\x9F\x9A\x80' for column 'bio' at row 1
# [Note] Statement may not be safe to log in statement format.

Analysis

문자셋 문제의 근본 원인을 분석했습니다:

-- 테이블별 문자셋 확인
SELECT 
    TABLE_NAME,
    CHARACTER_SET_NAME,
    TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

-- 결과
-- users       | latin1     | latin1_swedish_ci
-- posts       | utf8       | utf8_general_ci
-- comments    | utf8mb4    | utf8mb4_unicode_ci

-- 컬럼별 문자셋 확인
SELECT 
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';

-- 결과
-- username | latin1 | latin1_swedish_ci
-- bio      | latin1 | latin1_swedish_ci

# 클라이언트 문자셋 확인
mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set_client';"
# character_set_client = utf8mb4  <-- 서버는 utf8mb4를 기대하지만 테이블은 latin1

Solution

1단계: 서버 레벨 문자셋 설정

# my.cnf 파일 수정
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

# MySQL 서버 재시작
sudo systemctl restart mysql

2단계: 기존 테이블 문자셋 변경

-- 데이터베이스 문자셋 변경
ALTER DATABASE mydb 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 테이블 문자셋 변경
ALTER TABLE users 
    CONVERT TO CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 개별 컬럼 변경 (대용량 테이블의 경우)
ALTER TABLE users MODIFY COLUMN bio 
    TEXT CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

3단계: 연결 문자셋 설정

-- 세션 레벨 설정
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- 또는 개별 변수 설정
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

// PHP PDO 연결 시 문자셋 설정
$dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$pdo = new PDO($dsn, $username, $password, $options);

4단계: 인코딩 검증 스크립트

-- 인코딩 문제가 있는 데이터 확인 함수
DELIMITER //
CREATE FUNCTION check_encoding_issues(
    p_table_name VARCHAR(64),
    p_column_name VARCHAR(64)
)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE issue_count INT DEFAULT 0;
    DECLARE sql_text TEXT;
    
    SET sql_text = CONCAT(
        'SELECT COUNT(*) INTO @count FROM ', p_table_name,
        ' WHERE LENGTH(', p_column_name, ') != CHAR_LENGTH(', p_column_name, ')'
    );
    
    SET @sql = sql_text;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    RETURN @count;
END //
DELIMITER ;

Lessons Learned

UTF8 vs UTF8MB4: MySQL에서 utf8은 최대 3바이트이며, 4바이트 문자는 utf8mb4를 사용해야 합니다
일관성 유지: 서버, 데이터베이스, 테이블, 컬럼 모두 동일한 문자셋을 사용해야 합니다
대용량 변환 주의: 대용량 테이블의 문자셋 변경은 긴 시간이 소요되므로 장애 시간을 고려해야 합니다
파일 임포트 시 주의: CSV 파일 임포트 시 파일 인코딩과 일치하도록 설정해야 합니다
정렬 규칙(collation) 선택: 대소문자 구분 없는 비교가 필요하면 _ci, 구분이 필요하면 _cs를 선택합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

MySQL CharacterSet 설정 에러: 인코딩 문제 해결 가이드