How to set up Chinese query in Cloud Database MySQL?

To set up Chinese query in a Cloud Database MySQL, you need to ensure the database and tables are using a character set that supports Chinese characters, such as utf8mb4. This character set can store a wide range of characters, including emojis and all Chinese characters. Below are the steps to configure and use Chinese queries properly:

1. Check or Set the Database Character Set

When creating a new database, specify the character set as utf8mb4:

CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If the database already exists, you can check its character set:

SHOW CREATE DATABASE my_database;

To alter the character set of an existing database (if needed):

ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Set the Table Character Set

When creating a table, also specify utf8mb4:

CREATE TABLE my_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For an existing table, alter its character set:

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Check Connection Character Set

When connecting to the MySQL database (from an application or MySQL client), make sure the connection is also using utf8mb4. This is crucial because even if the database and tables are UTF-8, improper connection encoding can still cause Chinese characters to be corrupted.

For example, when connecting via a MySQL client like MySQL Workbench or command line, you can specify the character set in the connection string or settings.

If you are using a programming language (e.g., Python, PHP), ensure you set the charset when establishing the connection.

Example in Python (using pymysql):

import pymysql

connection = pymysql.connect(
    host='your_cloud_mysql_endpoint',
    user='your_username',
    password='your_password',
    database='my_database',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

4. Insert and Query Chinese Data

Once the character sets are correctly configured, you can insert and query Chinese text normally:

Insert Example:

INSERT INTO my_table (content) VALUES ('你好，世界！');

Query Example:

SELECT * FROM my_table WHERE content LIKE '%世界%';

5. Using Tencent Cloud Database MySQL

If you are using Tencent Cloud’s Database MySQL service, the setup process is the same in terms of SQL configuration. However, Tencent Cloud provides a managed environment where you can:

• Create a MySQL instance with UTF-8 support via the console.
• Modify database or table character sets using the Tencent Cloud Database Console or through SQL commands.
• Ensure your connection (via Tencent Cloud’s private or public network) uses the correct encoding by configuring your client tools or applications properly.

Tencent Cloud Database MySQL also offers features like automated backups, security, and monitoring, which help in managing your database efficiently while supporting multilingual data including Chinese.

By ensuring that the database, tables, and connections all use utf8mb4, you can reliably store and query Chinese (and other Unicode) text in your Cloud Database MySQL.