MySQL数据库插入emoji表情问题？Incorrect string value: ‘\\xF0\\x9F\\x98\\x8D\\xF0\\x9F...‘Mysql批量由utf8转化utf8mb4

忘是亡心i 2024-04-25 20:29 4阅读 0赞

## 先说解决办法： ##

**方法一：直接修改数据库、表、字段的编码为utf8mb4（推荐使用）；**

**方法二：使用Java转码解决；**

## 背景： ##

最近在维护流控系统时，部分销售反馈无法提交工作流，以及发现评论发emoji表情 （图标网站：[链接地址][Link 1]🐮🐮🐕）直接存入mysql会报如下错误：

Cause: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\x98\\x8D\\xF0\\x9F...' for column 'explain' at

## **问题描述：** ##

Java插入表情时，由于Mysql的UTF8编码大小不支持表情的4个字节，导致异常。

问题就是Mysql中utf8字符集不支持，UTF-8编码有可能是两个、三个、四个字节，其中Emoji表情是4个字节，而Mysql的utf8编码最多3个字节，所以导致了数据插不进去。

## 方法一： ##

> #### 直接修改数据库、表、字段的编码为utf8mb4（推荐使用）； ####
> 
> **那么如何解决呢？**
> 
> 在mysql控制台输入以下命令，查看是否如下图所示:
> 
>     SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
> 
> ![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAY2hlbnRoZTE_size_10_color_FFFFFF_t_70_g_se_x_16][]
> 
>   
> 修改database字符集，数据库的编码：
> 
>     set character_set_database = utf8mb4; 
> 
> 数据库服务器的编码：
> 
>     set character_set_server=utf8mb4;
> 
> 最后设置表字符集为**utf8mb4，**以支持表情：
> 
>     -- 修改表编码
>     ALTER TABLE `表名` default character set utf8mb4 collate=utf8mb4_general_ci;
>     -- 修改表的字段编码
>     ALTER TABLE `表名` convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
> 
> 然后再次执行要插入的表情，这时候再看看你的数据库，插入表情成功，如下图：
> 
> ![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAY2hlbnRoZTE_size_9_color_FFFFFF_t_70_g_se_x_16][]
> 
> 通过MySQL数据库的修改，前端不需要做任何操作，直接会显示。

**以上的修改适用于涉及数据库表比较少的项目，而小编负责的项目400多张表，要每张表都去复制执行的话，工作量太大；而且如果只修改部分表可能会导致，数据库不同编码联合查询排序时，导致编码排序问题；太麻烦了而且容易出错，所有选择全面修改，下面小编提供两种批量处理的方案，提供大家参考：**

