在做app客户端后端,调皮的用户输入了表情或者特殊字符串。入库后报错,插入不进去的问题:
一、在MYSQL数据库中,将/usr/local/mysql/my.cnf修改为:
如果找不到my.cnf
用命令
mysql --help | grep my.cnf 来找位置
或者sudo find / -name my.cnf
[client]
#password = your_password
port = 3306
socket = /usr/local/mysql/data/mysql.sock
default-character-set=utf8mb4
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
character-set-server=utf8mb4
collation-server=utf8_general_ci
#no-auto-rehash
datadir =/usr/local/mysql/data
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
log_error=/usr/local/mysql/data/mysql-error.log
[mysql]
no-auto-rehash
socket = /usr/local/mysql/data/mysql.sock
default-character-set=utf8mb4
二 修改msyql 的数据库、表、字段的字符集
MySQL中默认字符集的设置有四级:服务器级,数据库级,表级 。最终是字段级 的字符集设置。注意前三种均为默认设置,并不代码你的字段最终会使用这个字符集设置。所以我们建议要用show create table table ; 或show full fields from tableName; 来检查当前表中字段的字符集设置。
修改数据库字符集:
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如:ALTER TABLE logtest CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
只是修改表的默认字符集:
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如:ALTER TABLE logtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
修改字段的字符集:
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查看数据库编码:
SHOW CREATE DATABASE db_name;
查看表编码:
SHOW CREATE TABLE tbl_name;
查看字段编码:
SHOW FULL COLUMNS FROM tbl_name;
三 修改java里 连接池信息
我们项目用的是阿里巴巴的DruidDataSource数据库连接池,贴一下修改位置
<bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource"
..... Add property.....
<property name="connectionInitSqls" value="set names utf8mb4;" />
</bean>
如果是apache的BasicDataSource
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
....
<!-- 此配置用于在创建Connection对象时执行指定的初始化sql -->
<property name="connectionInitSqls" value="set names utf8mb4;" />
</bean>
四 jdbc连接串设置:
jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=utf8
建议删除useUnicode=true&characterEncoding=utf8,
五 其他问题:
1.MySQL版本要求:不低于5.5.3(不支持utf8mb4编码)
2.JDBC驱动版本要求:mysql connector版本高于5.1.13