Mycat (3) temporarily solves the utf8mb4 encoding problem

Mycat (3) temporarily solves the utf8mb4 encoding problem
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/