问题描述:
当微信昵称中有emoji表情符时,mysql数据库报更新异常:
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for colum n 'nickname' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
原因分析:这是由于mysql所设置的字符集不支持utf8mb4导致。
解决办法:
一、修改mysql server
改配置文件/etc/my.cnf(window为my.ini)
[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支持utf8mb4的版本是5.5.3+,必须升级到较新版本
查看版本:SHOW VARIABLES WHERE Variable_name LIKE ‘version%’;
执行数据库和表、字段的字符集sql:
ALTER DATABASE t_yown_userdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE t_yown_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table t_yown_user modify `NICKNAME` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '昵称';
重启mysql:
service mysqld stop
service mysqld start
检验是否修改好:
SHOW VARIABLES WHERE Variable_name LIKE ‘character_set_%’ OR Variable_name LIKE ‘collation%’
查询结果如下(所有结果集utf8mb4):
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 /usr/share/mysql/charsets/
collation_connection utf8mb4_general_ci
collation_database utf8mb4_unicode_ci
collation_server utf8mb4_unicode_ci
二、确认mysql驱动、连接串:
确保mysql connection版本高于5.1.13否则仍然不能试用utf8mb4
确认连接串 jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/t_yown_userdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
最后最好重启应用程序,祝顺利!