Mycat (3) temporarily solves the utf8mb4 encoding problem, mycatutf8mb4

Mycat (3) temporarily solves the utf8mb4 encoding problem, mycatutf8mb4

The original Article connection: http://blog.csdn.net/freewebsys/article/details/45537411 reproduced please indicate the source!

1. About utf8mb4

Utf8mb4 is a superset of utf8
Utf8mb4 is compatible with utf8 and can represent more characters than utf8.
Emoji is an emoticon. Its meaning comes from Japanese (Japanese)
Emojis are now widely used in mobile text messages and online chat software.
Emoji is a popular expression in foreign text messages.

2. utf8mb4 is supported in mycat.

A lot of detours have taken place. Theoretically, you only need to set it in charset of server. xml, but it is not that simple. The connection fails.

You can also configure utf8 gbk, but it seems that utf8mb4 encoding is too special, and mycat may have a bug.

Ultimate killer: Modify the source code, line org. opencloudb. mysql. nio. MySQLConnection: line 450.

    private static CommandPacket getCharsetCommand(int ci) {            String charset = CharsetUtil.getCharset(ci);            StringBuilder s = new StringBuilder();            LOGGER.info("################## MySQLConnection getCharsetCommand: "+ci+"\t|"+charset);            s.append("SET names utf8mb4 ");//.append(charset);            CommandPacket cmd = new CommandPacket();            cmd.packetId = 0;            cmd.command = MySQLPacket.COM_QUERY;            cmd.arg = s.toString().getBytes();            return cmd;        }

Solution: directly write the encoding of the dead connection settings. Of course, the mysql server also needs to modify the encoding.
Vi/etc/my. cnf

[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'

Client view code

SHOW VARIABLES LIKE 'character%'+--------------------------+--------------------+| 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              |+--------------------------+--------------------+rows in set (0.00 sec)

Modify the connection using jdbc:

jdbc.driverClassName=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUEjdbc.username=rootjdbc.password=password

Modify data tables: (modify Database and table fields)

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;ALTER TABLE table_name CHANGE column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Http://www.tutorialspoint.com/jdbc/jdbc-select-records.htm
Test the Connection database to view the character encoding:

//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample {   // JDBC driver name and database URL   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";     static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";   //  Database credentials   static final String USER = "username";   static final String PASS = "password";   public static void main(String[] args) {   Connection conn = null;   Statement stmt = null;   try{      //STEP 2: Register JDBC driver      Class.forName("com.mysql.jdbc.Driver");      //STEP 3: Open a connection      System.out.println("Connecting to a selected database...");      conn = DriverManager.getConnection(DB_URL, USER, PASS);      System.out.println("Connected database successfully...");      //STEP 4: Execute a query      System.out.println("Creating statement...");      stmt = conn.createStatement();      String sql = " show variables like 'character%' ";      ResultSet rs = stmt.executeQuery(sql);      //STEP 5: Extract data from result set      while (rs.next()) {                //Display values                System.out.print(rs.getString(1));                System.out.print("\t");                System.out.print(rs.getString(2));                System.out.println();            }      rs.close();   }catch(SQLException se){      //Handle errors for JDBC      se.printStackTrace();   }catch(Exception e){      //Handle errors for Class.forName      e.printStackTrace();   }finally{      //finally block used to close resources      try{         if(stmt!=null)            conn.close();      }catch(SQLException se){      }// do nothing      try{         if(conn!=null)            conn.close();      }catch(SQLException se){         se.printStackTrace();      }//end finally try   }//end try   System.out.println("Goodbye!");}//end main}//end JDBCExample

If utf8mb4 is returned, you can:

character_set_client    utf8mb4character_set_connection    utf8mb4character_set_database  utf8mb4character_set_filesystem    binarycharacter_set_results   utf8mb4character_set_server    utf8mb4character_set_system    utf8character_sets_dir  /usr/share/mysql/charsets/