Mysql Incorrect string value问题解决(Emoji表情保存)

分类: 数据库 0人评论 selfly 8月前发布

问题

今天在把内容保存到数据库的时候,后台报出:

 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect string value: '\xF0\x9F\x8E\x89' for column 'title' at row 1

导致数据无法保存。

网上查询资料后发现,原来是内容中有Emoji表情,该表情有4个字节而mysql的utf8字符集最大只支持3个字节,因而出错。

解决方案

知道了错误原因自然也就有了相应的解决方案。

在mysql中有一个utf8字符集的超集utf8mb4,该字符集允许存储的字符最多为4个字节,那么它就可以满足对Emoji表情的存储。所以我们只需要将对应的字符集改为utf8mb4即可。

先查看一下原先使用的字符集,是utf8:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_general_ci |
| collation_server         | utf8_general_ci |
+--------------------------+-----------------+

修改my.cnf配置

找到my.cnf文件,增加或修改下面两行,我这里是mariadb,原生mysql可能稍有不同,修改后别忘了重启:

character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

修改库表字符集

修改了上面的配置并重启后,对于新建的库表等应该就有效果了。但是对于原来已经存在的却并不起作用,需要单独显式的进行修改。

修改整库的字符集

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

修改表的字符集

ALTER TABLE haviea.share_article_comment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改列字符集

在经过上面两步之后,本来以为会一切正常,没想到后台又报出如下错误:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

看来是有字符集不对应,但是明明已经全改了啊,再次查看字符集:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

可以看到已经都改过来了,再查看create table的sql:

SHOW CREATE TABLE ktx_content;
CREATE TABLE `ktx_content` (
  `content_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '内容id',
  ......
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COMMENT='内容'

也已经变更过来了,那为什么还是不行呢?

一筹莫展之际,突然想到既然修改全局字符集不影响库,修改库不影响表,那修改了库表的字符集之后是不是影响不到列呢?

一查,还真是,再修改需要保存该内容列的字符集:

修改单个字段的字符集

ALTER TABLE table_name CHANGE src_column_name target_column_name VARCHAR(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

再次运行代码,终于一切正常,在内容中可以有流行的Emoji表情了!