MySQL Encoding Problems Latin1 (iso8859)

Because of the project environment, ibatis is used in Java projects, while ibatis already has an aiscii (iso8859) Data Source. Therefore, MySQL databases must use Latin1 encoding, the mysql client library also uses Latin1 encoding. Normally, the database is used normally, but it is strange to find a few data items, such as only filtering data from "Taobao _ Taobao, as a result, we checked out the data of "elephant _ King" and joined the dBA for a long time, you can use the hex function to view the ASCII values of "Taobao _ Taobao" and "elephant _ King", which are also different from those of GBK encoding. The result is good and can be distinguished.

"Xiang _ Wang" and "Tao _ Tao" indicate the encoding problem. In this case, check the encoding and use show variables like 'colla % '. The result is:

-------------------------- + ---------------------------- +
| Variable_name | value |
+ -------------------------- + ---------------------------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir |/usr/share/MySQL/charsets/|

 

It's strange that the encoding is okay. I can't explain it based on Oracle's experience. I checked the MySQL document and found that there is another concept of a checkpoint set.

Show variables like 'colla % '; the result is as follows:

---------------------- + ------------------- +
| Variable_name | value |
+ ---------------------- + ------------------- +
| Collation_connection | latin1_swedish_ci |
| Collation_database | latin1_swedish_ci |
| Collation_server | latin1_swedish_ci |
+ ---------------------- + ------------------- +

It is also Latin1, but it is not very familiar with latin1_swedish_ci. After reading the document, I learned that it is a case-insensitive Swedish Code. Therefore, it is estimated that it is because of this proofreader.

Convert the verification set to latin1_bin and use alter table xxx convert (latin1_swedish_ci, latin1_bin) to solve the problem.

 

The best solution is to specify the encoding when creating a database, as shown in figure

Create Database dbtest default Character Set Latin1 collate latin1_bin;

Or this parameter is specified when the table is created,

Create Table tbtest (name varchar (40) primary key, Val varchar (1000) engine = InnoDB default charset = Latin1 collate = latin1_bin;