SoFunction
Updated on 2025-05-19

Detailed explanation of the reasons and solutions for the occurrence of Chinese garbled code in Mycat

Preface

Mycat is an open source distributed database system that can help us build highly available and high-performance database clusters. However, when using Mycat, you sometimes encounter the problem of Chinese garbled code, which not only affects the correctness of the data, but may also cause unnecessary trouble to the business. This article will introduce in detail the reasons and solutions for the occurrence of Chinese garbled code in Mycat.

1. Problem phenomenon

When using Mycat, if you find that the data queried from the database has garbled Chinese code, it is usually represented as a question mark (?) or irregular characters. This phenomenon is not limited to query results, but may also occur in insertion or update operations.

2. Cause analysis

2.1 Character set configuration inconsistent

Most of the Chinese garbled problems in Mycat are caused by inconsistent character set settings between the client and the server. Consistent character encoding is required between Mycat, backend MySQL database and applications. Common encoding methods include UTF-8, GBK, etc.

2.2 Improper configuration file settings

Mycat configuration files and characters are not set correctly, which can also lead to Chinese garbled problems. In addition, the configuration of the connection pool and the parameter setting in the JDBC URL are also key factors.

2.3 The character set is not specified when the database table is created

When creating a database table, if the character set is not specified explicitly, the default character set of the database may be used by default, which may cause a mismatch to the character set of Mycat or other applications.

3. Solution

3.1 Unified Character Set

Make sure that all involved components (Mycat, MySQL database, application) use the same character set, and UTF-8 is recommended. The character set of MySQL can be checked and set by the following command:

-- View the current character set
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
 
-- set upMySQLGlobal character set
SET GLOBAL character_set_server=utf8;
SET GLOBAL collation_server=utf8_unicode_ci;
 
-- set upMySQLSession character set
SET NAMES utf8;

3.2 Modify Mycat configuration file

Edit Mycat's configuration file to ensure that the following configuration items are correctly set:

<system>
    <property name="charset">utf8</property>
</system>

Meanwhile, in ​​​​​, add character set parameters to the configuration for each data source:

<dataNode name="dn1" dataHost="localhost1" database="testdb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=UTF-8" user="root" password="root"/>
</dataHost>

3.3 Check the application's character set settings

Make sure that the application also specifies the correct character set when connecting Mycat. For example, if it is a Java application, you can add character set parameters to the JDBC URL:

String url = "jdbc:mysql://127.0.0.1:8066/testdb?useUnicode=true&characterEncoding=UTF-8";

3.4 Recreate the database table

If the data table already exists and the character set is incorrect, consider recreating the table and specifying the character set explicitly:

CREATE TABLE `example` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

4. Test verification

After completing the above configuration, restart the Mycat service and test whether the insertion and query of Chinese data are displayed normally through the application or directly using the SQL client.

By unifying the character set settings between various components, the Chinese garbled problem in Mycat can be effectively avoided.

5. Method supplement

Mycat is an open source distributed database middleware that can help you realize database read and write separation, database separation and table separation and other functions. When using Mycat, you may encounter problems with Chinese garbled code, which is usually related to character set settings. Below I will provide a specific example of how to configure the correct character set in Mycat to avoid the problem of garbled Chinese.

1. Modify Mycat configuration file

Mycat's configuration files are mainly located in the conf directory, including files such as ​​​​​ and ​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​ We need to set the character set correctly in these two files.

Modify ​​​​

Open the file, find the <property>​ tag in the <system>​ tag, and add or modify the following:

<system>
    <property name="charset">utf8</property>
    <property name="characterSetResults">utf8</property>
    <property name="useUnicode">true</property>
</system>

These settings ensure that Mycat uses UTF-8 encoding to process all data.

Modify ​​​​

Open the ​​​​​ file, find your database configuration section, add or modify the ​​default-character-set​​ property:

<schema name="testdb" checkSQLSchema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="users" dataNode="dn1" rule="mod-long" />
</schema>
 
<dataNode name="dn1" dataHost="localhost1" database="testdb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=utf8" user="root" password="root">
        <readHost host="hostS1" url="jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=utf8" user="root" password="root" />
    </writeHost>
</dataHost>

Make sure that the useUnicode=true​ and characterEncoding=utf8​ in the url​ parameter are set correctly.

2. Modify the character set of the MySQL database

