mysql修改字符集存储emj表情

背景

使用mysql数据库的时候，如果字符集是UTF-8并且在java服务器上，当存储emoji表情的时候，会抛出以下异常（比如微信开发获取用户昵称，有的用户的昵称用的是emoji的图像）
java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\xAA",…’ for column ‘nick_name’ at row 1
因为之前数据的utf8只能存储3个字节，而 emoji表情有些需要占4个直接，所以数据库存储不了,需要更改数据库的字符集为utf8mb4.

解决方法:

1、升级数据库版本，以下测试均在mysql5.7.36进行。
2、调整参数解释

[client]
#客户端连接字符，当设置skip-character-set-client-handshake=true后无效
default-character-set = utf8mb4  

[mysql]
#mysql是非交互时使用的字符集
default-character-set=utf8mb4  

[mysqld]
#服务端字符集
character-set-server = utf8mb4 

#所谓utf8_unicode_ci，其实是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。
#简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响**DISTINCT**、**GROUP BY**、**HAVING**语句的查询结果。
#另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
collation-server = utf8mb4_unicode_ci 
 
#只有在普通用户的客户端连接时才能执行这个参数，超级用户或有连接管理权限的用户会跳过这个设置；
#一个init_connect可以带一个或多个sql语句；
#init_connect的sql语句中若包含错误，则会导致连接失败。
init_connect=’SET NAMES utf8mb4’

#表示忽略客户端字符集，使用服务端字符集。
skip-character-set-client-handshake = true

2、调整mysql字符集

/etc/my.cnf

[mysqld]
character-set-server = utf8mb4
#最为重要，表示忽略客户端字符集，使用服务端字符集。
#或者使用 character-set-client-handshake = false 两者用其一。至于其他client、mysql均可不用设置
skip-character-set-client-handshake = true

3、查询字符集

当设置完成后,使用命令查询字符集
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                                |
| character_sets_dir       | /opt/sudytech/mysql/share/charsets/ |
| collation_connection     | utf8mb4_general_ci                  |
| collation_database       | utf8mb4_general_ci                  |
| collation_server         | utf8mb4_general_ci                 |
+--------------------------+-------------------------------------+
11 rows in set (0.01 sec)
character_set_client (客户端来源数据使用的字符集)
character_set_connection (连接层字符集)
character_set_database (当前选中数据库的默认字符集) 
character_set_results (查询结果字符集) 
character_set_server (默认的内部操作字符集)
上述字符集必须是utf8mb4.

4、修改已存在库表字符集编码

//修改数据库字符集编码
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
 
//修改表字符集编码
ALTER TABLE table_name CHARSET=utf8mb4, COLLATE=utf8mb4_unicode_ci;
 
//修改字段字符集编码
ALTER TABLE table_name  CHANGE column_name column_name VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
例如:
ALTER TABLE `T_ARTICLE` CHANGE `title` `title` VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NULL  COMMENT '标题';