最近线上项目微信登录失败，日志报错
org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xBD \xF0...' for column 'nickname' at row 1
### The error may exist in cn/qdgd/bps/basic/mapper/userCenter/AppUserThirdpartMapper.java (best guess)
### The error may involve cn.qdgd.bps.basic.mapper.userCenter.AppUserThirdpartMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO app_user_thirdpart ( id, product_id, nickname, bps_user_id, open_id, type, update_time, create_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xBD \xF0...' for column 'nickname' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\xBD \xF0...' for column 'nickname' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xBD \xF0...' for column 'nickname' at row 1
经查看日志 昵称保存报错
报错原因：
这是因为我们存放的中文，而我们的表并不支持中文字符集，使用
show variables like 'character%';查看mysql当前编码：
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8
character_set_system utf8
character_sets_dir /data/mysql/share/charsets/
我们可以看出
character_set_server使用的是utf8编码格式，而微信昵称可以加表情，某些微信表情占用4字节，而utf8是4字节编码格式，所以保存时会[1366]报错。
解决办法：
1 . 设置server为utf8mb4
show variables like '%char%';
set character_set_server=utf8mb4;
2 . 设置表的字符集格式
ALTER TABLE table_name CHARSET=utf8mb4;
3 . 设置表字段的字符集格式
ALTER TABLE table_name MODIFY nickname VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
4 . 设置数据库的字符集格式
SET NAMES utf8mb4
然后你会发现还是不行，在使用
show variables like 'character%';查看编码格式：
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8
character_set_system utf8
character_sets_dir /data/mysql/share/charsets/
你会发现还原回去了，因为jdbc每次连接数据库都会执行
SET NAMES utf8。
最终解决办法：
修改/etc/my.cnfmysql配置文件
[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
init_connect='SET NAMES utf8mb4'
然后重启mysql服务，问题解决。