[Note] MySQL utf8mb4_unicode_ci

들어가기 전

공급사로부터 받은 이미지 caption을 내부 카테고리로 매핑하는 배치가 있다. 구조는 단순하다. 미리 적재해 둔 매핑 테이블을 부트스트랩 시점에 캐시로 올려두고, 런타임에 caption을 정규화(canonicalize)한 뒤 캐시에서 조회하는 것이다.

그런데 어느 날, 분명히 매핑 데이터를 넣어 뒀는데도 특정 caption이 계속 UNMATCHED로 처리됐다. SQL 파일에도 있고, 미매핑 추적용 CSV에도 있었다. DB 부트스트랩은 항상 실행되는 always 모드였다. 로그만 보면 완벽하게 정상이었다.

처음엔 배치 코드 쪽을 의심했다. SQL 파일이 제대로 실행됐는지, 캐시 로딩에 타이밍 문제가 있는 건 아닌지, canonical key 생성 과정에서 뭔가 빠지는 건 아닌지. 코드를 훑어봐도 특이한 점이 없었다. 로그를 더 촘촘하게 찍어봐도 UNMATCHED는 계속 발생했다.

이 글은 그 원인을 찾아가는 과정이다.

---

시스템 구조

매핑 시스템의 핵심은 두 가지다.

DB 레이어: tb_provider_image_caption_mapping 테이블에 공급사 원본 caption(caption_raw)과 내부 카테고리 ID(image_caption_id)를 저장한다. caption_raw 컬럼에는 unique key가 걸려 있고, 컬럼 collation은 utf8mb4_unicode_ci다.

부트스트랩 시점에 아래와 같은 형태의 SQL로 초기 매핑 데이터를 밀어 넣는다.

INSERT IGNORE INTO tb_provider_image_caption_mapping (caption_raw, image_caption_id)
SELECT 'Cafeteria' AS caption_raw, 5 AS image_caption_id
UNION ALL
SELECT 'Cafetería', 5
UNION ALL
SELECT 'Lobby', 3
...

공급사마다 동일한 공간을 다르게 표기한다. 스페인어 표기가 섞인 공급사, 영어만 쓰는 공급사, 그 중간 어딘가를 쓰는 공급사. 그래서 'Cafeteria'와 'Cafetería'는 의도적으로 둘 다 같은 내부 카테고리(image_caption_id = 5)로 매핑되어 있었다. 문제가 없어 보였다.

캐시 레이어: 애플리케이션 기동 시 테이블 데이터를 in-memory 맵으로 로드한다. 런타임에 공급사 caption이 들어오면 ImageCaptionRawCanonicalizer로 canonical key를 만들고 캐시에서 조회한다.

canonical key를 쓰는 이유가 있다. 공급사마다 불필요한 공백, 대소문자, 유니코드 표현 방식이 제각각이다. DB에 적재된 caption_raw 그대로를 캐시 키로 쓰면 같은 의미의 caption이라도 표현 방식에 따라 miss가 발생할 수 있다. 그래서 DB 데이터를 캐시에 올릴 때도, 런타임 조회 키를 만들 때도 동일한 canonicalize 함수를 거친다. canonical key 생성 로직은 다음과 같다.

fun canonicalize(raw: String): String {
    return Normalizer.normalize(raw, Normalizer.Form.NFKC)
        .replace(whitespaceRegex, " ")
        .trim()
        .lowercase(Locale.ROOT)
}

흐름 자체는 깔끔하다. DB에서 읽어온 값도, 공급사에서 들어온 값도, 동일한 함수를 거쳐 캐시에서 비교된다. 이론상 같은 텍스트라면 항상 같은 키가 나와야 한다.

문제는 이 두 레이어가 각자 다른 기준으로 "같다"를 판단하고 있었다는 데 있다.

---

결정적 단서

UNMATCHED 로그에 찍힌 caption은 "Cafetería"였다 (í = U+00ED, 악센트 있음). SQL 파일에도 'Cafetería'가 분명히 존재했다. 코드 경로를 하나씩 따라가 봐도 뭔가 빠지는 지점이 없었다.

