MySQL 이모지 입력 문제와 해결 방법 🙏

0. “이모지 입력이 안되는데요 😰”

문제 상황

모니터링 도중 특정 테이블에서 입력이 실패하는 오류가 발생했다.

[1366] Incorrect string value: '\xF0\x9F\x98\x80' for column 'name' at row 1

에러 케이스를 분석한 결과, 공통적으로 입력 값에 이모지가 포함된 걸 발견했다. 일반 텍스트와 이모지는 어떤 차이가 있는걸까?

 

원인

문제가 되는 컬럼의 문자집합은 utf8이었다.

MySQL 이모지 관련 검색을 해보니 MySQL의 문자집합인 utf8은 3바이트까지만 지원하기 때문에, 4바이트 크기의 이모지를 저장하려고 하면 오류가 발생한다고 한다.

때문에 이를 해결하기 위해 문자 집합을 utf8에서 utf8mb4로 변경하고, 콜레이션을 utf8mb4_unicode_ci 로 설정해야 한다.

 

해결

• 테이블의 문자 집합 및 콜레이션 변경

ALTER TABLE [테이블명] 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

 

• 컬럼의 문자 집합 및 콜레이션 변경

ALTER TABLE [테이블명] 
MODIFY COLUMN [컬럼명] [문자열 컬럼 타입] 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

 

 

간단한 해결책..처럼 보이지만 바로 운영 환경에 실행하기는 어렵다. ALTER 명령을 실행하면 테이블이 잠기고, 이로 인해 쓰기 작업에 문제가 발생할 수 있기 때문이다.

문제를 해결하기 위해 알아보니, 두 가지 방법이 있었다.

 

1. pt-online-schema-change 툴을 이용해 실시간으로 테이블 변경 (잠금 최소화)
2. 트래픽이 적은 시간대에 점검 후 작업을 진행하는 방법

1번 방법을 시도해보고 싶었지만, 팀 내에 pt-online-chema-change 사용 경험이 없어 실행이 어려웠다. 대신 운영 DB와 유사한 환경에서 ALTER 문을 실행해 예상 작업 시간을 측정한 뒤 2번 방법으로 결정했다.

점검을 한 후 작업을 진행했기 때문에 별다른 이슈 없이 테이블 변경을 마칠 수 있었다.

 

 

 

1. 문자집합(캐릭터 셋)과 콜레이션

문자집합 (Chracter set)

문자집합은 문자열 컬럼(CHAR, VARCHAR, TEXT)에만 설정할 수 있다. MySQL에서는 컬럼단위까지 문자집합을 관리할 수 있다.

-- MySQL 서버에서 사용 가능한 문자 집합
SHOW CHARACTER SET;

• euckr
  • 한국어 전용으로 사용되는 문자집합
  • 모든 글자는 1~2바이트를 사용
• utf8mb4
  • 다국어 문자를 포함할 수 있는 컬럼에 사용하기 적합
  • 한 글자를 저장하기 위해 1~4바이트까지 사용
• utf8
  • utf8mb4의 부분집합
    • utf8mb4가 도입되기 이전에 주로 사용됨
  • 다국어 문자를 포함할 수 있는 컬럼에 사용하기 적합
  • 한 글자를 저장하기 위해 1~3바이트까지 사용

 

최근 웹 서비스나 스마트폰 애플리케이션에서는 여러 나라의 언어를 동시에 지원하기 위해 기본적으로 UTF-8(utf8mb4)을 많이 사용하는 추세

 

콜레이션 (Collation)

• 문자열 컬럼의 값을 비교하거나 정렬하는 기준

문자열 컬럼의 값을 비교하거나 정렬할 때는 항상 문자 집합 뿐 아니라 콜레이션의 일치 여부에 따라 결과가 달라지며, 쿼리의 성능 또한 상당한 영향을 받는다.

-- MySQL 서버에서 사용 가능한 콜레이션 목록
SHOW COLLATION;

• 3개의 파트로 구성된 콜레이션 이름
  • 첫 번째 파트 : 문자 집합의 이름
  • 두 번째 파트 : 해당 문자 집합의 하위 분류
  • 세 번째 파트 : 대문자나 소문자의 구분 여부
    • ci : 대소문자를 구분하지 않는 콜레이션 (Case Insensitive)
    • cs : 대소문자를 별도의 문자로 구분하는 콜레이션 (Case sensitive)
• 2개의 파트로 구성된 콜레이션 이름
  • 첫 번째 파트: 문자 집합의 이름
  • 두 번째 파트 : bin
    • bin : 이진 데이터 (binary)
      • 이진 데이터로 관리되는 문자열 컬럼은 콜레이션을 가지지 않는다. 콜레이션이 ‘xxx_bin’이라면 비교 및 정렬은 실제 문자 데이터의 바이트 값 기준으로 수행된다.

-- 컬럼의 문자집합, 콜레이션 조회
SELECT column_name, column_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = '[테이블 스키마명]' AND table_name = '[테이블 명]';

 

2. 3바이트 이모지는 입력이 되나요?

MySQL의 문자집합인 utf8은 3바이트까지만 지원하기 때문에,
4바이트 크기의 이모지를 저장하려고 하면 오류가 발생한다

 

 

‘4바이트 크기의 이모지’를 저장할 때 오류가 발생한다면 3바이트 크기의 이모지는 입력 가능한 걸까?

문자집합을 utf8로 세팅하고 3바이트 이모지를 찾아봤다. 3바이트 크기의 이모지는 ☕️, ☀️ , ❤️ 등이 있다.

