MySQL 4字节utf8字符更新失败一例
业务的小伙伴反映了下面的问题
问题
有一个4字节的utf8字符'????'插入到MySQL数据库中时报错
java.sql.SQLException: Incorrect string value: '\xF0\xA0\x99\xB6' for column 'c_utf8mb4' at row 1
数据库中存放该字符的列已经定义为utf8mb4编码了,但相关的参数character_set_server的值为utf8。 比较奇怪的是使用mysql-connector-java-5.1.15.jar驱动时没有问题,使用更高版本的驱动如mysql-connector-java-5.1.22.jar,就会出错。JDBC的下面2个连接参数,不过设置与否,都没有影响。
characterEncoding=utf8
useUnicode=true
原因
jdbc驱动未正确设置SET NAMES utf8mb4导致转码错误。
根据MySQL官方手册,在MySQL Jdbc中正确使用4字节UTF8字符的方法如下:
http://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-14.html:
Connector/J mapped both 3-byte and 4-byte UTF8 encodings to the same Java UTF8 encoding.
To use 3-byte UTF8 with Connector/J set characterEncoding=utf8 and set useUnicode=true in the connection string.
To use 4-byte UTF8 with Connector/J configure the MySQL server with character_set_server=utf8mb4. Connector/J will then use that setting as long as characterEncoding has not been set in the connection string. This is equivalent to autodetection of the character set. (Bug #58232)
按照MySQL官方手册提供的方法,MySQL JDBC驱动内部会在建立连接时发送SET NAMES utf8mb4给服务端,确保正确进行字符编码。 所以,本问题属于应用未按要求使用MySQL JDBC。但5.1.15可以插入4字节字符也是比较奇怪的事情。 mysql-connector官网的 change log中并且提交5.1.15~5.1.22之间有相关的改动。但是,通过比较代码发现,这部分逻辑确实发生了变更。
5.1.15
com\mysql\jdbc\ConnectionImpl.java:
private boolean configureClientCharacterSet(boolean dontCheckServerMatch)
throws SQLException
{
...
if(getEncoding() != null)
{
String mysqlEncodingName = CharsetMapping.getMysqlEncodingForJavaEncoding(getEncoding().toUpperCase(Locale.ENGLISH), this);
if(getUseOldUTF8Behavior())
mysqlEncodingName = "latin1";
if(dontCheckServerMatch || !characterSetNamesMatches(mysqlEncodingName))
execSQL(null, (new StringBuilder()).append("SET NAMES ").append(mysqlEncodingName).toString(), -1, null, 1003, 1007, false, database, null, false);
realJavaEncoding = getEncoding();
}
...
}
给CharsetMapping.getMysqlEncodingForJavaEncoding()传入的参数是UTF-8,对应的mysql的编码有2个,utf8和utf8mb4, 其中utf8mb4优先,所以这个函数返回的mysql编码是utf8mb4。即之后执行了SET NAMES utf8mb4
相关代码:
com\mysql\jdbc\CharsetMapping.java:
public static final String getMysqlEncodingForJavaEncoding(String javaEncodingUC, Connection conn)
throws SQLException
{
List mysqlEncodings = (List)JAVA_UC_TO_MYSQL_CHARSET_MAP.get(javaEncodingUC);
if(mysqlEncodings != null)
{
Iterator iter = mysqlEncodings.iterator();
VersionedStringProperty versionedProp = null;
do
{
if(!iter.hasNext())
break;
VersionedStringProperty propToCheck = (VersionedStringProperty)iter.next();
if(conn == null)
return propToCheck.toString();
if(versionedProp != null && !versionedProp.preferredValue && versionedProp.majorVersion == propToCheck.majorVersion && versionedProp.minorVersion == propToCheck.minorVersion && versionedProp.subminorVersion == propToCheck.subminorVersion)
return versionedProp.toString();
if(!propToCheck.isOkayForVersion(conn))
break;
if(propToCheck.preferredValue)
return propToCheck.toString();
versionedProp = propToCheck;
} while(true);
if(versionedProp != null)
return versionedProp.toString();
}
return null;
}
...
CHARSET_CONFIG.setProperty("javaToMysqlMappings", "US-ASCII =\t\t\tusa7,US-ASCII =\t\t\t>4.1.0 ascii,...
UTF-8 = \t\tutf8,UTF-8 =\t\t\t\t*> 5.5.2 utf8mb4,...");
注:上面的定义UTF-8 =\t\t\t\t*> 5.5.2 utf8mb4中的*代表有多个mysql编码对应于同一个Java编码时,该编码优先
5.1.22
com\mysql\jdbc\ConnectionImpl.java:
private boolean configureClientCharacterSet(boolean dontCheckServerMatch)
throws SQLException
{
...
if(getEncoding() != null)
{
String mysqlEncodingName = getServerCharacterEncoding();
if(getUseOldUTF8Behavior())
mysqlEncodingName = "latin1";
boolean ucs2 = false;
if("ucs2".equalsIgnoreCase(mysqlEncodingName) || "utf16".equalsIgnoreCase(mysqlEncodingName) || "utf32".equalsIgnoreCase(mysqlEncodingName))
{
mysqlEncodingName = "utf8";
ucs2 = true;
if(getCharacterSetResults() == null)
setCharacterSetResults("UTF-8");
}
if(dontCheckServerMatch || !characterSetNamesMatches(mysqlEncodingName) || ucs2)
execSQL(null, (new StringBuilder()).append("SET NAMES ").append(mysqlEncodingName).toString(), -1, null, 1003, 1007, false, database, null, false);
realJavaEncoding = getEncoding();
}
...
}
...
public String getServerCharacterEncoding()
{
if(io.versionMeetsMinimum(4, 1, 0))
{
String charset = (String)indexToCustomMysqlCharset.get(Integer.valueOf(io.serverCharsetIndex));
if(charset == null)
charset = (String)CharsetMapping.STATIC_INDEX_TO_MYSQL_CHARSET_MAP.get(Integer.valueOf(io.serverCharsetIndex));
return charset == null ? (String)serverVariables.get("character_set_server") : charset;
} else
{
return (String)serverVariables.get("character_set");
}
}
解决办法
一直使用旧版的5.1.15驱动不是一个好办法,因此在使用新版驱动时,采取以下措施之一解决这个问题。
参考官网的说明,修改my.cnf
character_set_server=utf8mb4
在应用中获取连接后执行下面的SQL
stmt.executeUpdate("set names utf8mb4")
补充
根据5.1.22的MySQL JDBC驱动代码,MySQL JDBC支持utf8mb4需要满足以下2个条件
1. MySQL系统变量`character_set_server`的值为utf8mb4
2. MySQL JDBC连接参数characterEncoding的值为以下值之一
- null
- UTF8
- UTF-8