Mysql入库不了表情符号怎么办

目录

前言

异常信息：

主要原因是

 解决办法

---

前言

异常信息：

### The error occurred while setting parameters ### SQL: INSERT INTO news_info ( id,  title, content, author, summary, 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 'author' at row 1 ;

uncategorized SQLException; SQL state [HY000];

error code [1366];

Incorrect string value: '\xF0\x9F\x91\x89' for column 'author' at row 1;

nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\x89' for column 'author' at row 1 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)

主要原因是

author  含有表情符号，表情是4字符，如果Mysql建库建表的时候没有声明是4字节，那么是在insert的时候会报错的!

 解决办法

需要在消费入库的时候进行author转换成正常的二进制，二字符的汉字，也就是剔除四字节的表情；

String  author=  ContentUtils.filterOffUtf8Mb(newsInfo.getAuthor());
newsInfo.setAuthor(author);

主要得工具类代码

 /**
     * 主要功能是做表情包剔除工具类
     * @param text
     * @return
     */
    public static String filterOffUtf8Mb(String text) {
        if (StringUtils.isBlank(text)) {
            return text;
        }
        String result = text;
        try {
            byte[] bytes = text.getBytes(UTF_CHARACTOR);
            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_CHARACTOR);
        } catch (Exception ex) {
            log.error("内容过滤4字节字符出现错误" + ex.getMessage());
        }
        return result;
    }