MySQL插入数据 “Incorrect string value“ 错误解析

最近在做项目的向一张评论表中插入数据的时候遇到了这个错误。几经辗转找到了报错的原因。

Incorrect string value 原因定位

我找到的原因是由于插入数据的字符集与表结构的默认字符集不匹配导致的这个问题。

举个例子，比如我们插入了一条数据，其中某个字段值包含了一个字符，这个字符是以四个字节进行编码的如"🔤"这样的。但是我们表结构的默认字符集最大允许三个字节作为一个字符，此时就会出现这个错误。

如下面我有这样一张表：

CREATE TABLE tb_description(
	id int(11) AUTO_INCREMENT COMMENT '主键',
	description varchar(1024) COMMENT '评论',
	create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	PRIMARY KEY(id)
)engine=InnoDB,charset=utf8;

这里我对应表的默认字符集为UTF8，此时如果我要插入一条这样的数据的话，

INSERT INTO tb_description(description)
VALUES("学到了😁");

就会复现上述的错误。

问题解决

异常原因找到了,那么解决方式也很简单。只需要修改字符集为合适的字符集即可。这里我们需要将建表时设置的字符集 utf8修改为支持四字节字符集utf8bm4即可。

一种方式是删表重建：

DROP TABLE IF EXISTS tb_description;
CREATE TABLE tb_description(
	id int(11) AUTO_INCREMENT COMMENT '主键',
	description varchar(1024) COMMENT '评论',
	create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	PRIMARY KEY(id)
)engine=InnoDB,charset=utf8mb4;
-- 这里在将原来的 charset=utf8 修改为了 cahrset=utf8mb4

这种方式只适用于开发时，或者刚刚建表且生产环境没有生产数据的情况。

如果遇到需要修改生产环境下的表结构时则需要用下面的语句:

ALTER TABLE tb_description DEFAULT CHARACTER SET utf8mb4;

原因分析

正常情况 utf-8 是一种可变长度的字符集，它的单个字符的长度范围可以在1~4字节之间变化。由于其支持字符范围广并且传输效率高而被广泛使用。虽然在 Windows 中我们默认用的还是 gbk字符集 ，但是在使用浏览器浏览网站的时候大部分网站其实已经在广泛使用 utf-8字符集了。

而在 MySQL 当中由于一些历史原因，其直接支持的 utf-8 格式并非完全版的 utf-8。简单来说MySQL中的utf-8字符集并不完全支持utf-8具体表现就是只能保存最长字节为三字节的字符，而对于四字节编码的字符时，就会无法保存，并且抛出 Incorrect string value异常来声明"你输入了我无法识别的字符"。

为了解决无法保存四字节编码的utf-8字符的问题，MySQL在5.3.3版本时推出了 utf8mb4字符集。因此我们在使用时如果存在目标字段值中有emoji或者生僻字的情况时，最好使用 utf8mb4 字符集建表。

参考资料

总结：被MySQL UTF8编码坑的惨痛教训…
mysql不能识别4个字节的utf8编码的字符
mysql修改默认的环境的字符集为utf-8
MySQL字符集 GBK、GB2312、UTF8区别 解决MYSQL中文乱码问题
How to support full Unicode in MySQL databases