[MariaDB] 문자셋 변경 utf8 -> utf8mb4

 

MariaDB의 문자셋을 UTF8로 설정해 두고 사용하고 있었는데 SQL이 오류가 발생했다. 로그를 봐도 깨진 문자열만 보이고 해당 문제가 무슨 글자인지 몰랐다.
인코딩이 맞지 않아서 깨진 것처럼 보이는 줄 알았는데 아니었다.

알고 보니 이모지(🎈🎄🎃)가 문제였다.

UTF-8의 경우 4바이트의 인코딩인데, MySQL, MariaDB의 경우 utf8이 3바이트로 구현되어 있었다. 그 이후 MySQL 5.5.3(2010년 3월), MariaDB에서 4바이트 길이인 utf8mb4가 추가되었다.

유니코드 U+10000 이상의 문자는 UTF-8로 인코딩을 하려면 4바이트가 필요한데, 최근 많이 사용되고 있는 이모지가 그 유형이었다.

이모지는 윈도우10에서 (윈도우+. 사용 가능)

utf8mb4 설정

MySQL/MariaDB에서 utf8mb4를 사용하려면 my.cnf 설정 파일 수정

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqldump]
default-character-set = utf8mb4

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

• skip-character-set-client-handshake

DB서버와 접속을 할 때 클아이언트는 자신이 사용할 문자셋 설정하며, 그 설정을 사용하도록 되어있지만 skip-character-set-client-handshake 옵션을 사용할 경우 클라이언트에서 설정한 문자셋을 무시하고 character_set_server값으로 설정됩니다.

데이터베이스 문자셋 변경

ALTER DATABASE {database} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

 

기존 테이블 문자셋 변경

기존에 사용하던 테이블의 문자셋을 변경의 경우 CONVERT TO 명령어로 진행하는 경우로 할 경우 해당 테이블 및 컬럼의 속성까지 함께 변경

ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

ALTER TABLE tbl_name 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

컬럼의 문자셋의 변경을 하지 않고 테이블에 대한 문자셋 변경만을 진행할 경우

ALTER TABLE tbl_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

• 변경 스크립트 일괄 생성

# {database}는 해당 스키마 명

SELECT 
    CONCAT('ALTER TABLE `', table_schema, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS t
FROM information_schema.TABLES
WHERE table_schema = '{database}'
AND table_type = 'BASE TABLE' # 없는 경우 VIEW까지 포함되어 오류 발생
ORDER by table_name;

• 생성 결과

ALTER TABLE `database`.`tbl_name1` 
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `database`.`tbl_name2` 
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `database`.`tbl_name3` 
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

 

문제 - 총 ROW SIZE 65535Byte 초과

문자열을 변경하는 경우 MySQL, MariaDB의 경우 한 ROW의 길이 제한이 존재하여, 3byte->4byte로 변환되어 컬럼의 데이터를 수정 가능성 있음 varchar의 일부 길이를 변경하거나, TEXT로 변환 필요

(약 VARCHAR 65,535를 할 경우 16,380길이가 가능)

문제 - INDEX의 길이가 767byte 초과하는 경우 오류

하나의 컬럼으로 생성할 수 있는 INDEX의 최대 길이는 767byte이다.
utf8일 경우 약 varchar(255)이며 (255 * 3 = 765)이며,
utf8mb4로 변경하는 경우 varchar(191)까지 가능하여 오류가 발생할 수 있다.(191 * 4 = 764byte)

innodb_large_prefix 옵션(기본 값)을 활성화할 경우 3,072바이트까지 사용 가능하다.

MariaDB 10.3.1 부터는 제거됨, 활성화가 기본

참고

• https://medium.com/oldbeedev/mysql-utf8mb4-character-set-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0-da7624958624
• https://dev.mysql.com/doc/refman/5.6/en/alter-table.html#alter-table-character-set
• https://mariadb.com/kb/en/innodb-limitations/
• https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

#mariadb #mysql #utf8 #utf8mb4 #4byte #emoji