MySql字符集从utf8升级到utf8mb4说明和实现方式[详细]

起源，项目中出现了异常，Message：Incorrect string value: '\xF0\x9F\x87\xB5\xF0\x9F...' for column 'signature' at row 1

经查，前端输入了emoji的表情——[cano . 40 🇵🇷]

因此了解到需要将数据库字符集改为 utf8mb4，下面记录了一步一步解决过程

1.了解 utf-8 和 utf-8mb4

utf8是“utf8mb3”的一个别名，可以支持1-3字节表示的unicode字符

utf8mb4的编码，可以支持1-4字节表示的unicode字符

2. utf-8mb4的作用

为了存储超出最大长度3字节的任何场景，最常见的就是Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和一些不常用的汉字，以及任何新增的 Unicode 字符等等

3. 字符集变更风险

`` MySql在5.5.3版本之后增加了一个utf8mb4的编码，mb4就是most bytes 4的意思，用来兼容四字节的unicode。其实，utf8mb4是utf8的超集，理论上原来使用“utf8”，然后将字符集修改为“utf8mb4”，也并不会对已有的utf8编码读取产生任何问题。当然，为了节省空间，一般情况下使用"utf8"也就够了``

4. 执行变更

4.1 备份数据库

4.2 判断当前MySql是否支持utf8mb4，如不支持升级版本到v5.5.3+

select version(); #查看mysql版本
SHOW variables LIKE ‘%char%’; #查看当前字符集
SHOW create database db_name; #查看数据库字符集
SHOW create table t_name; #查看表字符集

4.3 修改database、table、column字符集 (注意utf8mb4_general_ci和utf8mb4_unicode_ci)

# Update databse 
ALTER DATABASE dbgame CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

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

# Update colume (一般修改表就会同步表内列字符集，不过也有遇到过列还仍然是utf8的情况)
ALTER TABLE table_name CHANGE field field VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

!! 如果上述Sql无法修改列则使用以下语句：

# [$Character_Set]为字符集名称: utf8，[$Collation_Name]为排序规则名称，即字符序: utf8mb4_unicode_ci

ALTER TABLE [$Table] MODIFY [$Column_Name] [$Field_Type] CHARACTER SET [$Character_Set] COLLATE [$Collation_Name];

4.4 修改mysql配置文件my.cnf（window为my.ini）

　　　　如果提示不支持或不识别" Default-character-set = utf8mb4 "，则将 Default 改为小写 default 即可

# 对本地的mysql客户端的配置       
[client]
Default-character-set = utf8mb4
# 对其他远程连接的mysql客户端的配置
[mysql]
Default-character-set = utf8mb4
# 本地mysql服务的配置
[mysqld]
Character-set-lient-andshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
# 忽视连接时指定的字符集
skip-character-set-client-handshake = true

4.5 重启数据库，检查变量

service mysql restart

SHOW VARIABLES WHERE variable_name LIKE 'character_set_%' OR variable_name LIKE 'collation%';

注：如果经过上面操作仍无法存储emoji表情，修改表中字段对应的字符集为utf8mb4。（上面已有提到字段）

#例：修改table_name表中content字段支持emoji表情存取
SELECT * FROM table_name

SHOW FULL COLUMNS FROM table_name

ALTER TABLE table_name CHANGE content content VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. 连接字符串

其中的characterEncoding=utf8可以自动被识别为utf8mb4（兼容原来的utf8），而autoReconnection（当数据库连接异常中断时，是否自动重新连接？默认为false）强烈建议配上，忽略这个属性，可能导致缓存缘故，没有读取到DB最新的配置，导致一直无法试用utf8mb4字符集；

6. Other

提前统计好需要修改的数据库和表，以及涉及到的具体字段

提前准备好更新时的操作SQL语句，修改库时快速便捷，不易出错

如果是阿里云或腾讯云等云RDS，建议先创建一个新的实例，然后把库表结构转移过去，测试通过之后，再进行数据恢复和停服迁库操作。

参考：