Pink Spider/MySQL Collation 완전 정복

1. Charset vs Collation, 뭐가 다를까?

Character Set(문자셋) 은 “어떤 문자들을 저장할 수 있는가"를 정의합니다. utf8mb4는 이모지 포함 모든 유니코드 문자를 최대 4바이트로 저장합니다.

Collation(콜레이션) 은 “문자들을 어떻게 비교하고 정렬하는가"를 정의합니다. 'A' = 'a'인지, 'ä'가 'a'와 같게 취급되는지, 한글이 사전 순으로 정렬되는지를 결정하는 규칙 집합입니다.

핵심 관계: 모든 Collation은 특정 Charset에 종속됩니다. utf8mb4_unicode_ci는 utf8mb4 charset에서만 사용 가능합니다.

-- charset과 collation의 관계 확인
SHOW COLLATION
WHERE Charset = 'utf8mb4'
  AND Collation LIKE '%general%';

2. Collation 이름은 어떻게 읽나?

Collation 이름은 최대 4개 파트로 구성됩니다. 규칙을 알면 처음 보는 이름도 바로 해석할 수 있습니다.

utf8mb4  _  unicode  _  520  _  ci
   │            │          │      │
   │            │          │      └─ 플래그: ci / cs / bin
   │            │          └─────── 유니코드 버전: 520 = 5.2.0 / 0900 = 9.0.0
   │            └────────────────── 알고리즘: unicode / general / bin / 0900
   └─────────────────────────────── 문자셋: utf8mb4 / utf8 / latin1 …

접미사 플래그 의미

접미사	Full Name	동작
`_ci`	Case Insensitive	`'A' = 'a'` → true (대소문자 무시)
`_cs`	Case Sensitive	`'A' = 'a'` → false (대소문자 구분)
`_bin`	Binary	바이트 값 그대로 비교 (가장 빠름, 완전 구분)
`_ai`	Accent Insensitive	`'e' = 'é'` → true (악센트 무시)
`_as`	Accent Sensitive	`'e' ≠ 'é'` (악센트 구분)

3. 주요 Collation 비교

Collation	MySQL 버전	알고리즘	특징	추천
`utf8mb4_general_ci`	5.x	단순 변환	일부 언어 정렬 부정확, 빠름	❌ 비추천
`utf8mb4_unicode_ci`	5.x+	UCA 4.0	general보다 정확한 다국어 정렬	구버전 호환
`utf8mb4_unicode_520_ci`	5.6+	UCA 5.2	unicode보다 향상된 정확도	5.6 환경
`utf8mb4_0900_ai_ci`	8.0+	UCA 9.0	최신 유니코드, ai_ci, 빠름	✅ 권장
`utf8mb4_0900_bin`	8.0+	Binary	바이트 완전 일치, 최고 속도	토큰·해시

MySQL 8.0 기본값: MySQL 8.0부터 기본 charset은 utf8mb4, 기본 collation은 utf8mb4_0900_ai_ci로 변경되었습니다. 5.7 이하에서 업그레이드할 때 기존 동작이 달라질 수 있습니다.

-- 대소문자 구분 차이 실험
SELECT
  'Hello' COLLATE utf8mb4_0900_ai_ci = 'hello' AS ai_ci,  -- 1 (같음)
  'Hello' COLLATE utf8mb4_0900_bin   = 'hello' AS bin;     -- 0 (다름)

4. Collation이 적용되는 4가지 레벨

MySQL은 서버 → 데이터베이스 → 테이블 → 컬럼 순으로 상속됩니다. 하위 레벨이 상위를 덮어씁니다.

-- 1. 서버 레벨 (my.cnf)
-- [mysqld]
-- character-set-server = utf8mb4
-- collation-server      = utf8mb4_0900_ai_ci

-- 2. 데이터베이스 레벨
CREATE DATABASE myapp
  CHARACTER SET  utf8mb4
  COLLATE        utf8mb4_0900_ai_ci;

