MySQL collation 을 적지 않은 테이블은 서버를 따라갑니다 — 같은 DDL, 다른 UNIQUE

같은 마이그레이션을 두 MySQL 서버에 적용하면 같은 UNIQUE 제약이 생긴다고 믿었습니다. 재현해 보니 아니었습니다. CREATE TABLE 에 collation 을 적지 않으면 테이블은 서버 기본값을 물려받고, 서버의 collation_server 가 다르면 «같은 값»의 기준도 달라집니다. 에러는 나지 않습니다.

RDS 에 MySQL 인스턴스를 새로 만들기 전에 파라미터 그룹을 훑다가 collation_server 에서 멈췄습니다. 로컬과 RDS 의 테이블이 같다고 여겨 온 근거는 마이그레이션 파일이 같다는 것뿐이었는데, 그 파일에는 collation 이 없었습니다. 아래 출력은 로컬 Docker 의 MySQL 8.4.11 두 대에서 재현한 것이고, 파라미터 그룹으로 서버 기본값을 바꾼 상황은 RDS 대신 서버 시작 옵션으로 흉내 냈습니다.

서버에서 컬럼까지, collation 은 적지 않으면 물려받습니다

MySQL 8.0·8.4 매뉴얼의 서버 기본값은 문자셋 utf8mb4, collation utf8mb4_0900_ai_ci 입니다. 매뉴얼은 이 값이 CREATE DATABASE 에 지정이 없을 때 기본값으로 쓰인다고 적은 뒤 "They have no other purpose." 라고 덧붙입니다.

그 아래도 같습니다. CREATE TABLE 에 지정이 없으면 DB 의 값을, 컬럼에 지정이 없으면 테이블의 값을 씁니다. 어디에도 적지 않으면 컬럼의 비교 규칙은 DB 를 만든 순간의 서버 설정이 정합니다.

매뉴얼의 정의로 문자셋은 기호와 인코딩의 집합이고, collation 은 문자를 비교하는 규칙의 집합입니다. 뒤의 두 서버도 문자셋은 둘 다 utf8mb4 였습니다.

ORM 이 만든 DDL 에는 collation 이 없습니다

collation 을 물려받는 사슬의 맨 아래인 CREATE TABLE 은 대개 ORM 이 씁니다. varchar(100) 컬럼에 uniqueIndex 하나를 건 스키마로 drizzle-kit 0.31.10 이 생성한 DDL 입니다.

CREATE TABLE `tag` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	CONSTRAINT `tag_id` PRIMARY KEY(`id`),
	CONSTRAINT `uq_tag_name` UNIQUE(`name`)
);

CHARACTER SET 도 COLLATE 도 없습니다. drizzle-kit 0.x 에는 테이블 수준 문자셋을 지정할 수단이 없고, v1 베타 브랜치도 컬럼에 지정했을 때 컬럼 수준 절만 붙일 뿐 테이블 옵션은 내지 않습니다. 이 UNIQUE 가 무엇을 중복으로 볼지는 파일이 아니라 파일을 받는 서버의 collation 이 정합니다. 다른 ORM 은 확인하지 않았습니다.

RDS 파라미터 그룹이 서버 기본값을 쥡니다

RDS 에서 이 기본값은 파라미터 그룹의 character_set_server 와 collation_server 입니다. describe-engine-default-parameters 로 mysql8.4 패밀리를 조회하면 두 항목의 ParameterValue 는 응답에 아예 없고, Source 는 engine-default 로 나옵니다(mysql8.0 도 같습니다). MySQL 기본값에 맡겨 둔 것으로 읽었습니다.

실제 값은 인스턴스에 연결된 그룹이 정합니다. 그룹을 지정하지 않으면 기본 그룹을 쓰는데, 기본 그룹은 수정할 수 없어 값을 바꾸려면 커스텀 그룹을 만들어야 합니다. 커스텀 그룹 이름에는 마침표를 쓸 수 없어서 default.mysql8.0 같은 이름은 기본 그룹만 갖습니다.

반면 커스텀 이름 규칙(문자·숫자·하이픈)에는 default 라는 글자를 막는 조항이 없습니다. default- 로 시작해도 커스텀 그룹일 수 있고, 그 안의 collation_server 는 바뀌어 있을 수 있습니다.

AWS 문서는 문자셋·collation 파라미터를 인스턴스와 DB 를 만들기 전에 정하라고 하고, 나중에 바꾸면 기존 데이터베이스에는 적용되지 않는다고 적습니다. 서버 기본값이 CREATE DATABASE 에서만 쓰인다는 설명과 맞물립니다. 다만 기본 그룹에 실제로 어떤 값이 들어 있는지는 조회하지 않았습니다.

