Deep understanding of Java and MySQL garbled issues

Recently we have built a Java Web server with Tomcat and MySQL and deployed the game's server logic on that server.

Soon after the game was online, we found a large number of garbled characters in the database. This is a very serious problem and must be resolved immediately. But the problem is where, according to analysis, garbled can only appear in two time points:

1. When the data is transferred from the client to the server.

2. When the server stores the data in the database.

After debugging, we found that the server output data is normal, so the garbled problem can only occur when stored in storage.

Since it is the MySQL database coding problem, it is relatively good to do, first of all, enter the command:

Mysql>show variables like '%char% ';

+--------------------------+----------------+

| Variable_name | Value |

+--------------------------+----------------+

|character_set_client | UTF8 |

| character_set_connection| UTF8 |

|character_set_database | Latin1 |

|character_set_filesystem | binary |

|character_set_results | UTF8 |

|character_set_server | Latin1 |

|character_set_system | UTF8 |

+--------------------------+----------------+

The above command is used to display the database encoding currently used by MySQL. When you see so many data encoding, the head is a bit big, no way, can only bite the bullet to the one by one to figure out these parameters.

L Character_set_server: When creating a database, if no character encoding is specified, the system uses the value of Character_set_server as the default value.

L Character_set_database: When creating a table, if no character encoding is specified, the system uses the value of Character_set_database as the default value.

L Character_set_client: Defines the encoding of the data sent by the MySQL client.

L Character_set_connection: When the MySQL server receives the data sent by the client, it converts the data into the encoding specified by the character_set_connection.

L CHARACTER_SET_RESULTS:MYSQL the character encoding used by the server to return query results.

Knowing the meaning of these parameters, it is not so dazed and helpless, so the mind recalls the data storage process.

The process of inserting through the MySQL client is as follows, the MySQL client encodes the data entered by the user into character_set_client, sends it to the server, and the server receives it and translates it into character_set_connection. The server then stores the data as character_set_table (the above parameters do not contain this, that is, if the table is not specified, the value is Character_set_database).

Recall, found at the time of the construction of the table and did not actively specify the character encoding after the table statement, then according to the above description, the database table will use character_set_database specified character encoding, that is, latin1. Because UTF-8 encoded Chinese cannot be stored as latin1, when storing UTF-8 encoded Chinese in a latin1 encoded table, the database will turn unrecognized characters into a, and the process is irreversible.

To prove this, I tried to insert Chinese into the table via the MySQL client, and the result insert failed. So I decided to change the character encoding of the table to UTF-8 to see if I could insert Chinese into the table, after executing the following statement:

mysql> ALTER TABLE ' tableName ' defaultcharacter SET UTF8 COLLATE utf8_general_ci

The character encoding of the table was modified to UTF-8, and it was also possible to insert Chinese into the table.

I thought it was over, but when we started Tomcat to test it, we found that the database was still garbled. Since the MySQL client can insert Chinese, why not use JDBC? It is possible that JDBC does not use Character_set_client to encode client data.

After flipping through countless documents, it is found that if you do not specify a connection encoding through the Characterencoding property in the JDBC URL, JDBC driver uses character_set_server as the encoding for the connection, in this case, latin1. Now that the reason has been found, the problem is better solved.

Modify the JDBC URL to Jdbc:mysql://localhost/some_db?useunicode=yes

&characterencoding=utf-8, if you are using a Tomcat data source, then remember to replace ' & ' with ' & '.

Finally, when the server is started again, the correct characters are finally found in the database.