MySQL UTF-8的坑
2019-05-08 本文已影响0人
Taourist
背景
MySQL的UTF-8并不是“真正”的UTF-8，实际只支持最大3个字节。这样会导致，当插入数据包含emoji表情时，数据库会报错。
真正的UTF-8是每个字符最多4个字节。
因此，MySQL官方在2010年发布了一个不同于UTF-8的字符集，叫utf8mb4。
编码参数
首先我们查看一下当前数据库的编码和排序规则
image.png
我们关心的几个变量是
- character_set_server：默认的内部操作字符集
- character_set_client：客户端来源数据使用的字符集
- character_set_connection：MySQL接受到用户查询后，按照character_set_client将其转化为
- character_set_connection设定的字符集。
- character_set_results：查询结果编码的字符集
- character_set_database：当前选中数据库的默认字符集
迁移
那我们需要从utf8迁移到utf8mb4时，需要做哪些事情呢？
- 修改数据库、表的编码
# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)
- 需要注意utf8mb4会影响列和索引的长度，例如tinytext可以存储255字节，相当于是85个三字节或63个四字节，如果一个tinytext类型的列，从utf8转为utf8mb4，那它能存储的字节数就变少了。
同理，索引也有这个影响，InnoDB引擎最大存储767个字节，对应utf8和utf8mb4，相当于255或191个字符。
因此，在修改编码时，需要注意这个影响 - 修改mysql数据库配置：修改mysql连接编码为utf8mb4，最简单的方法可以执行 set names 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
JDBC
如果使用MySQL Connector，需要清楚一下几点：
- 对于5.1.46或更早的 Connector版本，mysql服务器必须设置character_set_server=utf8mb4，否则会使用utf8
- 对于5.1.47或更后面的版本，指定characterEncoding=UTF-8，会默认映射为utf8mb4
- 不要使用查询来set names，因为驱动不会检测编码被更改，仍然会使用最开始建立连接时使用的编码
