mysql系列之-SQLException: Incorrect string value: ‘\xF0\x9F\x98\xBD‘ for column ‘nick_name‘

1.背景

今天获取微信头像路径，并保存到Mysql数据时抛如下异常：

rg.springframework.jdbc.UncategorizedSQLException: 

### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xBD' for column 'nick_name' at row 1

### The error may exist in class path resource [mapper/system/SysUserMapper.xml]

### The error may involve com.whyt.system.mapper.SysUserMapper.insertUser-Inline

### The error occurred while setting parameters

### SQL: insert into sys_user(                 dept_id,       user_name,            nick_name,      user_type,       email,       avatar,       phonenumber,       sex,       password,       status,                       create_time    )values(                  ?,       ?,            ?,      ?,       ?,       ?,       ?,       ?,       ?,       ?,                       sysdate()    )

### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xBD' for column 'nick_name' at row 1

; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\xBD' for column 'nick_name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xBD' for column 'nick_name' at row 1

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) ~[mybatis-spring-2.0.4.jar!/:2.0.4]

	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.4.jar!/:2.0.4]

	at com.sun.proxy.$Proxy152.insert(Unknown Source) ~[na:na]

	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271) ~[mybatis-spring-2.0.4.jar!/:2.0.4]

	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.4.jar!/:3.5.4]

	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144) ~[mybatis-3.5.4.jar!/:3.5.4]

这个异常，个人还是很纳闷的，因为不是所有人的头像信息都存储不了，只是个别人的头像信息存储有问题，这个提示数据字段包含特殊字符，然后询问，然后发现这个用户的用户名是个图片。正常汉字不会超过三字节。特殊符号时会出现四字节（手机表情等）。定位到了原因那我们就有针对的办法。

2.解决办法

修改表的字符集由utf8 调整为 utf8mb4

2.1.查看字符集

show full columns from table;

 

2.1.修改字符集

alter table xxxtable convert to character set utf8mb4;

3.验证数据

可以看到数据可以正常存储展示了。