latin1 / utf8mb3 테이블을 utf8mb4로 변환

명령어

-- Columns that are not utf8mb4
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME <> 'utf8mb4'
  AND TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Generate conversion DDL for tables whose default charset is not utf8mb4
SELECT CONCAT('ALTER TABLE `', t.TABLE_SCHEMA, '`.`', t.TABLE_NAME,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=COPY, LOCK=SHARED;') AS ddl
FROM information_schema.TABLES t
JOIN information_schema.COLLATIONS c ON c.COLLATION_NAME = t.TABLE_COLLATION
WHERE c.CHARACTER_SET_NAME <> 'utf8mb4' AND t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

-- Schema default and a single table
ALTER DATABASE legacy CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE legacy.cust CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=COPY, LOCK=SHARED;

하는 일

utf8mb4가 아닌 컬럼과 테이블을 찾아 변환 DDL을 생성한다. CONVERT TO는 테이블 기본값과 모든 문자 컬럼을 함께 바꿔 이모지/4바이트 문자를 사용할 수 있게 한다.

사용 시점

5.x에서 마이그레이션된 스키마, 'Incorrect string value' (1366) 오류, collation이 다른 컬럼 간 조인이 인덱스를 사용하지 못할 때.

주의

CONVERT TO는 COPY로 테이블을 다시 쓰며 그동안 쓰기를 차단한다(INPLACE 미지원). 큰 테이블에서는 같은 --alter를 pt-online-schema-change / gh-ost로 실행한다. utf8mb4는 문자당 4바이트를 사용하므로 인덱스가 있는 VARCHAR(255)는 1020바이트가 된다. 한도는 DYNAMIC / COMPRESSED 행 포맷에서 3072바이트, COMPACT / REDUNDANT에서 767바이트이며 이를 넘으면 1071 오류가 발생한다. VARCHAR / TEXT 컬럼은 같은 문자 용량을 유지하기 위해 필요 시 더 큰 타입으로 승격된다(TEXT → MEDIUMTEXT). collation 변경은 정렬과 비교에 영향을 준다. utf8mb4_0900_ai_ci는 악센트와 대소문자를 구분하지 않으므로 이전에 달랐던 값이 unique 인덱스에서 충돌할 수 있다(1062). 조인되는 테이블은 함께 변환한다. 클라이언트 연결도 utf8mb4를 사용해야 한다.