Make sure the MySQL database itself also uses the UTF-8 character set. The character set of a database can be checked and modified by the following SQL commands:

-- Check the character set of the current database
SHOW VARIABLES LIKE 'character_set%';
 
-- Modify the character set of the database
ALTER DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;

3. Test the connection

After restarting the Mycat service, connect to Mycat using client tools (such as MySQL Workbench or command line tools) and execute some queries containing Chinese to ensure there are no garbled problems.

# Restart Mycat service./mycat stop
./mycat start
 
# Connect to Mycatmysql -h127.0.0.1 -P8066 -uroot -p
 
# Execute querySELECT * FROM  WHERE name = 'Zhang San';

4. Sample code

Assuming you are using Mycat in a Java application, you can refer to the following example code:

import ;
import ;
import ;
import ;
 
public class MycatExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://127.0.0.1:8066/testdb?useUnicode=true&amp;characterEncoding=utf8";
        String user = "root";
        String password = "root";
 
        try {
            // Load the driver            ("");
 
            // Get the connection            Connection conn = (url, user, password);
 
            // Create Statement object            Statement stmt = ();
 
            // Execute query            ResultSet rs = ("SELECT * FROM users WHERE name = 'Zhang San'");
 
            // Process the result set            while (()) {
                ("ID: " + ("id"));
                ("Name: " + ("name"));
                ("Email: " + ("email"));
            }
 
            // Close the resource            ();
            ();
            ();
        } catch (Exception e) {
            ();
        }
    }
}

Through the above steps and sample code, you should be able to solve the problem of Chinese garbled in Mycat. If the problem persists, check that each link is configured correctly and make sure that all components use the same character set.

Encountering Chinese garbled issues while using Mycat is a common problem, which is usually related to character set settings. Mycat is an open source distributed database system that supports multiple database backends (such as MySQL, PostgreSQL, etc.) and can be used as a middleware to optimize database access performance and scalability. Solving the Mycat Chinese garbled problem mainly involves the following configurations:

1. Mycat configuration file adjustment

Mycat's configuration files mainly include ​​​​ and ​​​​, and the character set needs to be set correctly in these two files.

In the ​​​​​​ file, make sure the ​​​charset property under the ​​<system>​ tag is set to UTF-8 or another character set you need:

<system>
    <property name="defaultSqlMode">STRICT_TRANS_TABLES</property>
    <property name="defaultAutoCommit">true</property>
    <property name="defaultTxIsolationLevel">REPEATABLE_READ</property>
    <property name="charset">UTF-8</property>
</system>

In the file, you can also specify a character set in the definition of each data node (<dataNode>) which is not necessary, but sometimes helps ensure consistency:

<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=UTF-8" user="root" password="password"/>
</dataHost>

Note the useUnicode=true​ and characterEncoding=UTF-8 in the URL parameters, which ensure that the MySQL connection uses the correct character set.

2. Database configuration

Make sure your MySQL database is also configured with the correct character set. You can check and set it with the following SQL commands:

-- View the character set of the current database
SHOW VARIABLES LIKE 'character_set_database';
 
-- View the currently connected character set
SHOW VARIABLES LIKE 'character_set_connection';
 
-- Set the database default character set
ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
 
-- Setting up the connection character set
SET NAMES utf8mb4;

3. Client application configuration

If your application accesses the database via Mycat, make sure that the correct character set is also specified in the application connection string:

// Java exampleString url = "jdbc:mysql://localhost:8066/your_database?useUnicode=true&amp;characterEncoding=UTF-8";
Connection conn = (url, "username", "password");

4. Test

After completing the above configuration, restart the Mycat service and test it through the client application or directly through Mycat to ensure that the Chinese display is normal.

5. Log check

If the problem persists, you can get more information by viewing Mycat's log files, which are usually located in the ​​logs​ folder of the Mycat installation directory. Log files can help you diagnose if there are other configuration issues or network issues.

Through the above steps, you should be able to solve the problem of Chinese garbled in Mycat. If the problem persists, it is recommended to check for other environmental factors, or try to upgrade Mycat to the latest version to take advantage of the latest fixes and improvements.

The above is the detailed explanation of the reasons and solutions for Mycat's Chinese garbled code. For more information on the solution to Mycat's Chinese garbled code, please pay attention to my other related articles!