Deep understanding of Java and MySQL garbled issues

Recently we have built a Java Webserver with Tomcat and MySQL and deployed the game's server logic on the 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 only possible now two time points:

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

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

After debugging, we found that the server output of the 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, there is no way, can only bite the bullet to the one-to-one to clear these parameters.

L Character_set_server: When creating a database, assuming that 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, assuming that 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 MySQLServer receives the data sent by the client, the data is converted to the encoding specified by the character_set_connection.

L CHARACTER_SET_RESULTS:MYSQLSERVER the character encoding used to return the query results.

Knowing the meaning of these parameters, it is not so helpless, so in mind recall the process of data storage.

The steps to run the insert through mysqlclient, such as the following, mysqlclient the data entered by the user into character_set_client, sent to Server,server received, converted to character_set_ Connection, and the server then stores the data as character_set_table (which does not include this, assuming that the table was not specified when the value is Character_set_database).

In retrospect, it was found that when the table was built without the initiative to specify the character encoding after the table statement, then according to the description above, 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 mysqlclient, and the result of inserting 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, and then run the following statement, for example:

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

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

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

After flipping through countless documents, it is found that the assumption does not specify the connection encoding through the characterencoding attribute in the JDBC URL, and the JDBC driver uses character_set_server as the encoding for the connection, in this case, the latin1. Now that the reason has been found, the problem is better to conquer.

Change 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 you start the server again, you find that the correct characters are eventually present in the database.

Deep understanding of Java and MySQL garbled issues