mysql数据库插入表情包emoji表情，出错解决方案

最新推荐文章于 2026-06-25 16:18:03 发布

原创最新推荐文章于 2026-06-25 16:18:03 发布 · 1.9k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

mysql

当尝试插入含有特殊符号的昵称时，出现SQL错误：Incorrect string value。原因是MySQL的utf8编码不支持四字节的Unicode字符。解决方案包括修改数据库配置为utf8mb4，如设置default-character-set=utf8mb4，并调整表和列的字符集为utf8mb4_unicode_ci。执行ALTER DATABASE和ALTER TABLE命令更新数据库和表的字符集，最后修改列定义以允许存储特殊符号。

0、插入含有特殊符号的昵称报错

### SQL: INSERT INTO user ( uuid_tiktok, nickname, person_img_url, create_time, update_time ) VALUES ( ?, ?, ?, ?, ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xB0\xF0\x9F...' for column 'nickname' at row 1
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
   at sun.reflect.GeneratedMethodAccessor222.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
   at com.sun.proxy.$Proxy72.insert(Unknown Source)
   at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
   at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:60)
   at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
   at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
   at com.sun.proxy.$Proxy81.insert(Unknown Source)
   at sun.reflect.GeneratedMethodAccessor221.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
   at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
   at com.sun.proxy.$Proxy82.insert(Unknown Source)
   at com.wallpaper.service.impl.UserServiceImpl.saveLoginInfo(UserServiceImpl.java:165)
   at com.wallpaper.service.impl.UserServiceImpl.login(UserServiceImpl.java:89)
   at com.wallpaper.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$facc40ce.invoke(<generated>)
   at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
   at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
   at com.wallpaper.service.impl.UserServiceImpl$$EnhancerBySpringCGLIB$$3984a99f.login(<generated>)
   at com.wallpaper.controller.UserController.login(UserController.java:274)
   at com.wallpaper.controller.UserController$$FastClassBySpringCGLIB$$839fd3a0.invoke(<generated>)
   at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
   at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
   at com.wallpaper.controller.UserController$$EnhancerBySpringCGLIB$$3960b555.login(<generated>)
   at sun.reflect.GeneratedMethodAccessor302.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)
   at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)
   at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)
   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
   at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)
   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)
   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
   at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
   at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
   at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
   at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
   at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
   at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
   at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
   at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:141)
   at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:82)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
   at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
   at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
   at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
   at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
   at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
   at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707)
   at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
   at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
   at java.lang.Thread.run(Thread.java:748)
1、查询数据库的编码格式为utf8

2、utf8mb4和utf8的编码区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

UTF-8是使用1~4个字节，一种变长的编码格式，字符编码。mb4即 most bytes 4，使用4个字节来表示完整的UTF-8。

mysql的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

总结：MySQL的utf8是utfmb3，只有三个字节，节省空间但不能表达全部的UTF-8。所以推荐使用utf8mb4。

utf8mb4_bin：将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

utf8mb4_general_ci：ci即case insensitive，不区分大小写。没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

utf8mb4_unicode_ci：是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

utf8mb4_general_ci是一个遗留的校对规则，不支持扩展，它仅能够在字符之间进行逐个比较。utf8_general_ci校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci的校对规则相比，比较正确性较差。

总结：general_ci 更快，unicode_ci 更准确。但相比现在的CPU来说，它远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。使用者更应该关心字符集与排序规则在db里需要统一。（可能产生乱码的字段不要作为主键或唯一索引。例如：以 url 来作为唯一索引，但是它记录的有可能是乱码。）

3、两种修改处理方案

3.1、数据库的配置

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

[client]
default-character-set=utf8mb4
3.2、命令行修改数据库

ALTER DATABASE t_yown_userdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE t_yown_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

alter table t_yown_user modify `NICKNAME` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '昵称';
4、修改之后数据库的显示结果，成功插入特殊符号