mysql系列之SQLException:Incorrectstringvalue:‘\xF0\x9F\x98\xBD‘forcolumn‘nick_name‘

1.背景

今天获取微信头像路径&＃xff0c;并保存到Mysql数据时抛如下异常&＃xff1a;

rg.springframework.jdbc.UncategorizedSQLException: ### Error updating database. Cause: java.sql.SQLException: Incorrect string value: &＃39;\xF0\x9F\x98\xBD&＃39; for column &＃39;nick_name&＃39; 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: &＃39;\xF0\x9F\x98\xBD&＃39; for column &＃39;nick_name&＃39; at row 1; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: &＃39;\xF0\x9F\x98\xBD&＃39; for column &＃39;nick_name&＃39; at row 1; nested exception is java.sql.SQLException: Incorrect string value: &＃39;\xF0\x9F\x98\xBD&＃39; for column &＃39;nick_name&＃39; at row 1at 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]

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

2.解决办法

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

2.1.查看字符集

show full columns from table;

2.1.修改字符集

alter table xxxtable convert to character set utf8mb4;

3.验证数据

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