같은 9개 INSERT, 서로 다른 UNIQUE 거부

서버 A 는 기본 설정 그대로 두고, 서버 B 만 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci 로 띄웠습니다. 같은 스크립트로 collation 없이 DB 와 테이블을 만들자 둘의 collation 이 서버 A 는 utf8mb4_0900_ai_ci, 서버 B 는 utf8mb4_general_ci 로 나왔고, 그 테이블의 UNIQUE(name) 에 아홉 값을 한 줄씩 넣었습니다.

순서	넣은 값	서버 A (0900_ai_ci)	서버 B (general_ci)
1	'abc'	저장	저장
2	'ABC'	거부	거부
3	'abc ' (끝에 공백)	저장	거부
4	'ss'	저장	저장
5	'ß'	거부	저장
6	'A'	저장	저장
7	'Ａ' (전각)	거부	저장
8	'😀'	저장	저장
9	'😁'	저장	거부

갈라진 ERROR 1062(Duplicate entry) 줄만 출력에서 옮기면 이렇습니다. 위 두 줄이 서버 A, 아래 두 줄이 서버 B 입니다.

ERROR 1062 (23000) at line 11: Duplicate entry 'ß' for key 'tag.uq_tag_name'
ERROR 1062 (23000) at line 13: Duplicate entry 'Ａ' for key 'tag.uq_tag_name'

ERROR 1062 (23000) at line 9: Duplicate entry 'abc ' for key 'tag.uq_tag_name'
ERROR 1062 (23000) at line 15: Duplicate entry '?' for key 'tag.uq_tag_name'

오류는 stderr 로 나가 해당 INSERT 옆에 찍히지 않아서, at line N 을 스크립트 줄 번호(첫 INSERT 가 7번 줄)에 대 보고 남은 행의 빈 id 로 한 번 더 맞췄습니다. InnoDB 는 실패한 INSERT 가 받아 간 AUTO_INCREMENT 값을 되돌리지 않는데, 빈 id(서버 A 2·5·7, 서버 B 2·3·9)가 거부된 순번과 그대로 겹쳤습니다. 15번 줄('😁')의 값이 ? 로 찍힌 것은 표시 문제일 수 있어 해석하지 않았습니다.

두 서버 모두 6행이 남았습니다. 개수만 같고 구성은 다릅니다. 서버 A 의 결과 표에서 'abc' 와 'abc ' 는 똑같은 줄로 보였고, HEX 열의 616263 과 61626320 에서만 끝의 공백이 드러났습니다.

utf8mb4_0900_ai_ci 와 utf8mb4_general_ci 는 다르게 합칩니다

처음에는 이 결과를 «0900_ai_ci 가 더 엄격하다»로 요약하려 했습니다. 틀린 요약이었습니다. 0900_ai_ci 는 ß 와 전각 문자를 합쳤고, general_ci 는 뒤 공백과 이모지를 합쳤습니다. 어느 쪽이 느슨한 게 아니라 합치는 대상이 다릅니다. COLLATE 를 명시해 같은 값을 비교한 결과는 두 서버에서 한 칸도 다르지 않았습니다.

비교 (COLLATE 명시)	0900_ai_ci	general_ci	매뉴얼에서 찾은 설명
'ABC' = 'abc'	1	1	둘 다 _ci (대소문자 무시)
'abc ' = 'abc'	0	1	pad 속성 (NO PAD / PAD SPACE)
'ß' = 'ss'	1	0	general_ci 는 확장을 지원하지 않음
'Ａ' = 'A'	1	0	찾지 못함
'😀' = '😁'	0	1	general 계열은 보충 문자를 모두 같게 봄

뒤 공백은 pad 속성의 차이입니다. 이름의 0900 은 UCA 9.0.0 기반이라는 뜻이고, 매뉴얼에 따르면 UCA 9.0.0 이상의 collation 은 NO PAD 라 끝의 공백도 다른 문자처럼 비교합니다. 대부분의 MySQL collation 은 끝의 공백을 무시하는 PAD SPACE 입니다. 같은 비교가 utf8mb4_bin 은 1, utf8mb4_0900_bin 은 0 이었으니 bin 이 붙었다고 규칙이 같지도 않습니다.

ß 는 확장의 차이입니다. 매뉴얼은 general_ci 를 확장(한 글자를 여러 글자의 조합과 같게 보는 것)을 지원하지 않는 레거시 collation 으로 설명하고, general_ci 에서는 ß = s, 확장을 지원하는 utf8mb4_unicode_ci 에서는 ß = ss 라고 적습니다. general_ci 에서 ß 와 s 가 부딪히는지는 넣어 보지 않았습니다.