> **批处理一**：通过MySQL系统表，获取选择的库下所有的表，进行拼接修改语句(推荐使用)：
> 
>     -- 下面是通过拼接方式，输出所有表和字段修改字符集语句：
>     -- 输出修改表编码语句：
>     SELECT CONCAT('ALTER TABLE ', TABLE_NAME,
>      ' default character set utf8mb4 collate=utf8mb4_general_ci;')
>     FROM information_schema.`TABLES`
>     WHERE TABLE_SCHEMA = '需要查询的数据库名称';
>     
>     -- 输出修改表字段编码语句：
>     SELECT CONCAT('ALTER TABLE ', TABLE_NAME,
>      ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
>     FROM information_schema.`TABLES`
>     WHERE TABLE_SCHEMA = '需要查询的数据库名称';
> 
> 直接复制输出结果，执行即可，如下图：
> 
> ![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAY2hlbnRoZTE_size_20_color_FFFFFF_t_70_g_se_x_16][]
> 
> **批处理二**：单表处理，只处理varchar类型的字段修改编码为utf8mb4：
> 
>     -- 输出针对单个表中，varchar类型的字段进行修改：
>     SELECT
>     CONCAT('ALTER TABLE ',TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',
>     COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ',
>     CASE WHEN IS_NULLABLE='YES' THEN ' DEFAULT NULL ' ELSE ' NOT NULL ' END,
>     ' COMMENT ',"'",COLUMN_COMMENT,"';")
>     FROM information_schema.`COLUMNS`
>     WHERE TABLE_SCHEMA = '需要查询的数据库名称' 
>     	AND DATA_TYPE='varchar'
>     	AND CHARACTER_SET_NAME!='utf8mb4'
>     	AND TABLE_NAME='需要查询的表名';
> 
> 直接复制输出结果，执行即可。
> 
> **批处理三**：通过存储过程批量直接修改字符集：
> 
>     -- 先把数据库改为utf8mb4字符集
>     ALTER DATABASE [数据库名] CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
>     
>     -- 执行以下的脚本创建存储过程，然后再运行即可在已经将库中已经存在的表改为utf8mb4编码
>     DROP PROCEDURE IF EXISTS UP_CHANGE_UTF8MB4;
>     DELIMITER $$
>     CREATE PROCEDURE UP_CHANGE_UTF8MB4()
>     COMMENT '将当前数据库中所有表的字符集转换成utf8mb4'
>     BEGIN
>     DECLARE $i INT;
>     DECLARE $cnt INT;
>     DECLARE $NAME VARCHAR(64);
>     -- #创建临时表，代替游标
>     DROP TABLE IF EXISTS tmp_Table_name;
>     CREATE TEMPORARY TABLE tmp_Table_name (
>     id INT NOT NULL AUTO_INCREMENT,
>     table_name VARCHAR(64) NOT NULL,
>     PRIMARY KEY (`id`)
>     );
>     -- 插入要处理的表名到临时表中
>     INSERT INTO tmp_Table_name (table_name)
>     SELECT
>     table_name
>     FROM information_schema.`TABLES`
>     WHERE TABLE_TYPE = 'BASE TABLE'
>     AND TABLE_SCHEMA = DATABASE();
>     -- 循环处理每一张表，改表的字符集
>     SET $i = 1;
>     SELECT
>     COUNT(1) INTO $cnt
>     FROM tmp_Table_name;
>     WHILE $i <= $cnt DO
>     SELECT
>     table_name INTO $NAME
>     FROM tmp_Table_name
>     WHERE id = $i;
>     	SET @asql = CONCAT('ALTER TABLE ', $NAME, ' CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; ');
>     PREPARE asql FROM @asql;
>     EXECUTE asql;
>     	SET @asql = CONCAT('ALTER TABLE ', $NAME, ' CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; ');
>     PREPARE asql FROM @asql;
>     SELECT @asql;
>     EXECUTE asql;
>     SET $i = $i + 1;
>     END WHILE;
>     DEALLOCATE PREPARE asql;
>     DROP TABLE tmp_Table_name;
>     END$$
>     DELIMITER ;
>     -- 
>     -- 执行存储过程
>     CALL  `UP_CHANGE_UTF8MB4`();
>     -- 删除存储过程
>     DROP PROCEDURE  IF EXISTS UP_CHANGE_UTF8MB4;
> 
> 推荐使用批处理一的方案，存储过程的方案仅提供查看学习。

## 方法二： ##

> #### 使用java转码解决 ####
> 
>     <dependency>
>          <groupId>com.vdurmont</groupId>
>          <artifactId>emoji-java</artifactId>
>         <version>3.2.0</version>
>     </dependency>
> 
>     @RequestMapping("/add/content")
>         public ResponseEntity insertContent(@RequestBody Content content) {
>             String title = content.getTitle(); 
>             String titles = EmojiParser.parseToAliases(title);
>             content.setTitle(titles);
>             Integer integer = emojiMapper.insertContent(content);
>             if (integer == 1) {
>                return ResponseEntity.ok().build();
>             }
>             return  ResponseEntity.badRequest().build(); 
>         }
>      
>         @RequestMapping("/get/{id}")
>         public Content getById(@PathVariable("id") Integer id) { 
>             Content content = emojiMapper.selectById(id);
>             String title = EmojiParser.parseToUnicode(content.getTitle());
>             content.setTitle(title);
>             if (content != null) {
>                 return content;
>             }
>             return  null;
>         }
> 
> 如果影响的字段比较少，可以选择使用该处理方法。

最近处理生产环境突发问题时，总结的处理经验；有任何问题，欢迎大家交流学习和指正~

[Link 1]: https://www.emojiall.com/zh-hans/sub-categories/D1
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAY2hlbnRoZTE_size_10_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/2e16c44612c24738a5bbd23940e218ec.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAY2hlbnRoZTE_size_9_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/b88dcb8a81c540c1bd32b2e7ff554200.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAY2hlbnRoZTE_size_20_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/9b3aab33f3a0419e9222d96748a7dcc5.png