场景:Java应用中调用微信接口获取微信用户的昵称作为系统用户名,在提交入库的时候报错如下

[org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1366, SQLState: HY000
2017-09-04 20:06:06,790 [http-7100-2] ERROR [org.hibernate.util.JDBCExceptionReporter] - Incorrect string value: '\xF0\x9F\x92\x95\xF0\x9F...' for column 'NICKNAME' at row 1
2017-09-04 20:06:06,790 [http-7100-2] ERROR [org.hibernate.event.def.AbstractFlushingEventListener] - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not update: [com.powerlong.org.pd.entity.plcrm.Member#7]
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)

排错如下:
登陆db服务器查询系统编码字符集:

root@clcdb21 21:31:  [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| 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               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

字符集就是utf8mb4_unicode_ci编码啊,但就是报错,保存不进去微信特殊表情符,查看db编码:

root@clcdb21 21:53:  [crm]> show create database crm;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| crm      | CREATE DATABASE `crm` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

没错就是utf8mb4编码,继续排查查看表的编码:

root@clcdb21 21:53:  [crm]> show create table member;  
CREATE TABLE `member` (
  `MEMBER_ID` varchar(50) NOT NULL COMMENT '主键ID',
  `NAME` varchar(128) DEFAULT NULL COMMENT '姓名',
  `SEX` varchar(10) DEFAULT NULL COMMENT '性别',
  `BIRTHDAY` datetime DEFAULT NULL COMMENT '生日',
  `ICON` varchar(255) DEFAULT NULL COMMENT '头像图片',
  `NICKNAME` varchar(128) DEFAULT NULL COMMENT '昵称',
  `EN_NAME` varchar(128) DEFAULT NULL COMMENT '英文名字',
  `MALL_ID` varchar(50) DEFAULT NULL COMMENT '商场ID',
  `ENABLED` varchar(10) DEFAULT NULL COMMENT '状态:0正常 1冻结 (暂时不用)',
  `IS_DEL` varchar(10) DEFAULT NULL COMMENT '是否删除 0 正常状态 1 删除状态',
  `MEMBER_SOURCE` varchar(10) DEFAULT NULL COMMENT '会员来源',
  `MOBILE` varchar(32) DEFAULT NULL COMMENT '手机号',
  `PHONE` varchar(32) DEFAULT NULL COMMENT '其他电话',
  `IDE` varchar(64) DEFAULT NULL COMMENT '身份证号码',
  `EMAIL` varchar(128) DEFAULT NULL COMMENT '邮箱',
  `ADDRESS` varchar(255) DEFAULT NULL COMMENT '地址',
  `OUTSIDE_ID` varchar(128) DEFAULT NULL COMMENT '其他系统账号ID',
  `MLEVEL` varchar(50) DEFAULT NULL COMMENT '会员级别',
  `MEMBER_NO` varchar(10) DEFAULT NULL COMMENT '会员号',
  `OVER_DATE` datetime DEFAULT NULL COMMENT '超时日期',
  `USER_NAME` varchar(50) DEFAULT NULL,
  `OPEN_CARD_GIFT` varchar(10) DEFAULT NULL COMMENT '是否领取开卡礼0没领取,1领取',
  `REMARK` varchar(255) DEFAULT NULL COMMENT '备注',
  `CREATER_NAME` varchar(128) DEFAULT NULL,
  `UPDATER_NAME` varchar(128) DEFAULT NULL,
  `CREATOR` varchar(20) DEFAULT NULL,
  `CREATED_DATE` datetime DEFAULT NULL,
  `UPDATOR` varchar(20) DEFAULT NULL,
  `UPDATED_DATE` datetime DEFAULT NULL,
  `RECORD_VERSION` decimal(10,0) NOT NULL,
  `IS_LIFE` varchar(2) NOT NULL COMMENT '是否休眠 -1 未知(也可以认为是不休眠): 0:休眠  1:不休眠(最后登录时间在2016-01-01前都为休眠用户)',
  `TOTAL_SCORE` decimal(65,30) DEFAULT NULL COMMENT '总积分,数据来源是根据member_id,去关联sc_member表里面求和所得。',
  `LAST_LOGIN_DATE` datetime DEFAULT NULL COMMENT '最后登录时间',
  `RESERVE_CREATE_DATE` datetime DEFAULT NULL COMMENT '备用创建时间字段',
  PRIMARY KEY (`MEMBER_ID`),
  KEY `INDEX_MEMBER_ISDEL` (`IS_DEL`),
  KEY `INDEX_MEMBER_MOBILE` (`MOBILE`),
  KEY `MEMBER_IDX2` (`MALL_ID`),
  KEY `MEMBER_IDX3` (`MALL_ID`,`IS_DEL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

编码就是utf8mb4,木问题,在查看字段的字符编码:

root@clcdb21 21:34:  [crm]> show full columns from member;
+---------------------+----------------+--------------------+------+-----+---------+-------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Field               | Type           | Collation          | Null | Key | Default | Extra | Privileges                      | Comment                                                                                                                                    |
+---------------------+----------------+--------------------+------+-----+---------+-------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| MEMBER_ID           | varchar(50)    | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references | 主键ID                                                                                                                                     |
| NAME                | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 姓名                                                                                                                                       |
| SEX                 | varchar(10)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 性别                                                                                                                                       |
| BIRTHDAY            | datetime       | NULL               | YES  |     | NULL    |       | select,insert,update,references | 生日                                                                                                                                       |
| ICON                | varchar(255)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 头像图片                                                                                                                                   |
| NICKNAME            | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 昵称                                                                                                                                       |
| EN_NAME             | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 英文名字                                                                                                                                   |
| MALL_ID             | varchar(50)    | utf8mb4_general_ci | YES  | MUL | NULL    |       | select,insert,update,references | 商场ID                                                                                                                                     |
| ENABLED             | varchar(10)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 状态:0正常 1冻结 (暂时不用)                                                                                                               |
| IS_DEL              | varchar(10)    | utf8mb4_general_ci | YES  | MUL | NULL    |       | select,insert,update,references | 是否删除 0 正常状态 1 删除状态                                                                                                             |
| MEMBER_SOURCE       | varchar(10)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 会员来源                                                                                                                                   |
| MOBILE              | varchar(32)    | utf8mb4_general_ci | YES  | MUL | NULL    |       | select,insert,update,references | 手机号                                                                                                                                     |
| PHONE               | varchar(32)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 其他电话                                                                                                                                   |
| IDE                 | varchar(64)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 身份证号码                                                                                                                                 |
| EMAIL               | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 邮箱                                                                                                                                       |
| ADDRESS             | varchar(255)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 地址                                                                                                                                       |
| OUTSIDE_ID          | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 其他系统账号ID                                                                                                                             |
| MLEVEL              | varchar(50)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 会员级别                                                                                                                                   |
| MEMBER_NO           | varchar(10)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 会员号                                                                                                                                     |
| OVER_DATE           | datetime       | NULL               | YES  |     | NULL    |       | select,insert,update,references | 超时日期                                                                                                                                   |
| USER_NAME           | varchar(50)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| OPEN_CARD_GIFT      | varchar(10)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 是否领取开卡礼0没领取,1领取                                                                                                               |
| REMARK              | varchar(255)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references | 备注                                                                                                                                       |
| CREATER_NAME        | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| UPDATER_NAME        | varchar(128)   | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| CREATOR             | varchar(20)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| CREATED_DATE        | datetime       | NULL               | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| UPDATOR             | varchar(20)    | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| UPDATED_DATE        | datetime       | NULL               | YES  |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| RECORD_VERSION      | decimal(10,0)  | NULL               | NO   |     | NULL    |       | select,insert,update,references |                                                                                                                                            |
| IS_LIFE             | varchar(2)     | utf8mb4_general_ci | NO   |     | NULL    |       | select,insert,update,references | 是否休眠 -1 未知(也可以认为是不休眠): 0:休眠  1:不休眠(最后登录时间在2016-01-01前都为休眠用户)                                        |
| TOTAL_SCORE         | decimal(65,30) | NULL               | YES  |     | NULL    |       | select,insert,update,references | 总积分,数据来源是根据member_id,去关联sc_member表里面求和所得。                                                                           |
| LAST_LOGIN_DATE     | datetime       | NULL               | YES  |     | NULL    |       | select,insert,update,references | 最后登录时间                                                                                                                               |
| RESERVE_CREATE_DATE | datetime       | NULL               | YES  |     | NULL    |       | select,insert,update,references | 备用创建时间字段                                                                                                                           |
+---------------------+----------------+--------------------+------+-----+---------+-------+---------------------------------+----------------------------------------------------------------

发现字段的编码都是utf8mb4_general_ci编码,将修改编码统一为utf8mb4_general_ci

set collation_connection = utf8mb4_general_ci ;
set collation_database = utf8mb4_general_ci ;
set collation_server = utf8mb4_general_ci  ;

发现MySQL5.7.18里面这样设置以后退出终端字符集会自动回到utf8mb4_unicode_ci 编码,在my.cnf里面的[mysqld]段增加如下配置:

skip-character-set-client-handshake        #忽略客户端的字符集
character-set-server = utf8mb4             #设置服务端编码为utf8mb4     
init_connect='SET NAMES utf8mb4'           #初始化连接都设置为utf8mb4字符
character-set-client-handshake = FALSE     #客户端字符集和服务端字符集不同的时候将拒绝连接到服务端执行任何操作
collation-server = utf8mb4_general_ci      #设置排序字符集utf8mb4_general_ci 为

重启MySQL服务,查看系统字符编码:

root@clcdb21 21:53:  [crm]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| 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               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

重启Tomcat应用,测试OK可以把微信Emoji表情符号保存入库;
PS:网上很多教程说是字符编码集用utf8mb4_unicode_ci即可支持,但是MySQL5.7.18版或其他新版本里面必须用utf8mb4_general_ci编码才能保存。

带符号 * 的表示必填项