그래서 방향을 바꿨다. 코드가 아니라 DB에 실제로 뭐가 들어가 있는지 직접 확인했다.

SELECT * FROM tb_provider_image_caption_mapping WHERE caption_raw = 'Cafetería';

결과가 이상했다.

caption_raw: Cafeteria   ← 악센트 없는 버전이 반환됨

쿼리가 반환한 건 'Cafeteria'였다. 분명히 'Cafetería'로 조회했는데. 테이블에 'Cafetería'가 없다는 게 아니라, 'Cafetería'로 조회했더니 'Cafeteria'가 나왔다는 점이 핵심이다. 이 쿼리 결과 하나로 문제의 위치가 DB와 Java 사이의 경계임을 알 수 있었다.

---

utf8mb4_unicode_ci는 accent-insensitive다

여기서 utf8mb4_unicode_ci의 특성을 짚고 넘어가야 한다.

ci는 case-insensitive의 약자다. MySQL collation 이름에 ci가 붙으면 대소문자를 구분하지 않는다는 것은 많이들 안다. 'Hello'와 'hello'를 같다고 보는 것이다.

그런데 unicode_ci는 그것 외에도 accent-insensitive 속성을 가진다. 유니코드 비교 알고리즘(UCA, Unicode Collation Algorithm)에서 악센트는 secondary weight로 처리되고, ci collation은 이 secondary weight를 비교 시 무시한다. 결과적으로 MySQL 입장에서 'Cafeteria'와 'Cafetería'는 같은 값이다.

SELECT 'Cafeteria' = 'Cafetería' COLLATE utf8mb4_unicode_ci;
-- 결과: 1 (같음)

SELECT 'e' = 'é' COLLATE utf8mb4_unicode_ci;
-- 결과: 1 (같음)

SELECT 'resume' = 'résumé' COLLATE utf8mb4_unicode_ci;
-- 결과: 1 (같음)

비교뿐만 아니라 unique key 판단에도 동일한 collation이 적용된다. 즉, utf8mb4_unicode_ci collation의 컬럼에 unique key가 걸려 있다면, 'Cafeteria'와 'Cafetería'는 unique key 충돌로 처리된다.

만약 accent를 구분해서 저장하고 싶다면 utf8mb4_0900_as_cs(MySQL 8.0+, accent-sensitive & case-sensitive) 계열의 collation을 선택해야 한다. 단, collation 변경은 기존 인덱스 재빌드를 수반하고, 애플리케이션 전체에서 비교 동작이 달라지는 영향을 동반하기 때문에 신중하게 결정해야 한다.

이것을 확인하는 순간, 퍼즐이 맞춰지기 시작했다.

---

INSERT IGNORE의 조용한 스킵

utf8mb4_unicode_ci에서 두 값이 동일하게 취급된다는 건, unique key 관점에서도 충돌로 인식된다는 뜻이다. 그리고 SQL에는 INSERT IGNORE가 쓰여 있었다.

시나리오를 재구성하면 이렇다.

1. SQL 파일은 위에서 아래로 순서대로 실행된다. 'Cafeteria'(악센트 없음)가 먼저 INSERT된다.
2. 이후 'Cafetería'(악센트 있음) INSERT 시도 → utf8mb4_unicode_ci 기준으로 'Cafeteria'와 동일 → unique key 충돌.
3. INSERT IGNORE이므로 에러 없이 조용히 스킵.
4. DB에는 먼저 들어온 'Cafeteria'만 남는다.

INSERT IGNORE는 충돌 시 에러 대신 경고(warning)를 낸다. 하지만 배치 부트스트랩에서 warning 로그를 집중적으로 보는 사람은 많지 않다. 수백 개의 caption을 UNION ALL로 한꺼번에 밀어 넣는 SQL이라면 특히 그렇다. 실행 성공 로그만 찍히고, 내부에서 몇 건이 스킵됐는지는 별도로 확인하지 않으면 알 수 없다.

