mysql 表情符串_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