mysql支持表情符存储

问题：
默认数据库是配置为utf-8字符编码的，遇到表情的存储时候会报如下异常

### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8E\x8F' for column 'nickname' at row 1
### The error may involve com.apl.pgs.mini.mapper.UserWechatMapper.insertSelective-Inline
### The error occurred while setting parameters
### SQL: insert into user_wechat          ( user_id,                                                        open_id_mini,                                                        nickname,                                           photo_url )           values ( ?,                                                        ?,                                                        ?,                                           ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8E\x8F' for column 'nickname' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x8E\x8F' for column 'nickname' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8E\x8F' for column 'nickname' at row 1] with root cause
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8E\x8F' for column 'nickname' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3005)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3003)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:136)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
        at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
        at com.sun.proxy.$Proxy143.execute(Unknown Source)

分析这个异常我们可以知道，是因为字段长度不足。查阅资料，utf-8是3字节的，而表情符号是4个字节存储的。
好在，5.5.6版本后的mysql数据库也支持了表情符号的存储，需要配置为utf8mb4;从名字上看，估计也是数据库官方当时为此为补救的（戏言，无考究）。

解决方案：

1：可以使用一些表情包工具，把表情进行utf8编号后存到数据库
2：修改mysql使得它支持utf8mb4
3：把表情符过滤掉（粗暴）

我这使用的是第二种方式，做些记录以供分享
注意：使用此方式最好是刚架构这个项目的时候；

1.在命令行窗口连接登入数据库（window，Linux都一样）
执行sql： SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

[root@izj6cj1ia0m0e7i73fstobz apl-pgs-mini]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
+--------------------------+----------------------------+
11 rows in set (0.00 sec)

从结果我们可以看到，数据库的编码是utf8的

2.修改数据客户端字符编码
执行sql：set names utf8mb4;

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> 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   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_general_ci         |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
+--------------------------+----------------------------+
11 rows in set (0.00 sec)

可以看到，客户端连接字符编码已经修改过来

3.修改服务端字符编码
修改配置文件;window对应的是my.ini; linux对应的是/etc/my.cnf
加入 character-set-server=utf8mb4 重启mysql即可

[mysqld]
#设置mysql客户端默认字符集
#default-character-set=utf8mb4
#
# 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

#允许最大连接数
#max_connections=200
#服务端编码
character-set-server=utf8mb4
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启后检查数据库的字符编码

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | utf8mb4_general_ci         |
| collation_server         | utf8mb4_general_ci         |
+--------------------------+----------------------------+
11 rows in set (0.00 sec)

mysql> exit;

可以看到，服务端的字符编码也修改过来了。

到此，我们就可以正常的存储含有表情数据了

数据库版本不能低于5.5