Mysql 1366 error: incorrect data insertion due to Character Set conflict. Solution: mysql1366

Mysql 1366 error: incorrect data insertion due to Character Set conflict. Solution: mysql1366

I. error message: errorCode: 1366

Caused by: java. SQL. SQLException: Incorrect string value: '\ xE7 \ xAE...' for column 'description' at row 1

Ii. Cause of exception: Character Set conflict

When mysql inserts data into the database, data cannot be inserted because the inserted data encoding is inconsistent with the character set of the database field.

Iii. Tracing

Run show character set in the mysql database. You can see that the maximum length of different character sets is different.

If UTF-8 encoded data is inserted, the field character set in the database table is latin. (How to query the field character set: show full columns from user ;)

If the utf8 character set with the maximum length of 3 is stored in the latin database field with the maximum length of 1, the data will be truncated and cannot be inserted normally, resulting in an exception.

Iv. Solution

Modifies the character set encoding of a database.

The MySQL configuration file is called my. ini in Windows, and under the root directory of MySQL installation; under Linux, it is called my. cnf, which is located in/etc/my. cnf.

(1) window Server

Mysql database configuration file my. ini, which is placed in the mysql root directory. Search for the default-character-set attribute in this file and change the value to utf8: default-character-set = utf8.

(2) linux Server
Modify my. cnf
Vi/etc/my. cnf
Add under [client]
Default-character-set = utf8
Add under [mysqld]

Default-character-set = utf8

In a new version later than 5.5, if this is modified, it will cause an error that cannot be started,
The pid file update problem is reported.

Check the mysql Log as follows:

The default-character-set parameter is unavailable.

The configuration method for the new mysql version is
In [mysqld], replace default-character-set = utf8 with character-set-server = utf8.
Restart mysql by using sudo/etc/init. d/mysql restart.
Note that after modifying the database code, you need to delete the previous database and recreate it.

V. Supplementary knowledge
(1) Character (Character) refers to the smallest semantic symbol in human language. For example, 'A' and 'B;
Given a series of characters, each character is given a value, which is used to represent the corresponding character. This value is the character Encoding (Encoding ). For example, if 'A' is given A value of 0 and 'B' is given A value of 1, 0 is the encoding of 'A;

After a series of characters are given and the corresponding encoding is granted, a Set of all these characters and encoding pairs is the Character Set ). For example, if the given character list is {'A', 'B'}, {'A' => 0, 'B' => 1} is A character set;

(2) Collation refers to the comparison rules between characters in the same character set;
Only after confirming the character order can an equivalent character set be defined and the relationship between characters in size be defined;
Each Collation only corresponds to one character set, but one character set can correspond to multiple character sequences, one of which is the Default Collation );

The Character Sequence names in MySQL follow naming conventions: start with the character set name corresponding to the character sequence; start with _ ci (indicating case insensitive) and _ cs (indicating case sensitive) or end with _ bin (compare by encoding value. For example, in the collation ''utf8 _ general_ci '', ''a' and ''a' are equivalent;

(3) MySQL Character Set settings
System variables:
-Character_set_server: Default internal operation Character Set
-Character_set_client: character set used by the client source data
-Character_set_connection: Connection layer Character Set
-Character_set_results: Query Result Character Set
-Character_set_database: Default Character Set of the currently selected Database
-Character_set_system: System metadata (field name, etc.) Character Set