追根溯源，彻底搞清楚 Mysql JDBC 对 UTF-8 的支持

JDBC client 与 Mysqlserver 默认是自动进行检测的。如果服务器端指定了 character_set_server 变量 , 则 JDBC 驱动会自动使用该字符集 ( 在不指定 JDBC URL 参数 characterEncoding 和 connectionCollation 的情况下 ) 。

可以通过 characterEncoding ( 该参数值是使用 Java 风格的形式指定 . 例如 UTF-8 ) 来进行手工指定 , 而不是自动检测。

为了在 MySQL JDBC 驱动版本 5.1.46 及之前的版本中使用 utf8mb4, 则服务器端必须配置 character_set_server=utf8mb4,   否则 JDBC URL 参数 characterEncoding=UTF-8   表示的是 MySQL 的 utf8, 而不是 utf8mb4 。

this.io.serverCharsetIndex

/**
 * Initialize communications with the MySQL server. Handles logging on, and
 * handling initial connection errors.
*/
void doHandshake(String user, String password, String database) throws SQLException{

      ......

/* New protocol with 16 bytes to describe server characteristics */
// read character set (1 byte)
this.serverCharsetIndex= buf.readByte() &0xff;

      ......

}

collation[33] = new Collation(33, "utf8_general_ci", 1, MYSQL_CHARSET_NAME_utf8);

static  boolsend_server_handshake_packet(MPVIO_EXT *mpvio, const char *data, uintdata_len)

{

int2store(end,  mpvio->client_capabilities);

/* write server characteristics: up to 16 bytes  allowed */

end[2]= (char) default_charset_info->number;

int2store(end + 3,  mpvio->server_status[0]);

}

jdbc:mysql://xxx:3306?com.mysql.jdbc.faultInjection.serverCharsetIndex=45

collation[45] = new Collation(45, "utf8mb4_general_ci", 1, MYSQL_CHARSET_NAME_utf8mb4);

private booleanconfigureClientCharacterSet(booleandontCheckServerMatch) throws SQLException{

//从设置参数取值覆盖Mysql Server返回的字符集

// Fault injection for testing server character set indices
if (this.props!= null &&

this.props.getProperty("com.mysql.jdbc.faultInjection.serverCharsetIndex") != null) {

this . io . serverCharsetIndex = Integer . parseInt ( this . props .

}

}

Functionality  Added or Changed

• The  value  UTF-8  for  the connection property  characterEncoding  now  maps to the  utf8mb4  character set on the server  and, for MySQL Server 5.5.2 and later,  characterEncoding=UTF-8  can  now be used to set the connection character set to  utf8mb4  even  if  character_set_server  has  been set to something else on the server. (Before this change, the server  must have  character_set_server=utf8mb4  for  Connector/J to use that character set.)

• Also,  if the connection property  connectionCollation  is  also set and is incompatible with the value of  characterEncoding ,  characterEncoding  will  be overridden with the encoding corresponding to  connectionCollation .

if (getUseUnicode()) {

//1. 如果JDBC链接字符串指定了”characterEncoding=UTF-8”，根据Mysql Server返回的字符集确定是使用utf8或者utf8mb4

if (realJavaEncoding != null)  {

// Now, inform the server  what character set we will be using from now-on...

if (realJavaEncoding.equalsIgnoreCase( "UTF-8" ) || realJavaEncoding.equalsIgnoreCase( "UTF8" )) {

boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);

Boolean useutf8mb4 = utf8mb4Supported &&  (CharsetMapping.UTF8MB4_INDEXES.contains( this.io.serverCharsetIndex ));

}

} else if (getEncoding() != null) {

//2. 如果JDBC链接字符串未指定”characterEncoding”参数，则会使用Mysql Server返回字符集

String mysqlCharsetName =  getServerCharset();

if  (getUseOldUTF8Behavior()) {

mysqlCharsetName =  "latin1";

}

}

}

if (getUseUnicode()) {

  //1. 如果JDBC链接字符串指定了”characterEncoding=UTF-8”，则会默认使用utf8mb4字符集

` (realJavaEncoding != null)  {

// Now, inform the server  what character set we will be using from now-on...

if (realJavaEncoding.equalsIgnoreCase( "UTF-8" ) || realJavaEncoding.equalsIgnoreCase( "UTF8" )) {

// charset names  are case-sensitive

boolean utf8mb4Supported =  versionMeetsMinimum(5, 5, 2);

String utf8CharsetName =  connectionCollationSuffix.length() > 0 ? connectionCollationCharset

: (utf8mb4Supported  ? "utf8mb4" : "utf8") ;

}

} else if (getEncoding() !=  null) {

//2. 如果JDBC链接字符串未指定”characterEncoding”参数，则会使用Mysql Server返回字符集

// Tell the server we'll use the server  default charset to send our queries from now on....

String mysqlCharsetName =  connectionCollationSuffix.length() > 0 ? 

connectionCollationCharset   :  (getUseOldUTF8Behavior() ? 

"latin1 " : getServerCharset() );

}

}

//兼容UTF-8,utf-8,UTF8,utf8

}

java.sql.SQLException: Unsupported character  encoding 'UTF-8mb4'.

at  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)

at  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)

at  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)

at  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)

at  com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2575)

protected void postInitialization() throws SQLException{

if (testEncoding!= null) {
// Attempt to use the encoding, and bail out if it can't be used
try {
    String testString= "abc";
     StringUtils.getBytes(testString, testEncoding);
} catch (UnsupportedEncodingExceptionUE) {
     throw    SQLError.createSQLException(

Messages.getString("ConnectionProperties.unsupportedCharacterEncoding", new Object[] { testEncoding}),
    "0S100", getExceptionInterceptor());
   }
  }

}

static Charset findCharset(String alias) throws UnsupportedEncodingException{
    try {
      Charset cs = charsetsByAlias.get(alias);
       if (cs == null) {
          cs  = Charset.forName(alias);
     }

......
}