问题
今天在把内容保存到数据库的时候,后台报出:
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表情了!