先转载别人对于mysql的设置。
(1) 因为mysql的utf-8不完全,不能支持四字节的字符存储;所以需要改成utf8mb4编码格式。下面就是mysql的配置。
(2) jdbc的url链接配置为: jdbc:mysql://xxxxxx:3306/?useUnicode=true&characterEncoding=utf-8 ,是为了保证中文字符的查询不乱码
(3) 在使用数据库连接池之后,还需要配置数据库连接池的参数:
<!-- 2.数据库连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- 配置连接池属性 -->
<property name="driverClass" value="${jdbc.driver}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- c3p0连接池的私有属性 -->
<property name="maxPoolSize" value="500" />
<property name="minPoolSize" value="10" />
<!-- 关闭连接后不自动commit -->
<property name="autoCommitOnClose" value="false" />
<!-- 获取连接超时时间 -->
<property name="checkoutTimeout" value="10000" />
<!-- 当获取连接失败重试次数 -->
<property name="acquireRetryAttempts" value="2" />
<!--为了解决utf-8保存emoji表情异常问题-->
<property name="connectionCustomizerClassName" value="com.ktp.project.logic.UTF8MB4ConnectionCustomizer"/>
</bean>
关键语句为: <property name="connectionCustomizerClassName" value="com.ktp.project.logic.UTF8MB4ConnectionCustomizer"/>
另外: com.ktp.project.logic.UTF8MB4ConnectionCustomizer类对应的代码:
package com.ktp.project.logic;
import com.mchange.v2.c3p0.AbstractConnectionCustomizer;
import java.sql.Connection;
import java.sql.Statement;
public class UTF8MB4ConnectionCustomizer extends AbstractConnectionCustomizer {
public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws java.lang.Exception {
Statement stmt = null;
try {
stmt = c.createStatement();
stmt.executeUpdate("SET names utf8mb4");
} finally {
if (stmt != null) stmt.close();
}
}
}
/*********************************************************/
一.将MySQL配置文件修改如下,没有的就添加
[client]port=3306
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
port=3306
basedir=D:\mysql
#解压目录
datadir=D:\mysql\data
#解压目录下data目录
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[WinMySQLAdmin]
D:\mysql\bin\mysqld.exe
[mysql]
default-character-set=utf8mb4
二.修改数据库和表字符集
修改数据库字符集:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
修改表的字符集:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
三.重启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 | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
这边有个坑,如果用Navicat数据库管理工具查询,会跟用控制台查询出的结果不一样,以控制台为准。
mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
如果sql_mode值为STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,那么任然存储不了,
去设置这个sql_mod 模式
mysql> set global sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> show variables like '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
做完这些就完成了,让MySQL支持Emoji表情
这句sql可以模拟向数据库插入emoji表情,INSERT INTO test(id) VALUES(UNHEX('F0F0F1F2'));
只要能够执行成功,就说明可以了,别管插入的信息在数据库里是什么样子,有可能是空白,有可能是问号。
****************************************************/