### Cause: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x96\x8B \xE7...‘ for column ‘con

Bertha 。 2023-02-25 15:26 19阅读 0赞

**目录**

前言：

插入数据库异常报错信息如下，xshell服务器后台抓取的异常信息

插入失败原因，原因定位说明

数据库配置操作命令，解决方案一

java层面的最简洁的解决方案二【推荐】

网上一些其他的方案：

--------------------

# 前言： #

主要是工作中，处理内容资讯，里面居然出现了表情包。导致Mysql在插入insert实体类的时候，异常抛出来了。出现了下面的异常信息。问题原因是，表情包，是四个字节，插入数据库如果没有设置的话，会异常报错。

错误信息：

org.springframework.jdbc.UncategorizedSQLException:   
\#\#\# Error updating database.  **Cause: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x96\\x8B \\xE7...' for column 'content' 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, title\_img\_link, sub\_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,     level,  news\_type, content\_text )  VALUES  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,   ?, ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?,     ?,  ?, ? )  
\#\#\# **Cause: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x96\\x8B \\xE7...' for column 'content' at row 1  
; uncategorized SQLException; SQL state \[HY000\]; error code \[1366\]; Incorrect string value: '\\xF0\\x9F\\x96\\x8B \\xE7...' for column 'content' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x96\\x8B \\xE7...' for column 'content' at row 1**  
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~\[spring-jdbc-5.0.8.RELEASE.jar!/:5.0.8.RELEASE\]  
    at

# 插入数据库异常报错信息如下，xshell服务器后台抓取的异常信息 #

**Incorrect string value: '\\xF0\\x9F\\x96\\x8B \\xE7...' for column 'content' at row 1**

org.springframework.jdbc.UncategorizedSQLException: 
    ### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x96\x8B \xE7...' for column 'content' 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, title_img_link, sub_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,     level,  news_type, content_text )  VALUES  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,   ?, ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?,     ?,  ?, ? )
    ### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x96\x8B \xE7...' for column 'content' at row 1
    ; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x96\x8B \xE7...' for column 'content' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x96\x8B \xE7...' for column 'content' at row 1
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    	at com.sun.proxy.$Proxy127.insert(Unknown Source) ~[?:?]
    	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    	at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:68) ~[mybatis-plus-core-3.0.3.jar!/:?]
    	at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64) ~[mybatis-plus-core-3.0.3.jar!/:?]
    	at com.sun.proxy.$Proxy174.insert(Unknown Source) ~[?:?]
    	at com.gildata.threebody.tbm.dao.impl.TbmNewsInfoDaoImpl.insert(TbmNewsInfoDaoImpl.java:43) ~[tbm-dao-1.0.0-SNAPSHOT.jar!/:?]
    	at com.gildata.threebody.tbm.dao.impl.TbmNewsInfoDaoImpl$$FastClassBySpringCGLIB$$b0681880.invoke(<generated>) ~[tbm-dao-1.0.0-SNAPSHOT.jar!/:?]
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.7.RELEASE.jar!/:5.0.7.RELEASE]
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar!/:5.0.7.RELEASE]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
    	at com.gildata.threebody.tbm.dao.impl.TbmNewsInfoDaoImpl$$EnhancerBySpringCGLIB$$9ca26153.insert(<generated>) ~[tbm-dao-1.0.0-SNAPSHOT.jar!/:?]
    	at com.gildata.threebody.tbm.provider.impl.service.NewsConsumerServiceImpl.builderNewsInfo(NewsConsumerServiceImpl.java:276) ~[tbm-provider-1.0.0-SNAPSHOT.jar!/:?]
    	at com.gildata.threebody.tbm.provider.impl.service.NewsConsumerServiceImpl.accept(NewsConsumerServiceImpl.java:197) ~[tbm-provider-1.0.0-SNAPSHOT.jar!/:?]
    	at com.gildata.threebody.tbm.provider.impl.service.DefaultNewsConsumer.accept(DefaultNewsConsumer.java:25) ~[tbm-provider-1.0.0-SNAPSHOT.jar!/:?]
    	at com.gildata.threebody.tbm.provider.kafka.listener.KafkaListeners.lambda$batchListener$0(KafkaListeners.java:37) ~[tbm-provider-1.0.0-SNAPSHOT.jar!/:?]
    	at java.util.ArrayList.forEach(ArrayList.java:1257) [?:1.8.0_191]
    	at com.gildata.threebody.tbm.provider.kafka.listener.KafkaListeners.batchListener(KafkaListeners.java:33) [tbm-provider-1.0.0-SNAPSHOT.jar!/:?]
    	at sun.reflect.GeneratedMethodAccessor1149.invoke(Unknown Source) ~[?:?]
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191]
    	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191]
    	at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:181) [spring-messaging-5.0.7.RELEASE.jar!/:5.0.7.RELEASE]
    	at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:114) [spring-messaging-5.0.7.RELEASE.jar!/:5.0.7.RELEASE]
    	at org.springframework.kafka.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:48) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:248) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.onMessage(BatchMessagingMessageListenerAdapter.java:128) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.onMessage(BatchMessagingMessageListenerAdapter.java:59) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeBatchListener(KafkaMessageListenerContainer.java:927) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeBatchListener(KafkaMessageListenerContainer.java:882) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeListener(KafkaMessageListenerContainer.java:864) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.run(KafkaMessageListenerContainer.java:725) [spring-kafka-2.1.10.RELEASE.jar!/:2.1.10.RELEASE]
    	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_191]
    	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_191]
    	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]
    Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x96\x8B \xE7...' for column 'content' at row 1
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.46.jar!/:5.1.46]

