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文件中
要注意&要用&转义
jdbc:mysql://192.168.1.123:3306/xfcloud?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&rewriteBatchedStatements=TRUE
阿里连接池里面要配置
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
<property name="connectionInitSqls" value="set names utf8mb4;" />