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驱动不是一个好办法，因此在使用新版驱动时，采取以下措施之一解决这个问题。

1. 参考官网的说明，修改my.cnf

   character_set_server=utf8mb4 

2. 在应用中获取连接后执行下面的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