-- 3. 테이블 레벨
CREATE TABLE users (
  id    INT PRIMARY KEY,
  name  VARCHAR(100)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 4. 컬럼 레벨 (테이블 설정 덮어쓰기)
CREATE TABLE tokens (
  id    INT PRIMARY KEY,
  token VARCHAR(64) CHARACTER SET utf8mb4
                    COLLATE utf8mb4_0900_bin  -- 토큰은 대소문자 구분 필요
);

5. 한국어 서비스의 올바른 Collation 설정

한글은 유니코드 코드포인트 순서와 가나다 순 정렬이 일치하므로, 다국어 서비스가 아닌 경우 어떤 UCA collation을 써도 한글 정렬 결과는 동일합니다. 단, 영문 대소문자 및 특수문자 처리 정책에 따라 선택이 달라집니다.

✅ 권장 설정 (MySQL 8.0+)

일반 텍스트, 이름, 검색어 컬럼 → utf8mb4_0900_ai_ci
비밀번호 해시, 토큰, UUID 컬럼 → utf8mb4_0900_bin

⛔ 주의·비추천

utf8mb4_general_ci — 다국어 정렬 부정확
utf8 charset — 이모지 저장 불가 (3바이트 한계)
테이블마다 다른 collation — JOIN 시 불필요한 변환 비용
혼합 charset 컬럼 간 비교 — 에러 발생

권장 my.cnf 설정

[mysqld]
character-set-server  = utf8mb4
collation-server      = utf8mb4_0900_ai_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

6. 현재 설정 확인 및 변경 방법

현재 설정 조회

-- 서버 전체 변수 확인
SHOW VARIABLES LIKE '%collation%';
SHOW VARIABLES LIKE '%character%';

-- 특정 테이블 collation 확인
SHOW CREATE TABLE users;

-- 컬럼별 collation 상세 조회
SELECT
  COLUMN_NAME,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'myapp'
  AND TABLE_NAME   = 'users';

Collation 변경 (마이그레이션)

⚠️ 주의: Collation 변경은 테이블 리빌드를 동반합니다. 대용량 테이블에서는 서비스 점검 또는 pt-online-schema-change 사용을 권장합니다.

-- 테이블 전체 변경 (테이블 리빌드 발생)
ALTER TABLE users
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 특정 컬럼만 변경
ALTER TABLE users
  MODIFY COLUMN name VARCHAR(100)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci
    NOT NULL;

-- 데이터베이스 기본값만 변경 (기존 테이블은 그대로)
ALTER DATABASE myapp
  CHARACTER SET  utf8mb4
  COLLATE        utf8mb4_0900_ai_ci;

7. Collation과 인덱스·성능의 관계

Collation 불일치는 쿼리 성능에 직접적인 영향을 미칩니다. 특히 JOIN 시 두 컬럼의 collation이 다르면 인덱스를 사용할 수 없고 풀 스캔이 발생합니다.

-- ❌ 나쁜 예: collation 불일치 JOIN (인덱스 미사용)
SELECT *
FROM  users u                           -- collation: utf8mb4_general_ci
JOIN  orders o ON u.email = o.email;    -- collation: utf8mb4_0900_ai_ci
-- EXPLAIN 결과: type = ALL (풀 스캔)

-- ✅ 좋은 예: 명시적 COLLATE 지정 또는 동일 collation 사용
SELECT *
FROM  users u
JOIN  orders o
  ON  u.email = o.email COLLATE utf8mb4_0900_ai_ci;

`_bin` Collation과 성능

_bin collation은 바이트 직접 비교라 변환 오버헤드가 없어 가장 빠릅니다. 단, 대소문자·악센트를 완전히 구분하므로 검색 쿼리에는 적합하지 않습니다. 토큰, 해시, UUID 저장 컬럼에 이상적입니다.

8. 현업에서 자주 겪는 실수들

⚠️ `utf8` charset 사용

MySQL의 utf8은 실제 UTF-8이 아닌 3바이트 버전입니다. 이모지(4바이트)가 깨집니다. 반드시 utf8mb4를 사용하세요.

⚠️ 5.7 → 8.0 업그레이드 시 기본 collation 변경

MySQL 8.0에서 기본값이 utf8mb4_0900_ai_ci로 바뀝니다. 새 테이블과 기존 테이블의 collation이 달라져 JOIN 성능이 떨어질 수 있습니다.

⚠️ WHERE 절 함수 사용으로 인덱스 무효화

WHERE LOWER(name) = 'alice'는 인덱스를 사용할 수 없습니다. _ci collation을 쓰면 WHERE name = 'alice'만으로 대소문자 무시 검색이 가능합니다.

⚠️ 커넥션 charset 불일치

서버는 utf8mb4인데 커넥션이 utf8이면 데이터가 손상될 수 있습니다. 항상 SET NAMES utf8mb4 또는 드라이버 옵션 characterEncoding=utf8mb4를 설정하세요.

⚠️ 비밀번호 컬럼에 `_ci` collation

_ci를 쓰면 'Password1'과 'password1'이 같은 값으로 인식됩니다. 비밀번호 해시 컬럼은 반드시 _bin을 사용하세요.

-- 컬럼별로 올바른 collation 적용 예시
CREATE TABLE users (
  id            INT          PRIMARY KEY AUTO_INCREMENT,
  email         VARCHAR(255) COLLATE utf8mb4_0900_ai_ci,  -- 검색: ci
  password_hash VARCHAR(255) COLLATE utf8mb4_0900_bin,     -- 해시: bin
  name          VARCHAR(100) COLLATE utf8mb4_0900_ai_ci,  -- 이름: ci
  api_token     VARCHAR(64)  COLLATE utf8mb4_0900_bin      -- 토큰: bin
) ENGINE=InnoDB
  CHARACTER SET  utf8mb4
  COLLATE        utf8mb4_0900_ai_ci;

정리

상황	권장 Collation
일반 텍스트, 이름, 검색어	`utf8mb4_0900_ai_ci`
비밀번호 해시, 토큰, UUID	`utf8mb4_0900_bin`
MySQL 5.6 환경	`utf8mb4_unicode_520_ci`
MySQL 5.5 이하	`utf8mb4_unicode_ci`

Collation은 한 번 잘못 선택하면 마이그레이션 비용이 크므로, 프로젝트 초기에 utf8mb4 + utf8mb4_0900_ai_ci로 통일하는 것이 가장 좋습니다.