증상이 나타나기 전까지는 이 스킵이 일어났다는 사실 자체를 알 수 없었다. INSERT IGNORE가 편리한 이유가 정확히 이 디버깅을 어렵게 만든 이유였다.

스킵 여부를 사후에 확인하려면 아래처럼 warning을 조회할 수 있다.

-- INSERT 직후 실행
SHOW WARNINGS;

-- warning 발생 건수만 확인
SHOW COUNT(*) WARNINGS;

다만 이건 해당 세션에서 마지막으로 실행된 문장의 warning만 보여준다. 부트스트랩처럼 여러 문장이 연속으로 실행되는 구조에서는 개별 warning을 모두 추적하기 어렵다. 결국 가장 안전한 방법은 적재 후 건수를 검증하는 단계를 별도로 두는 것이다.

---

두 시스템의 "같다" 기준이 달랐다

DB에 'Cafeteria'만 남은 상태에서 캐시를 로드하면, 이 값을 canonicalize한 키 "cafeteria"가 캐시에 올라간다.

런타임에 공급사가 "Cafetería"를 보내면, Java canonical key는 어떻게 생성될까?

Normalizer.normalize("Cafetería", Normalizer.Form.NFKC)
// NFKC: 호환 분해(NFKD) 후 정준 결합(NFC)
// í (U+00ED)는 이미 NFC 형태의 precomposed 문자 → 분해 후 다시 결합, 그대로 유지
// 결과: "Cafetería" (변화 없음)
    .lowercase(Locale.ROOT)
// 결과: "cafetería"

캐시의 키는 "cafeteria", 조회 키는 "cafetería". 이 둘은 Java에서 다른 문자열이다. "cafeteria".equals("cafetería")는 false다. 캐시 miss → UNMATCHED.

여기서 NFKC와 NFKD의 차이를 짚어두는 게 좋다.

정규화 형식	처리 방식	accent 처리
NFC	정준 분해 후 정준 결합	precomposed 형태로 유지
NFD	정준 분해	기본 문자 + combining mark로 분리
NFKC	호환 분해 후 정준 결합	precomposed 형태로 유지
NFKD	호환 분해	기본 문자 + combining mark로 분리

NFKC와 NFKD의 차이는 마지막 단계에 있다. NFKD는 분해만 하고 결합을 하지 않는다. 즉, í(U+00ED)를 i(U+0069)와 combining accent(U+0301)로 분리한 채로 둔다. 이 상태에서 combining mark를 제거하면 accent가 사라진 기본 문자만 남는다. NFKC는 분해 후 다시 결합(NFC)하기 때문에, í는 결국 다시 í(U+00ED)가 된다. accent 제거 효과가 없다.

정리하면 이렇다.

시스템	비교 기준	결과
MySQL (utf8mb4_unicode_ci)	UCA 기반, accent = secondary weight → 비교 시 무시	'Cafeteria' == 'Cafetería' ✅
Java NFKC + lowercase	유니코드 코드포인트 단위 비교	"cafeteria" ≠ "cafetería" ❌

두 시스템이 각자의 규칙 안에서는 완전히 정상이었다. 문제는 이 두 규칙이 서로 다르다는 데 있었다. 그리고 그 불일치는, 두 시스템의 경계에서만 드러났다.

---

해결: NFKD + combining mark 제거

MySQL의 accent-insensitive 동작과 Java canonical key를 일치시키면 된다.

utf8mb4_unicode_ci가 accent를 무시하는 방식은 내부적으로 UCA에 따라 악센트를 collation weight 계산에서 제외하는 것이다. Java에서 이와 동등한 처리를 하려면 NFKD 정규화 후 combining mark를 제거하면 된다.

NFKD는 í(U+00ED)를 기본 문자 i(U+0069)와 combining accent(U+0301)로 분리한다. 이때 \p{M}은 Java 정규식에서 유니코드 Mark 카테고리 전체를 의미하며, combining accent, diaeresis, tilde 등 악센트 관련 combining mark를 모두 포함한다. 이 패턴으로 제거하면 i만 남는다.

