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

先说解决办法：

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

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

背景：

最近在维护流控系统时，部分销售反馈无法提交工作流，以及发现评论发emoji表情?（图标网站：链接地址🐮🐮🐕）直接存入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%';

修改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;

然后再次执行要插入的表情，这时候再看看你的数据库，插入表情成功，如下图：

通过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 = '需要查询的数据库名称';

直接复制输出结果，执行即可，如下图：

批处理二：单表处理，只处理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;
    }

?如果影响的字段比较少，可以选择使用该处理方法。

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