왜 당신의 MySQL 데이터베이스는 'café'를 'café'라고 생각할까?

Source: Dev.to

문제 상황

오전 2시 47분. 프로덕션 환경에서 디버깅 중이다(예, 우리 모두 그런 경험이 있다). 파리의 한 고객이 방금 만든 “Café Rouge” 를 찾을 수 없다고 보고했다.

SELECT * FROM restaurants WHERE name = 'Café Rouge';

결과: Empty set.

하지만 데이터는 존재한다:

문제는? 데이터베이스가 입력한 문자열과 일치하지 못한다.

인코딩이 중요한 이유

문자 “é” 를 인코딩하는 방법은 여러 가지가 있다:

사람에게는 동일하게 보이지만 컴퓨터에게는 완전히 다른 문자열이다. 사용자가 한 형태를 입력하고 데이터베이스가 다른 형태로 저장돼 있다면, 직접 비교는 실패한다.

문자 집합

문자 집합은 기호를 숫자(인코딩)와 매핑한다. MySQL에서 흔히 쓰이는 문자 집합:

• utf8mb4 – 전체 UTF‑8 지원(4바이트 문자, 이모지 등)
• latin1 – 서유럽 언어
• ascii – 7비트 영어 전용

Note: MySQL의 오래된 utf8 문자 집합은 최대 3바이트 문자만 지원하므로 많은 이모지나 희귀 기호를 저장할 수 없다.

-- 이모지와 함께 실패
CREATE TABLE old_table (
    message VARCHAR(100) CHARACTER SET utf8
);

INSERT INTO old_table VALUES ('I love coding! 😍');
-- ERROR 1366: Incorrect string value

utf8mb4를 사용하라:

CREATE TABLE modern_table (
    message VARCHAR(100) CHARACTER SET utf8mb4
);

INSERT INTO modern_table VALUES ('I love coding! 😍');
-- Success

정렬 규칙(Collations)

문자 집합이 사전이라면, 정렬 규칙은 비교를 위한 문법을 정의한다:

• 대소문자 구분 (A vs a)
• 악센트 구분 (café vs cafe)
• 다중 문자 매핑 (ß vs ss)
• 정렬 규칙(자연 순서)

MySQL 정렬 규칙 명명 패턴: charset_language_sensitivity

비교 예시

SET @name1 = 'José';
SET @name2 = 'jose';
SET @name3 = 'José'; -- 다른 é 인코딩

-- 바이너리 정렬 규칙(바이트 비교)
SELECT @name1 = @name2 COLLATE utf8mb4_bin;          -- 0 (false)

-- 대소문자 무시 정렬 규칙
SELECT @name1 = @name2 COLLATE utf8mb4_general_ci; -- 1 (true)

-- 악센트 무시 정렬 규칙
SELECT 'José' = 'Jose' COLLATE utf8mb4_0900_ai_ci; -- 1 (true)

실무 영향

고유 사용자 이름

CREATE TABLE users (
    username VARCHAR(50) COLLATE utf8mb4_0900_as_cs
    -- as = accent sensitive, cs = case sensitive
);

INSERT INTO users VALUES ('José'), ('jose'), ('Jose');
-- 세 개의 서로 다른 행

유연한 검색

CREATE TABLE search_terms (
    query VARCHAR(100) COLLATE utf8mb4_0900_ai_ci
);

SELECT * FROM products
WHERE name LIKE '%café%' COLLATE utf8mb4_0900_ai_ci;
-- café, Café, CAFÉ, cafe, CAFE 모두 매치

실제 사례: 독일어 “Müller”

한 사이트가 latin1 문자 집합과 바이너리 정렬 규칙으로 이름을 저장했다. 사용자는 분해된 ü(u + ¨) 로 “Müller”를 입력했지만, 쿼리는 행을 반환하지 않았다.

해결 방법:

ALTER TABLE users
MODIFY name VARCHAR(100)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

이모지 오류

INSERT INTO posts (content) VALUES ('This is fire! 🔥');
-- ERROR 1366: Incorrect string value

예방: 테이블에 utf8mb4를 사용하도록 설정한다.

성능 고려사항

바이너리 정렬 규칙은 원시 바이트를 비교하므로 더 빠르다.

-- 바이너리 정렬 규칙(≈0.05 s)
SELECT COUNT(*) FROM users
WHERE email = 'test@example.com' COLLATE utf8mb4_bin;

-- 유니코드 정렬 규칙(≈0.12 s)
SELECT COUNT(*) FROM users
WHERE email = 'test@example.com' COLLATE utf8mb4_unicode_ci;

언제 어떤 것을 사용할까

문자 집합·정렬 규칙 혼용

MySQL은 서버, 데이터베이스, 테이블, 컬럼, 쿼리 수준에서 서로 다른 설정을 허용한다. 혼용하면 오류가 발생한다:

CREATE DATABASE app
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

CREATE TABLE app.users (
    id INT PRIMARY KEY,
    email VARCHAR(255) CHARACTER SET latin1,   -- ⚠️
    username VARCHAR(100) COLLATE utf8mb4_bin -- ⚠️
);

SELECT * FROM users u1
JOIN users u2 ON u1.username = u2.email;
-- ERROR 1267: Illegal mix of collations

골든 룰: 전체 애플리케이션에 하나의 문자 집합과 정렬 규칙을 선택한다(예: utf8mb4 + utf8mb4_unicode_ci) 그리고 그것을 고수한다.

권장 기본값

CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

포렌식 쿼리

문자열 인코딩 확인

SELECT HEX('café');  -- 636166C3A9 (UTF‑8) 반환

컬럼 문자 집합·정렬 규칙 확인

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

특정 정렬 규칙으로 문자열 비교

SELECT 'Müller' = 'Mueller' COLLATE utf8mb4_unicode_ci;

정렬 규칙 불일치 찾기

SELECT DISTINCT COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database';
-- 이상적으로는 하나 혹은 두 개의 정렬 규칙만 반환

기존 데이터베이스 마이그레이션

데이터베이스가 이미 잘못된 문자 집합·정렬 규칙을 사용하고 있다면:

1. 백업(항상):

   mysqldump your_database > backup.sql

2. 테이블·컬럼 변환(단일 테이블 예시):

   ALTER TABLE your_table
   CONVERT TO CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;

3. 서버 기본값 업데이트(my.cnf):

   [mysqld]
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci

4. 연결당 설정 적용(필요 시):

   SET NAMES utf8mb4;

마이그레이션 후에는 애플리케이션 테스트를 다시 실행해 모든 쿼리가 기대대로 동작하는지 확인한다.