# 插入失败原因，原因定位说明 #

主要是数据库的格式不支持表情的插入，如果mysql>5.5是支持的，四个字符意味着这是表情符号图片，一般的需要设置表和数据库的格式，统一为utf8mb4类型

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70][]

需要手动的修改数据库与数据表字段的格式，这样才可以，

### 数据库配置操作命令，解决方案一 ###

show variables like 'character\_set\_database';

[client]  
    default-character-set=utf8mb4  
      
    [mysqld]  
    character-set-server = utf8mb4  
    collation-server = utf8mb4_unicode_ci  
    init_connect='SET NAMES utf8mb4'  
    skip-character-set-client-handshake = true  
      
    [mysql]  
    default-character-set = utf8mb4

针对数据库之后，针对数据库表的单个字段进行修改字符段格式编码：

-- 修改一张表的所有字段的编码格式
    alter table `tbm_news_info` convert to character set utf8mb4; 
    -- 修改表的编码格式
    ALTER TABLE `tbm_news_info` DEFAULT CHARACTER SET utf8mb4; 
    -- 修改字段的编码格式
    ALTER TABLE `tbm_news_info` CHANGE `content`  longtext  CHARACTER SET utf8mb4 NOT NULL;

# java层面的最简洁的解决方案二【推荐】 #

主要是数据库里面有千万量级的数据，改动数据库属性不建议，还是改动java代码，剔除内容里面的四字节代码，剔除表情包的字符串，然后入库，需要处理下：

另外一种方案就是需要使用java替换掉表情符号，这样就可以入库了。会损失些文字效果。

目前我也是采用下面的方案，

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;

# 网上一些其他的方案： #

我自己测试并不能完全解决。所以没有采用，记录下来分享一下。

/**
         * 判断是否有emoji表情
         * @param source
         * @return
         */
        public static boolean containsEmoji(String source) {
            int len = source.length();
            boolean isEmoji = false;
            for (int i = 0; i < len; i++) {
                char hs = source.charAt(i);
                if (0xd800 <= hs && hs <= 0xdbff) {
                    if (source.length() > 1) {
                        char ls = source.charAt(i + 1);
                        int uc = ((hs - 0xd800) * 0x400) + (ls - 0xdc00) + 0x10000;
                        if (0x1d000 <= uc && uc <= 0x1f77f) {
                            return true;
                        }
                    }
                } else {
                    // non surrogate
                    if (0x2100 <= hs && hs <= 0x27ff && hs != 0x263b) {
                        return true;
                    } else if (0x2B05 <= hs && hs <= 0x2b07) {
                        return true;
                    } else if (0x2934 <= hs && hs <= 0x2935) {
                        return true;
                    } else if (0x3297 <= hs && hs <= 0x3299) {
                        return true;
                    } else if (hs == 0xa9 || hs == 0xae || hs == 0x303d || hs == 0x3030 || hs == 0x2b55 || hs == 0x2b1c || hs == 0x2b1b || hs == 0x2b50 || hs == 0x231a) {
                        return true;
                    }
                    if (!isEmoji && source.length() > 1 && i < source.length() - 1) {
                        char ls = source.charAt(i + 1);
                        if (ls == 0x20e3) {
                            return true;
                        }
                    }
                }
            }
            return isEmoji;
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70]: /images/20230209/891fb0dcc542414c962085cc6c8d7dc1.png

### Cause: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x96\x8B \xE7...‘ for column ‘con

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Incorrect string value: ‘\xF0\x9F\x98\xAD“,...‘ for column ‘commentContent‘ at row 1

相关 ### Cause: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x96\x8B \xE7...‘ for column ‘con

相关 Mysql：Incorrect string value: ‘\xF0\x9F\x8D\x83\xF0\x9F...‘ for column 问题解决方案

相关解决mysql保存数据SQLException: Incorrect string value: '\xF0\x9F\x91\x87\xE5\x91...' for column '错误

相关 java.sql.SQLException: Incorrect string value: '\xE6\x88\x91\xE7\x9A\x84...' for column 'title'

相关 Incorrect string value: '\xF0\x9F\x98\x82' for column '' at row 1

相关 java.sql.SQLException: Incorrect string value: '\xF0\x9F\x87\xA8\xF0\x9F...'

相关 MysqlDataTruncation: Data truncation: Incorrect string value: '\xF0\x9D\x90\xB6"#...' for column

相关 [2019-04-19 19:38:07] [HY000][1366] Incorrect string value: '\xE7\x8E\x8B\xE8\x83\x9C...' for column

相关 Incorrect string value: '\xE7\xA8\x8B\xE5\xBA\x8F...' for column 'course' at row 1