SELECT
    emoji,
    CHAR_LENGTH(emoji) AS char_length,
    LENGTH(emoji) AS byte_length
FROM (
         SELECT '[이모지1]' AS emoji UNION ALL
         SELECT '[이모지2]' UNION ALL
         SELECT '[이모지3]'
     ) AS emojis;

• CHAR_LENGTH(): 문자열의 문자 수를 반환 (유니코드 코드포인트 기준).
• LENGTH(): 문자열의 바이트 길이를 반환 (인코딩된 실제 크기 기준).

입력한 이모지는 하나인데 CHAR_LENGTH는 2, LENGTH는 6이 나왔다. 그 이유는 ☕️ 이모지가 조합형 이모지이기 때문이다.

☕️ 이모지가 MySQL에서 CHAR_LENGTH가 2, LENGTH가 6 바이트로 나오는 이유는 이모지가 두 개의 유니코드 코드포인트로 구성되어 있기 때문입니다. 각각의 코드포인트가 UTF-8로 인코딩될 때 차지하는 바이트 수를 합산하면 총 6 바이트가 됩니다.

 

 

 

☕️

U+2615 (Hot Beverage, ☕): 

• 하얀색 커피잔 이모지.
  • 텍스트 스타일(흑백)
• UTF-8로 인코딩 시 3 바이트.
  • E2 98 95 (3 바이트)

U+FE0F (Variation Selector-16, VS16, ️):

• 컬러 이모지 스타일을 지정하는 변형 선택자.
• UTF-8로 인코딩 시 3 바이트.
  • EF B8 8F (3 바이트)

이렇게 두 개의 유니코드가 조합된 이모지가 ☕️ 이다.

실제로 문자집합을 utf8로 세팅하고, 해당 3바이트 이모지를 입력하면 삽입/조회 둘 다 문제 없이 잘 되는 걸 확인했다.

하지만 4바이트 이모지가 대부분이므로, 당연히 utf8mb4 을 사용하는게 좋다.

 

3. 이모지 입력은 되는데 이모지가 ?로 보여요.

문자집합과 콜레이션을 변경하고 이모지를 저장하니 오류 없이 잘 저장되었다. 하지만 이상하게 해당 레코드를 조회하면 이모지가 ? 로 보이는게 아닌가.

이번엔 무슨 문제인고 알아보니 문자 집합 시스템 변수 설정이 원인이었다.

 

-- character_set 변수 조회
SHOW VARIABLES LIKE 'character%';

 

 

SHOW VARIABLES로 문자집합 관련 시스템 변수를 조회해보니 다른 변수는 다 utf8mb4 인데 character_set_results 변수가 utf8mb3 로 되어 있었다.

이 말은 DB 자체에는 이모지가 정상적으로 저장되었지만, 쿼리 처리 결과를 클라이언트에 보낼 때 사용하는 문자 집합 character_set_results 변수가 utf8mb3라서 ?로 깨져 보였던 것이다.

 

 

SET character_set_results = 'utf8mb4';

문자 집합이 일치하도록utf8mb4로 변경해주면 이모지가 정상적으로 보인다.

 

 

문자 집합 시스템 변수

• character_set_system
  • MySQL 서버가 식별자를 저장할 때 사용하는 문자 집합
  • 사용자가 설정하거나 변경할 필요 X
• character_set_server
  • MySQL 서버의 전역 기본 문자 집합
  • DB나 테이블 또는 컬럼에 아무런 문자 집합이 설정되지 않을 때 이 시스템 변수에 명시된 문자 집합이 기본으로 사용된다.
• character_set_database
  • MySQL DB의 기본 문자 집합
  • DB를 생성할 때 아무런 문자 집합이 명시되지 않았다면 이 시스템 변수에 명시된 문자 집합이 기본값으로 사용된다.
    • 이 변수가 정의되지 않으면 character_set_server 시스템 변수에 명시된 문자 집합이 기본으로 사용된다.
• character_set_filesystem
  • LOAD DATA INFILE … 또는 SELECT … INTO OUTFILE 문장을 실행할 떄 인자로 지정되는 파일의 이름을 해석할 때 사용되는 문자 집합
    • 데이터 파일의 내용을 읽을 떄 사용하는 문자 집합이 아니라 파일의 이름을 찾을 때 사용하는 문자 집합
  • 각 커넥션에서 임의 문자 집합으로 변경해서 사용 가능
• character_set_client
  • MySQL 클라이언트가 보낸 SQL 문장은 character_set_client에 설정된 문자 집합으로 인코딩해서 MySQL 서버로 전송한다.
  • 각 커넥션에서 임의 문자 집합으로 변경해서 사용 가능
• character_set_connection
  • MySQL 서버가 클라이언트로부터 전달받은 SQL 문장을 처리하기 위해 character_set_connection의 문자 집합으로 변환한다. 또한 클라이언트로부터 전달받은 숫자 값을 문자열로 변환할 때도 character_set_connection에 설정된 문자 집합이 사용된다.
  • 각 커넥션에서 임의 문자 집합으로 변경해서 사용 가능
• character_set_results
  • MySQL 서버가 각 쿼리의 처리 결과를 클라이언트로 보낼 때 사용하는 문자 집합
  • 각 커넥션에서 임의의 문자 집합으로 변경해서 사용할 수 있다.

 

참고자료

•  https://product.kyobobook.co.kr/detail/S000001766483

Real MySQL 8.0 (2권) | 백은빈 - 교보문고