MySQL中关于emoji表情的存储（微信登录出现登录失败【由于微信昵称字符问题】）

问题描述

今天网站遇到用户说登录失败，查看日志发现是微信登录自动生成用户时，用户创建失败。其中部分的日志如下：

{
 Cause: java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\xA6\\x84  ...' for column 'nick_name' at row 1;
 uncategorized SQLException; 
 SQL state [HY000]; error code [1366];
 Incorrect string value: '\\xF0\\x9F\\xA6\\x84  ...' for column 'nick_name' at row 1; 
 nested exception is java.sql.SQLException: Incorrect string value: '\\xF0\\x9F\\xA6\\x84  ...' for column 'nick_name' at row 1","path":"/login/weixin"
}

---

解决方法

既然说是字符串错误，大概率又是mysql留的坑，utf8只能存正常的字符串，对于emoji之类的占三、四个字节的表情不支持，而mysql5.7默认的字符集又是utf8。

1. 所以手动修改数据库表字段的字符集为utfmb4 – 这才是真正的utf8啊！！！
2. 然后再修改mysql数据库的默认字符集，防止以后创建的表再出现类似问题
   我的数据库是在linux下，一般位于 /etc/my.cnf

vim /etc/my.cnf

这是mysql5.7的默认配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
character_set_server=utf8
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

这里我们把
character_set_server=utf8
换成
character_set_server=utf8mb4
再加一个字符集排序规则
collation-server=utf8mb4_general_ci
你可以直接复制一下内容覆盖你的配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8'
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

---

闲谈

1. 微信最近新出的微信名可以使用emoji和彩色字，可能会导致微信登录获取微信昵称由于自己数据库字符集是utf8的问题导致存入失败
2. 记住安装mysql后直接先把mysql的默认字符集给改成utfmb4，utf这个字符集是个失败作品，utf8mb4是为了弥补一些utf8的问题。但是随着新的emoji越来越多，utf8mb4可能也不能完美的存下所有的emoji，如果碰到这种问题还是得在程序中做处理，不能完全依赖数据库的字符集。
3. 处理方式可以借鉴《王者荣耀》，把非法的字符直接给改了，不完全按照用户输入或者获取到的数据插入即可。