MySQL 의 utf8 은 실제 UTF-8 이 아니다. 한 글자를 최대 3바이트까지만 담는 축약 구현이라 이모지와 일부 한자·보조 평면 문자를 저장하지 못한다. MySQL 8.0.29 이상은 이 이름을 utf8mb3 의 별칭으로 취급하며 사용을 권장하지 않는다고 명시한다. 새로 만들거나 바꿀 때는 예외 없이 utf8mb4 를 쓴다.
콜레이션은 서버 기본값을 따르되, MySQL 8.0 은 utf8mb4_0900_ai_ci, MariaDB 는 utf8mb4_uca1400_ai_ci(10.10 이상) 또는 utf8mb4_general_ci 가 흔하다. 서로 다른 콜레이션의 컬럼을 조인하면 Illegal mix of collations 오류가 나므로 한 데이터베이스 안에서는 하나로 통일한다.
SHOW VARIABLES LIKE 'character\_set\_%';
SHOW VARIABLES LIKE 'collation\_%';
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'appdb';
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'appdb';
컬럼 단위로 다른 문자셋이 섞여 있는 경우가 흔하므로 컬럼도 함께 본다.
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'appdb' AND character_set_name IS NOT NULL
AND character_set_name <> 'utf8mb4';
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
서버 기본값은 앞으로 만들 객체에만 적용된다. 이미 있는 데이터베이스·테이블·컬럼은 따로 바꿔야 한다.
ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CONVERT TO CHARACTER SET 은 테이블을 다시 쓰므로 큰 테이블에서는 오래 걸리고 잠금이 걸린다. 변환 대상 목록은 쿼리로 만들어 둔다.
SELECT CONCAT('ALTER TABLE `', table_name,
'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_type = 'BASE TABLE';
한국 환경에서 가장 흔한 상황은 애플리케이션은 UTF-8 로 보냈는데 컬럼이 latin1 이라 UTF-8 바이트가 latin1 로 해석되어 들어간 경우다. 이 상태에서 CONVERT TO CHARACTER SET 을 그냥 돌리면 latin1 글자를 다시 UTF-8 로 변환하면서 두 번 깨진다.
이때는 바이트를 그대로 둔 채 해석만 바꿔야 하므로 VARBINARY/BLOB 을 거친다.
ALTER TABLE users MODIFY name VARBINARY(255);
ALTER TABLE users MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
TEXT 계열은 BLOB 을 거친다. 길이 제한에 주의한다. VARCHAR(N) 은 문자 수 기준이지만 인덱스는 바이트 기준이므로, latin1 에서 utf8mb4 로 바꾸면 Specified key was too long 이 날 수 있다. 이 경우 인덱스 길이를 KEY idx_name (name(191)) 처럼 줄이거나, innodb_large_prefix(MySQL 5.7 이하)·ROW_FORMAT=DYNAMIC 을 확인한다.
어느 경로든 작업 전에 덤프를 받아 둔다. 변환은 되돌릴 수 없다.
서버와 테이블을 다 바꿔도 클라이언트가 다른 문자셋으로 접속하면 여전히 깨진다. JDBC 는 드라이버 8.0 이상이면 characterEncoding=UTF-8 을 따로 주지 않아도 서버 값을 따르지만, 오래된 커넥터는 명시가 필요하다. 접속 후 확인은 다음과 같다.
SHOW VARIABLES LIKE 'character_set_client';
SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'character_set_results';