nested exception is java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x91\x89: ...‘ 解决办法，表情格式

ゝ一纸荒年。 2022-12-29 06:54 144阅读 0赞

# 报错信息： #

org.springframework.jdbc.UncategorizedSQLException: \#\#\# Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x91\\x89: ...' for column 'summary' at row 1 \#\#\# The error may involve com.dao.mapper.middleground.TbmNewsInfoMapper.insert-Inline \#\#\# The error occurred while setting parameters \#\#\# SQL: INSERT INTO tbm\_news\_info ( id, parent\_news\_id, dup\_news\_id, rid, app\_ids, title, content, author, summary, src\_publish\_date, news\_url, ds\_source\_type, ds\_source\_name, copyright\_status, media\_source, ds\_code, ds\_news\_columns, layout\_number, layout\_info, sensitives, scraping\_time, level, news\_type, content\_text, original\_columns, content\_words ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) \#\#\# Cause: java.sql.SQLException: **Incorrect string value: '\\xF0\\x9F\\x91\\x89: ...' for column 'summary' at row 1 ; uncategorized SQLException; SQL state \[HY000\]; error code \[1366\]; Incorrect string value: '\\xF0\\x9F\\x91\\x89: ...' for column 'summary' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x91\\x89: ...'** for column 'summary' at row 1 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) at com.sun.proxy.$Proxy118.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:68) at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64) at com.sun.proxy.$Proxy181.insert(Unknown Source) at com.gildata.threebody.tbm.dao.impl.TbmNewsInfoDaoImpl.insert(TbmNewsInfoDaoImpl.java:43) at com.gildata.threebody.tbm.dao.impl.TbmNewsInfoDaoImpl$$FastClassBySpringCGLIB$$b0681880.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.gildata.threebody.tbm.dao.impl.TbmNewsInfoDaoImpl$$EnhancerBySpringCGLIB$$c5e0c365.insert(<generated>) at com.gildata.threebody.tbm.provider.impl.service.NewsConsumerServiceImpl.builderNewsInfo(NewsConsumerServiceImpl.java:398) at com.gildata.threebody.tbm.provider.impl.service.NewsConsumerServiceImpl.accept(NewsConsumerServiceImpl.java:260) at com.gildata.threebody.tbm.provider.impl.service.DefaultNewsConsumer.accept(DefaultNewsConsumer.java:25) at com.gildata.threebody.tbm.provider.kafka.listener.KafkaListeners.lambda$batchListener$0(KafkaListeners.java:37) at java.util.ArrayList.forEach(ArrayList.java:1257) at com.gildata.threebody.tbm.provider.kafka.listener.KafkaListeners.batchListener(KafkaListeners.java:33) at sun.reflect.GeneratedMethodAccessor905.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:181) at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:114) at org.springframework.kafka.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:48) at org.springframework.kafka.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:248) at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.onMessage(BatchMessagingMessageListenerAdapter.java:128) at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.onMessage(BatchMessagingMessageListenerAdapter.java:59) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeBatchListener(KafkaMessageListenerContainer.java:927) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeBatchListener(KafkaMessageListenerContainer.java:882) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeListener(KafkaMessageListenerContainer.java:864) at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.run(KafkaMessageListenerContainer.java:725) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x91\\x89: ...' for column 'summary' at row 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement\_execute(FilterChainImpl.java:3409) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement\_execute(FilterEventAdapter.java:440) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement\_execute(FilterChainImpl.java:3407) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at sun.reflect.GeneratedMethodAccessor639.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy316.update(Unknown Source) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) at sun.reflect.GeneratedMethodAccessor688.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)

# 解决办法： #

先通过去除表情符号的字符，然后再进行入库操作：

tbmNewsInfo.setSummary( filterOffUtf8Mb(newsInfo.getSummary()));

filterOffUtf8Mb处理表情格式的方法如下：

public static String filterOffUtf8Mb(String text) {
            String result = text;
            try {
                byte[] bytes = text.getBytes("utf-8");
                ByteBuffer buffer = ByteBuffer.allocate(bytes.length);
                int i = 0;
                while (i < bytes.length) {
                    short b = bytes[i];
                    if (b > 0) {
                        buffer.put(bytes[i++]);
                        continue;
                    }
                    b += 256; // 去掉符号位
                    if (((b >> 5) ^ 0x06) == 0) {
                        buffer.put(bytes, i, 2);
                        i += 2;
                    } else if (((b >> 4) ^ 0x0E) == 0) {
                        buffer.put(bytes, i, 3);
                        i += 3;
                    } else if (((b >> 3) ^ 0x1E) == 0) {
                        i += 4;
                    } else if (((b >> 2) ^ 0xBE) == 0) {
                        i += 5;
                    } else {
                        i += 6;
                    }
                }
                buffer.flip();
                result = new String(buffer.array(), "utf-8");
            } catch (Exception ex) {
                log.error("资讯内容过滤4字节字符出现错误" + ex.getMessage());
            }
            return result;
        }

# 原因定位： #

主要是摘要这个字段，也是字符格式入库，需要把表情符号删除替换掉然后才能正常入库，然后数据库格式改成了mb4发现也不能支持。字符串修改之后，目前已经正常入库！