1. 问题
1.1 发现问题
最近生产环境日志中报了一个异常：
12021-07-11 20:54:41.632 ERROR 26289 --- [XNIO-1 task-11] c.e.t.s.t.mp.WxMpMessageRouterService :
2### Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1
3### The error may exist in com/eveus/tap/account/mapper/AccountMapper.java (best guess)
4### The error may involve com.eveus.tap.account.mapper.AccountMapper.insert-Inline
5### The error occurred while setting parameters
6### SQL: INSERT INTO tap_account ( open_id, avatar, nick_name, country, province, city, gender, secret, status, IS_DISABLED, is_subscribe, create_time, update_time ) VALUES
7 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
8### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1
9; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1; nested exception is java.sql.SQLException:
10 Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1
11
12org.springframework.jdbc.UncategorizedSQLException:
13### Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1
14### The error may exist in com/eveus/tap/account/mapper/AccountMapper.java (best guess)
15### The error may involve com.eveus.tap.account.mapper.AccountMapper.insert-Inline
16### The error occurred while setting parameters
17### SQL: INSERT INTO tap_account ( open_id, avatar, nick_name, country, province, city, gender, secret, status, IS_DISABLED, is_subscribe, create_time, update_time ) VALUES
18 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
19### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1
20; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1; nested exception is java.sql.SQLException:
21 Incorrect string value: '\xF0\x9F\xA6\x84' for column 'NICK_NAME' at row 1
22 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
23 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
24 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
25 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
26 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
27 at com.sun.proxy.$Proxy134.insert(Unknown Source)
28 at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
29 at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:60)
30 at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
31 at com.sun.proxy.$Proxy186.insert(Unknown Source)
32 at com.baomidou.mybatisplus.extension.service.IService.save(IService.java:59)
33 at com.eveus.tap.account.service.impl.AccountServiceImpl.addAccount(AccountServiceImpl.java:151)
34 at com.eveus.tap.account.service.impl.AccountServiceImpl.ensureLoad(AccountServiceImpl.java:226)
35 at com.eveus.tap.account.service.impl.AccountServiceImpl$$FastClassBySpringCGLIB$$34fe95c8.invoke(<generated>)
36 at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
37 at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)
38 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
39 at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
40 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
41 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
42 at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
43 at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
44 at com.eveus.tap.account.service.impl.AccountServiceImpl$$EnhancerBySpringCGLIB$$cbb1e26b.ensureLoad(<generated>)
45 at com.eveus.tap.server.taxer.mp.handler.SubscribeHandler.handle(SubscribeHandler.java:54)
46 at me.chanjar.weixin.mp.api.WxMpMessageRouterRule.service(WxMpMessageRouterRule.java:226)
47 at me.chanjar.weixin.mp.api.WxMpMessageRouter.route(WxMpMessageRouter.java:203)
48 at me.chanjar.weixin.mp.api.WxMpMessageRouter.route(WxMpMessageRouter.java:154)
49 at me.chanjar.weixin.mp.api.WxMpMessageRouter.route(WxMpMessageRouter.java:233)
看错误信息是因为 MySQL 字符编码不正确导致无法保存特殊字符导致的。把字符还原一下，发现 \xF0\x9F\xA6\x84 代表的字符内容是：🦄，是一个 Emoji 字符。
当使用对 utf8 编码的时候，和一般汉字占用3个字节不同，Emoji 字符的编码会比较特殊一点，占用4个字节。而由于历史的原因，MySQL 使用的 utf8 编码最长支持3个字节，如果要保存4个字节的 unicode，则需要使用 utf8mb4 编码。上述错误应该是因此而出。