Problems with character sets in MySQL database

Today in the Hibernate case, when writing to MySQL, a error:incorrect string value: ' \xe5\x8a\xa0\xe5\x86\x85 ... ' for column ' content ' is present Row 1

Later found in the program is written in the string encoding is utf-8, but because I am installed through the non-installation of MySQL, not set the database default Code table, so the database default code table is not utf-8, but latin1:

After finding the cause, the problem is solved by modifying the database Code table, and the database is successfully saved to record. The following is a summary of the problem of modifying the MySQL character set by reviewing the online chat:

First, MySQL's character set problem is mainly two concepts, one is Character sets, one is collations, the former is character content
and coding, which are some of the rules for comparing operations on the former. These two sets of parameters can be four levels in a DB instance, a single database, a table, a column, and so on
do not specify.

For users, it is generally recommended to use UTF8 encoding to store data. To solve the garbled problem, is not only the MySQL data storage problem, but also
is related to how the user's program files are encoded, the user program, and how the MySQL database is connected.

First of all, MySQL has the default character set, which is determined when the installation, when compiling MySQL can be default_charset=
UTF8 and default_collation=utf8_general_ci These two parameters (MySQL5.5 version, version 5.1 with--with-charset=
UTF8--with-collation=utf8_general_ci) to specify the default character set as UTF8, which is also the most permanent way, after this designation,
The encoding of the client connecting to the database is also UTF8 by default, and the application does not require any processing.

(1) The simplest method of modification is to modify the character set key values in the MySQL My.ini file,

such as Default-character-set = UTF8
Character_set_server = UTF8

After the modification, restart the MySQL services, service MySQL restart

Use mysql> show VARIABLES like ' character% '; view, discover that database encoding has been changed to UTF8

2. +--------------------------+---------------------------------+
4. | variable_name | Value |
6. +--------------------------+---------------------------------+
8. | character_set_client | UTF8 |
10. | character_set_connection | UTF8 |
12. | Character_set_database | UTF8 |
14. | Character_set_filesystem | binary |
16. | Character_set_results | UTF8 |
18. | Character_set_server | UTF8 |
20. | Character_set_system | UTF8 |
22. | Character_sets_dir | D: "mysql-5.0.37" Share "charsets" |
24. +--------------------------+---------------------------------+

(2) Another way to modify the MySQL default character set is to use the MySQL command

2. MySQL> SET character_set_client = UTF8;
4. MySQL> SET character_set_connection = UTF8;
6. MySQL> SET character_set_database = UTF8;
8. MySQL> SET character_set_results = UTF8;
10. MySQL> SET character_set_server = UTF8;
14. MySQL> SET collation_connection = UTF8;
16. MySQL> SET collation_database = UTF8;
18. MySQL> SET collation_server = utf8;

In general, even if you set the table MySQL default character set is UTF8 and send the query through UTF-8 encoding, you will find that the database is still garbled. The problem is on the connection connection layer. The workaround is to execute the following sentence before sending the query:

1. SET NAMES ' UTF8 ';

It is equivalent to the following three-sentence instruction:

1. SET character_set_client = UTF8;
2. SET character_set_results = UTF8;
3. SET character_set_connection = UTF8;

(3) Finally, if you do not want to change the character set in both ways, you can explicitly specify the encoding that the current database will use when creating the database:

Create database hibernate character set UTF8;

----------------------------------------------------------------------------------------------------------

Note: This section of this article refers to http://database.51cto.com/art/201010/229167.htm

Problems with character sets in MySQL database