今天在插入 MySQL 时遇到如下错误

Incorrect string value: ‘\xF0\x9F\x98\x81…’ for column ‘data’ at row 1

查证之后发现是因为插入的时候字符串中有 emoji,而 emoji 是 unicode 编码,MySQL 当时在建表时选择了 utf8 编码,导致了上述错误。mysql 支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了,因此引出了 utf8mb4 编码。MySQL 在 5.5.3 之后增加了这个 utf8mb4 的编码,mb4 就是 most bytes 4 的意思,专门用来兼容四字节的 unicode。好在 utf8mb4 是 utf8 的超集,除了将编码改为 utf8mb4 外不需要做其他转换。当然,为了节省空间,一般情况下使用 utf8 也就够了。

为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8,对于一般性要求建议普通表使用 utf8, 如果这个表需要支持 emoji 就使用 utf8mb4。

深入 Mysql 字符集设置

字符 (Character) 是指人类语言中最小的表义符号。例如’A’、’B’等;给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就是字符的编码 (Encoding)。例如,我们给字符’A’赋予数值 0,给字符’B’赋予数值 1,则 0 就是字符’A’的编码;给定一系列字符并赋予对应的编码后,所有这些字符和编码对组成的集合就是字符集 (Character Set)。例如,给定字符列表为{‘A’,’B’}时,{‘A’=>0, ‘B’=>1}就是一个字符集;

字符序 (Collation) 是指在同一字符集内字符之间的比较规则;确定字符序后,才能在一个字符集上定义什么是等价的字符,以及字符之间的大小关系;每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序 (Default Collation);

MySQL 中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以 _ci(表示大小写不敏感)、_cs(表示大小写敏感)或 _bin(表示按编码值比较)结尾。例如:在字符序 utf8_general_ci 下,字符 “a” 和“A”是等价的;

字符集相关命令

检测字符集问题的一些手段

  • SHOW CHARACTER SET;
  • SHOW COLLATION;
  • SHOW VARIABLES LIKE ‘character%’;
  • SHOW VARIABLES LIKE ‘collation%’;
  • 查看数据库的字符集 use dbname;SELECT @@character_set_database, @@collation_database;
  • 查看表的字符集 SHOW TABLE STATUS where name like 'table_name';
  • 查看表中列的字符集 SHOW FULL COLUMNS FROM table_name;

其他一些修改语句

# 修改数据库:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 修改表:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 修改表字段:
ALTER TABLE table_name MODIFY column_name VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;