mysql数据库存储emoji表情

1.之前的mysql数据用的编码是utf8,uft8最多可以存储占3个字符的数据，而emoji表情数据是四个字符，因此mysql的utf8无法存储会报错

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'strContent' at row 1

所以就必须要修改mysql数据库的字符编码集为utf8mb4,utf8mb4是utf8的超集，兼容utf8存储的字符。utf8mb4是mysql5.5.3之后才有的字符编码集。

2.我的mysql是5.6.17版本，安装的路劲为D:\mysql\mysql-5.6.17-winx64，修改my.ini里面的编码配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]

# 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

# These are commonly set, remove the # and set as required.

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
basedir = D:\mysql\mysql-5.6.17-winx64
datadir = D:\mysql\mysql-5.6.17-winx64\data
port = 3306
# server_id = .....


# 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

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

3.win10下首先要暂停mysql服务

用管理员的身份打开cmd

cd到bin目录(D:\mysql\mysql-5.6.17-winx64\bin)下执行暂时服务命令

net  stop  mysql 

执行删除mysqld

mysqld -remove

重新注册mysqld服务

mysqld --install mysql --defaults-file="D:\mysql\mysql-5.6.17-winx64\my-default.ini"

启动数据库

net   start    mysql

以管理员登录查看

mysql -u root -p

执行查看字符编码命令

show variables like '%char%';

+--------------------------+----------------------------------------------+
| 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                                         |
| character_sets_dir       | D:\mysql\mysql-5.6.17-winx64\share\charsets\ |
+--------------------------+----------------------------------------------+
8 rows in set (0.00 sec)

4.java应用方面修改

mysql的连接驱动mysql-connector-java-5.1.21.jar的版本要高于5.1.13

config.properties里面

jdbc_url=jdbc:mysql://192.168.1.123:3306/xfcloud?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&rewriteBatchedStatements=TRUE

如果没有用属性文件直接配置在xml文件中

要注意&要用&amp转义

jdbc:mysql://192.168.1.123:3306/xfcloud?useUnicode=true&ampzeroDateTimeBehavior=convertToNull&ampautoReconnect=true&amprewriteBatchedStatements=TRUE

阿里连接池里面要配置

<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"

<property name="connectionInitSqls" value="set names utf8mb4;" />