이모지는 보충 문자의 가중치 차이입니다. 두 이모지는 HEX 가 4바이트(F09F9880, F09F9881)인 BMP 밖의 보충 문자입니다. 매뉴얼은 general 계열 collation 이 보충 문자에 0xfffd 의 가중치를 줘서 모든 보충 문자가 서로 같다고 적고, UCA 4.0.0 보다 새 버전에 기반한 collation 에서는 반드시 같은 가중치는 아니라고 적습니다. 전각 Ａ 가 0900_ai_ci 에서 A 와 같아지는 이유는 매뉴얼에서 찾지 못해 결과만 적어 둡니다.

막는 법 — collation 을 적고, 환경마다 물어봅니다

ORM 이 테이블과 컬럼에 적어 주지 않는다면 남는 자리는 DB 생성입니다. DB 를 만들 때 collation 을 명시하면 그 아래 테이블은 서버 설정이 아니라 이 값을 물려받습니다. 어떤 collation 이 맞는지는 무엇을 같은 값으로 볼지의 문제라 정답을 말하기 어렵지만, 그 결정은 서버 설정이 아니라 DDL 이 내려야 한다는 생각이 들었습니다.

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 환경마다 같은 조회를 돌려 나란히 비교
SELECT @@character_set_server, @@collation_server;
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'mydb';
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'mydb'
 ORDER BY TABLE_NAME, COLUMN_NAME;

이미 돌고 있는 환경은 이 조회를 환경마다 똑같이 돌려 대조하는 편이 그룹 이름을 믿는 것보다 확실합니다. RDS 그룹 쪽은 aws rds describe-db-parameters --db-parameter-group-name <그룹> --source user 로 출처가 user 인 항목만 추려 볼 수 있습니다. pad 속성은 information_schema.COLLATIONS 의 PAD_ATTRIBUTE 열에 있습니다.

나중에 바꿀 때는 순서를 조심해야 합니다. ALTER TABLE ... CONVERT TO CHARACTER SET ... COLLATE ... 는 모든 문자 컬럼(CHAR·VARCHAR·TEXT)의 collation 을 바꾸고, 예외는 문자셋이 binary 인 컬럼과 다중값 인덱스용 숨은 컬럼입니다. 그래서 컬럼 collation 을 먼저 지정하고 CONVERT 를 뒤에 실행하면 그 지정이 덮입니다. 기존 데이터가 새 기준에서 서로 같아지는 경우 변환이 어떻게 끝나는지는 재현하지 않았습니다.

남는 것 — 적지 않은 결정은 서버로 옮겨 갑니다

돌이켜 보면 제가 믿은 것은 «같은 파일이면 같은 스키마»였습니다. 컬럼 이름도 타입도 인덱스도 같았으니 틀린 말은 아닙니다. 다만 파일에 적히지 않은 결정이 하나 있었고, 그 결정은 사라진 게 아니라 DB 를 만든 순간의 서버 설정으로 옮겨 가 있었습니다. 마이그레이션 파일을 아무리 나란히 놓아도 보이지 않는 자리입니다.

더 곤란한 것은 조용함입니다. 거부된 값은 에러라도 남기지만, 한쪽 서버에서만 받아들여진 값은 아무것도 남기지 않습니다. 두 서버는 똑같이 6행을 남겼고, 행 수를 세는 확인으로는 이 차이가 보이지 않습니다.

«utf8mb4 면 같다»는 생각도 내려놓게 됐습니다. 문자셋이 같다는 것은 같은 문자를 담을 수 있다는 뜻일 뿐, 같은 문자열을 같다고 본다는 뜻은 아니었습니다. 다음에 두 환경의 스키마가 같다고 말하고 싶어지면, 그 «같다»가 컬럼 이름까지인지 비교 규칙(collation)까지인지부터 확인해 보려 합니다.

참고 문서

• MySQL 매뉴얼 — 문자셋과 collation 개요 · 서버(8.4, 8.0) · 데이터베이스 · 테이블 · 컬럼
• MySQL 8.4 매뉴얼 — 유니코드 문자셋 · 끝 공백 비교 · collation 이름 규칙 · COLLATIONS 테이블 · ALTER TABLE · InnoDB AUTO_INCREMENT
• AWS — RDS 파라미터 그룹 개요 · DB 파라미터 그룹 만들기 · describe-db-parameters
• drizzle-kit sqlgenerator.ts · RFC 3629 (UTF-8)