MYSQL에 이모티콘(emoji) 저장하기 + error 처리(mysql row size too large. the maximum row size for the used table type not counting blobs is 65535)

MYSQL을 이용하여 DB를 관리하던 도중, 이모티콘(이모지-emoji)를 저장하면

 

error를 띄우거나 '?' 로 저장되는 현상이 발생합니다

 

이는 MYSQL의 character type이 이모지를 저장하지 못하는 utf8로 설정되어 있기 때문입니다

 

따라서 이를 해결하기 위해  MYSQL 5.5.3  버전 이후부터 제공하는 utf8mb4로 character set을 설정하여 해결할 수 있습니다.

 

utf8과 utf8mb4의 차이점?

이모지와 같은 글자들은 글자당 최대 4bytes가 필요합니다

 

하지만 utf8은 글자당 최대 3bytes까지 지원하는 가변 자료형 입니다

 

따라서 가변 4bytes의 문자열을 저장할 수 있는 utf8mb4를 사용하면 이모지를 저장할 수 있습니다.

 

로컬에서 Mysql을 사용하는 경우 해결법

vi /etc/my.cnf

위 코드를 통해 데이터베이스 설정파일로 들어가 해당 내용을 아래와 같이 변경 및 추가합니다

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

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

mysql 서비스를 재시작합니다

sudo service mysql restart

다음 쿼리를 통해 변경이 잘 되었는지 확인합니다

SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'

 

마지막으로 기존에 만들어져있던 DB의 언어셋과 collate를 변경합니다

ALTER DATABASE [데이터베이스 이름] CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

AWS RDS를 사용하는 경우 해결법

AWS에 접속하여 [서비스 - RDS]로 들어가 [파라미터 그룹]을 수정합니다

 

파라미터 검색에 character와 collation을 입력하여 각각 [파라미터 편집]을 통하여 다음과 같은 값으로 변경합니다

파라미터에 [character, collation] 검색 후 아래와 같은 값으로 파라미터 값 변경

character_set_client  : utf8mb4

character_set_connection : utf8mb4

character_set_database : utf8mb4

character_set_results : utf8mb4

character_set_server : utf8mb4

collation_connection : utf8mb4_unicode_ci

collation_server : utf8mb4_unicode_ci 

 

RDS DB를 재부팅합니다

다음 쿼리를 통해 변경이 잘 되었는지 확인합니다

SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'

 

마지막으로 기존에 만들어져있던 DB의 언어셋과 collate를 변경합니다

ALTER DATABASE [데이터베이스 이름] CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

추가적으로  Node.JS   를 이용하고 있다면 다음 게시글을 참고하여 Node.JS의 character set도 변경합니다

[Server/NodeJS] - Node.JS에 이모티콘(emoji) 저장하기

 

error handling 1) [mysql row size too large. the maximum row size for the used table type not counting blobs is 65535]

위와 같은 방법으로 utf8 에서 utf8mb4로 바꾸게 되면 char 당 바이트 수가 늘어나기 때문에

 

기존의 DB에 varchar(10000)과 같이 char형이 많이 쓰인다면 maximum row size를 벗어나므로

 

다음과 같은 에러를 만나게 됩니다

mysql row size too large. the maximum row size for the used table type not counting blobs is 65535

 

이러한 에러는 해당  varchar  를  Text  로 type을 변경하면 해결할 수 있습니다

 

error handling 2) Illegal mix of collations (utf8_unicode ci, IMPLICIT) and (utf8 general_ci, IMPLICIT) for operation '='

일부 table에 대해서만 utf8mb4로 변경하거나 에러 내용과 같이 collation을 통일시켜 주지 않으면

 

query에서 character set 혹은 collation이 다른 table끼리 비교 혹은 join 연산을 할 시에

 

다음과 같은 에러를 만납니다

Illegal mix of collations (utf8_unicode ci, IMPLICIT) and (utf8 general_ci, IMPLICIT) for operation '='

이는 해당 query문에서 비교되는 모든 table의 collation을 통일하면 해결 가능합니다