在 MySQL 中使用 utf8mb4 字符集

2018-08-04 约 758 字预计阅读 2 分钟

最近在向 MySQL 数据库里更新数据的时候，遇到了这样的一个报错：

1
"Incorrect string value: '\\xF0\\x9F\\x98\\x82\\xEF\\xBC...' for column 'content' at row 1"

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

utf8 和 utf8mb4

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

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

使用 utf8mb4

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

全局配置

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
[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

检查设置：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
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 字序；

1
create database dbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

新建数据库连接时，指定字符集 utf8mb4；

1
mysql+driver]://[username]:[password]@[host]:[port/dbname?charset=utf8mb4

从 utf8 切换到 utf8mb4

备份数据库数据；
升级 MySQL 版本到 5.5.3+；
修改数据库、表、字段字符集和字序；

1
2
3
4
5
6
7
8
# 数据库
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;

检查字段和索引 key 的最大长度，参考 Section 10.1.11 of the MySQL 5.5 Reference Manual；
修改数据库服务器设置，同上；
修复和优化所有表；

1
2
3
# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

或者直接使用该命令：

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

目录