java.sql.SQLException: Incorrect string value: '\xF0\x9F\x94\xB4\xEB\xB0...' for column 'XXXXX' at row 1

* 개요 

INSERT 중에 다음 오류 발생

 

org.springframework.jdbc.UncategorizedSQLException: 

Hibernate operation: could not insert: .... 

uncategorized SQLException for SQL [insert into ...)]; SQL state [HY000]; error code [1366];

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x94\xB4\xEB\xB0...' for column 'descr' at row 1

 

 

* 환경 

MySQL 8.0.11

Hibernate 3.3.1

Spring 3

 

 

* 해결 1 - Schema 또는 Table 또는 Column 의 인코딩을 변경

https://stackoverflow.com/questions/20411440/incorrect-string-value-xf0-x9f-x8e-xb6-xf0-x9f-mysql

https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc/10959780

 

가장 흔한 경우로, COLLATE 가 latin1 등으로 지정 되어있다면 

utf8mb4_general_ci 로 바꾸면 해결됨.

 

ALTER TABLE 테이블
CHARACTER SET = utf8 , COLLATE = utf8mb4_general_ci;

 

보통 Column 은 Table 의 설정을 따르고, 

Table 은 Schema 의 설정을 따르지만..

혹시나 모르니 각각 확인해봐야 한다.

 

- 함께 확인해야 하는 것

MySQL Connect 시 URL 에 

jdbc:mysql:///DB명?useUnicode=true&characterEncoding=UTF-8

 

- 나의 경우 오류 났던 문자는 \xF0\x9F\x94\xB4 이었다

(특수문자 동그라미... 티스토리 편집기에서 못쓰네)

이 문자는 4bytes 에 해당하여 utf8_general_ci 에선 쓸 수 없고

utf8mb4_general_ci 로 설정해야 사용할 수 있다.

 

 

* 해결 2 - 접속시 인코딩 수정

스키마, 테이블, 컬럼 모두 인코딩이 고쳐져있는데 사용 불가능 하다면 

연결시 인코딩의 문제일 수 있다.

 

SHOW VARIABLES 
WHERE Variable_name like 'c%'
;

 

조회해본 결과 인코딩이 달랐다.

이 중에서 INSERT 를 위해 다음과 같이 설정하면 바로 효과가 난다

(하지만 이건 현재 연결되어있는 MySQL workbench 에서만 확인

저걸 set 한다고 어플에서 효과가 나타나진 않음

)

 

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;

 

UPDATE ...... SET `...`='🔴' WHERE `id`='1';

 

해보면 바로 효과 확인..

 

SELECT 해보면 '?' 로 나타난다 

이건 

 

SET character_set_results = utf8mb4;

 

를 한 후 다시 SELECT 해보면 정상적으로 나타나는걸 확인할 수 있다.

 

- 하지만 SET 으로는 현재 접속에만 적용될 뿐이다.

MySQL 전체에 적용하려면 my.cnf 를 수정해야 한다.

 

https://yookeun.github.io/database/2015/07/21/mysql-utf8mb4/

MariaDB(MySQL)에서 UTF8MB4 설정

 

* 해결 3 - MySQL 설정 바꾸기 힘들다면...

자바에서 아예 걸러줌

4 bytes 문자를 찾아서 제거

https://stackoverflow.com/questions/24840667/what-is-the-regex-to-extract-all-the-emojis-from-a-string

 

제일 심플한건 아래 코드

 

public static String mysqlSafe(String input) { 
  if (input == null) return null; 
    StringBuilder sb = new StringBuilder(); 

    for (int i = 0; i < input.length(); i++) { 
      if (i < (input.length() - 1)) { // Emojis are two characters long in java, e.g. a rocket emoji is "\uD83D\uDE80"; 
        if (Character.isSurrogatePair(input.charAt(i), input.charAt(i + 1))) { 
          i += 1; //also skip the second character of the emoji 
          continue; 
        } 
      } 
      sb.append(input.charAt(i)); 
    } 

  return sb.toString(); 
}