MySQL garbled Problem Solution

First, analyze the garbled Information

1. garbled characters are written into the database;

2. query results are returned with garbled characters;

What is the case when garbled characters occur?

FirstMySQLEnter show variables like '% char %' in the command line to viewMySQLCharacter Set settings:

You can see in the query resultsMySQLThe character set of the client, database connection, database, file system, query result, server, and system in the database system is set here. The character set of the file system is fixed, the character set of the system and server is determined during installation and is irrelevant to the garbled problem. The garbled problem is related to the character set settings of the client, database connection, database, and query results.

* Note: The client views accessMySQLDatabase access through the command line, the command line window is the client, access through JDBC and other connections,ProgramIs the client.

We areMySQLWhen writing Chinese data, encoding and conversion must be performed on the client, database connection, and database write respectively.

During query, encode and convert the returned results, database connections, and clients respectively.

Now we should be clear that garbled characters occur in one or more links of databases, clients, query results, and database connections.

Next, let's solve this problem.

When logging on to the database, we useMySQL-- Default-character-set = Character Set-u root-P to connect, then we use the show variables like '% char %' command to view the character set settings, you can find that the character set of the client, database connection, and query results has been set to the character set selected during logon.

If you have logged on, you can use the set names character set; command to achieve the above effect, equivalent to the following command:

Set character_set_client = Character Set

Set character_set_connection = Character Set

Set character_set_results = Character Set

If you connect to the database through JDBC, you can write the URL as follows:

Url = JDBC:MySQL: // Localhost: 3306/abs? Useunicode = true & characterencoding = Character Set

JSP pages and other terminals also need to set the corresponding Character Set

The character set of the database can be modified.MySQLTo specify the character set, you can also add the default Character Set character set when creating the database. Through this setting, the character set is unified throughout the data writing and reading process, there will be no garbled characters.

Why can't I write Chinese characters directly from the command line without any garbled characters?

It can be clear that the character set settings of client, database connection, and query results are not changed under the command line, and the input Chinese is transcoded and converted back to the initial character set, we can see that it is not garbled, but it does not mean that Chinese characters are properly stored as Chinese characters in the database.

For example, there is now a utf8 encoding database where the client connection uses GBK encoding and the connection uses the default ISO8859-1 (that isMySQLIn Latin1), we send the "Chinese" string in the client, the client will send a string of GBK format binary code connection layer, the connection layer in ISO8859-1 format to send this binary code to the database, the database stores the encoding in utf8 format. When we read this field in utf8 format, it must be garbled. That is to say, Chinese data is stored in garbled format when written to the database, during the query operation on the same client, a set of operations is performed to the contrary to the write operation. The wrong utf8 format binary code is converted into the correct GBK code and displayed correctly.