技术栈
MYSQL8.0
node版本:14.15.1
express依赖包:4.16.1
mysql依赖包:2.18.1
问题概述
错误信息:
{"code":"ER_TRUNCATED_WRONG_VALUE_FOR_FIELD","errno":1366,"sqlMessage":"Incorrect string value: '\\xF0\\x9F\\x99\\x89\\xF0\\x9F...' for column 'nickname' at row 1","sqlState":"HY000","index":0,"sql":"INSERT INTO wechat_user_info(openid,nickname,sex,language,city,province,country,headimgurl,privilege,created_at) VALUES('xxxxxxxxxxxxxxxxx','Rohlin🙉🙈🙊',1,'zh_CN','深圳','广东','中国','https://thirdwx.qlogo.cn/mmopen/vi_32/4P7a5qOVkVBWAoMd8Hc2yvaQxRhSib4NicCtf3HesP98m0S0LV2ibeJa1RR3TwEcONyVfFlLsHPh1htaqPcGrOo1Q/132','','2021-06-21 14:38:36.850')"}
nickname字段存在emoji表情,导致存入mysql数据表失败;
解决方案
排查一:数据表中nickname字段字符集是utf8mb4是正常的,并非utf8,因为utf8不支持4个字节的表情;
排查二:可以直接通过sql语句插入表情,排查也无问题;
已经基本排除是数据表问题;
经过一番摸索后,发现需要在配置node连接mysql的时候加上charset:'UTF8MB4_UNICODE_CI',修改后重新npm run start,成功解决;