在MySQL中使用utf8mb4字符集

最近在向 MySQL 数据库里更新数据的时候,遇到了这样的一个问题:

“Incorrect string value: ‘\xF0\x9F\x98\x82\xEF\xBC…’ for column ‘content’ at row 1”

报错的字符串是一个 emoji 表情,emoji 表情要用 utf8mb4 字符集的字段存储,而我的数据库采用了 utf8 字符集,导致报错。

utf8 和 utf8mb4

详情可以看参考链接中 MacTalk 的微信公众号文章,简单总结如下:

  1. MySQL 中的 utf8 每个字符的编码长度最多三个字节,不是通俗意义上的 utf8 字符集,少很多字符;
  2. MySQL 中的 utf8mb4 字符集才是才是真正的 utf8 字符集,所以它能够正常编码 emoji 表情。
  3. 建议 MySQL 和 MariaDB 用户使用 utf8mb4 代替 utf8

使用 utf8mb4

MySQL 安装后默认的字符集是 utf8。使用 utf8mb4 字符集需要进行一些设置。

全局配置

如果能改变数据库服务器的设置,只需要修改 MySQL 配置文件,如下:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
# 忽略客户端的字符集请求,强制使用服务器字符集返回结果
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

检查设置:

mysql> 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 |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

数据库实例配置

如果不能改变数据库服务器的设置,只针对某个数据库使用 utf8mb4 字符集,需要:

  • 创建数据库时,指定 utf8mb4 字符集和 utf8mb4_unicode_ci 字序;
create database dbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
  • 新建数据库连接时,指定字符集 utf8mb4
mysql+[driver]://[username]:[password]@[host]:[port]/[dbname]?charset=utf8mb4

从 utf8 切换到 utf8mb4

  1. 备份数据库数据;
  2. 升级 MySQL 版本到 5.5.3+;
  3. 修改数据库、表、字段字符集和字序;
# 数据库
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# 表
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# varchar 字段
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(xx) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  1. 检查字段和索引 key 的最大长度,参考 Section 10.1.11 of the MySQL 5.5 Reference Manual
  2. 修改数据库服务器设置,同上;
  3. 修复和优化所有表;
# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

或者直接使用该命令:

mysqlcheck -u root -p --auto-repair --optimize --all-databases

参考链接

目录