fun canonicalize(raw: String): String {
    return Normalizer.normalize(raw, Normalizer.Form.NFKD)
        .replace("\\p{M}".toRegex(), "")  // combining mark (악센트 등) 제거
        .replace(whitespaceRegex, " ")
        .trim()
        .lowercase(Locale.ROOT)
}

변경 후 동작을 단계별로 보면 이렇다.

입력:   "Cafetería"
NFKD:   "Cafetería" → 'i' + U+0301 (combining acute accent) 로 분리
\p{M}:  combining mark 제거 → "Cafeteria"
trim:   "Cafeteria"
lower:  "cafeteria"

동일하게 DB에서 읽어온 'Cafeteria'도 같은 경로를 거친다.

입력:   "Cafeteria"
NFKD:   "Cafeteria" (combining mark 없음, 변화 없음)
\p{M}:  아무것도 제거되지 않음 → "Cafeteria"
lower:  "cafeteria"

캐시 키 "cafeteria"와 조회 키 "cafeteria". 일치한다.

\p{M}이 제거하는 범위를 한 번 더 확인해 두면 좋다. M 카테고리는 Mn(Non-spacing mark), Mc(Spacing combining mark), Me(Enclosing mark) 세 가지를 포함한다. accent, tilde, cedilla, umlaut 등 대부분의 발음기호 관련 combining mark가 Mn에 해당한다. 즉, 'Cafetería' → 'Cafeteria'뿐만 아니라 'résumé' → 'resume', 'naïve' → 'naive', 'garçon' → 'garcon'같은 변환도 동일하게 처리된다. 공급사 caption에 유럽어 표기가 섞여 있는 경우라면 이 범위가 의도와 일치하는지 미리 확인해 두는 것이 좋다.

---

마무리

이 버그의 근본 원인은 코드 오류도, 설정 실수도 아니었다. 두 시스템이 "동등하다"를 판단하는 기준이 서로 달랐고, 그 경계에서 데이터가 조용히 소실됐다. 코드만 아무리 들여다봐도 찾을 수 없었던 이유가 여기 있었다. 문제는 코드 한 줄이 아니라 두 시스템의 설계 사이에 있었다.

여러 시스템이 맞물릴 때는 각 시스템의 동등성 기준을 명시적으로 확인해야 한다. MySQL collation은 어떤 문자를 같다고 보는가, Java 문자열 비교는 어느 수준에서 동작하는가, 이 두 기준이 맞닿는 지점에서 일치하는가. 특히 DB와 애플리케이션 계층이 분리된 구조에서, 캐시처럼 두 계층의 데이터가 직접 비교되는 지점은 이런 불일치가 가장 조용히, 가장 오래 숨어있을 수 있는 곳이다.

INSERT IGNORE처럼 오류를 삼키는 패턴은 문제가 발생했다는 사실 자체를 숨긴다. 대량 부트스트랩 SQL에서 예상치 못한 스킵이 발생해도 아무런 신호가 없다. 이런 패턴을 쓸 때는 두 가지를 반드시 점검해야 한다. 하나는 unique key 판단에 쓰이는 collation이 의도와 일치하는지, 다른 하나는 적재 후 건수를 검증하는 단계가 있는지다. 경고 하나를 놓쳤다고 배포가 실패하진 않지만, 그 경고가 수개월 뒤 UNMATCHED 버그로 돌아올 수 있다.

두 시스템이 각자의 규칙 안에서 정상이더라도, 규칙이 다른 두 시스템을 연결할 때는 경계 지점의 동작을 명시적으로 정의하고 검증해야 한다. 이 원칙은 collation과 canonicalization에 국한되지 않는다. 날짜 파싱, 숫자 포맷, 인코딩, 타임존 등 두 시스템이 같은 데이터를 서로 다른 규칙으로 해석할 수 있는 모든 경계에서 동일하게 적용된다.

---

오탈자 및 오류 내용을 댓글 또는 메일로 알려주시면, 검토 후 조치하겠습니다.