使用Mysql服务器的utf8字符编码,在存入emoji表情时会报异常:
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for column 'name' at row 1
原因:
utf8是三字节,utf8mb4是4字节而emoji表情也是4字节。
解决方法:
修改mysql配置文件my.cnf(windows为my.ini)
my.cnf一般在etc/mysql/my.cnf位置。找到后请在以下三部分里添加如下内容:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
重启 MySQL Server、检查字符集
1.)重启命令参考:/etc/init.d/mysql restart
2.)输入命令:mysql,进入mysql命令行(如果提示没权限,可以尝试输入mysql -uroot -p你的密码)
3.)在mysql命令行中输入:
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
修改服务端的db配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE
username=root
password=root
大于5.1.13的版本去掉characterEncoding=utf8就ok,不去掉都是这种错:java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94'
附上批量转数据库表编码的代码:
@Test
public void testC() {
// 获取所有表
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/happy_help?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE", "root", "root");
ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), "root", null, new String[]{"TABLE"});
while(rs.next()) {
String tableName = rs.getString("TABLE_NAME");
String sql = "ALTER TABLE "+tableName+" CONVERT TO CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`";
PreparedStatement ps = conn.prepareStatement(sql);
ps.executeUpdate();
}
}
catch (Exception e)
{
e.printStackTrace();
throw new RuntimeException(e);
}
finally {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}