[MySQL] mysql에러 ERROR 1366 (HY000) : incorrect string value

오류상황

 

1.mysql insert query 실행시 오류

mysql> ERROR 1366 (HY000) : incorrect string value : "\xED\x95\x9C\xEC\x9A\xB0...' for column 'itemName' at row 1

 

2. application 내에서 insert query 실행시 오류

org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xED\x95\x9C\x9A\xBO...'for column 'itemName' at row 1
### The error may involve com.jworks.grevu.mapper.ItemMapper.addItem-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO item (itemName, itemPrice, itemDesc, shopid) VALUES (?,?,?,?)
### Cause: java.sql.SQLException: Incorrect string value: '\xED\x95\x9C\x9A\xBO...'for column 'itemName' at row 1; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xED\x95\x9C\x9A\xBO...'for column 'itemName' at row 1; nested exception is java.sql SQLException: Incorrect string value: '\xED\x95\x9C\x9A\xBO...'for column 'itemName' at row 1

 

 

수정방법

 

1.my.cnf 파일 설정 변경

$ sudo vi /usr/local/mysql/my.cnf
[mysqld]
...
default-character-set=utf8
default-collation=uft8_general_ci
...

 

2.database, table character set 설정

mysql> ALTER TABLE table_name convert to charset utf8;

 

3.완료 후 mysql 재시작

$mysql restart