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

Posted by 杨青波 on 2020-12-02

问题描述

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

1
2
3
4
5
6
7
{
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
1
vim /etc/my.cnf

这是mysql5.7的默认配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 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
你可以直接复制一下内容覆盖你的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 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. 处理方式可以借鉴《王者荣耀》,把非法的字符直接给改了,不完全按照用户输入或者获取到的数据插入即可。


支付宝打赏 微信打赏

赞赏一下