Causes and solutions to the garbled character set problem in MySQL database

Preface

Sometimes when we view database data, we see garbled characters. In fact, no matter what kind of database, as long as garbled characters appear, it is mostly due to problems with the database character set settings.

Next, we will introduce the setting of database character set and the solution to the garbled code problem.

Character set of mysql database

To put it simply, a character is like a single word, an encoding is like a number given to each word, a character set is like a collection of characters and encodings, and a validation rule is the corresponding sorting rule of the character set. The character set plus the corresponding validation rule is the language. (Each character set can have multiple collations, but there is a default collation)

The MySQL database can organize characters using the corresponding character set and verification rules by setting the character set. It's like using that language to interpret an article. For example: the utf8 encoding commonly used by Chinese people represents Chinese.

MySQL can support multiple character sets. Different tables in the same database and different fields in the same table can be specified to use different character sets.

MySQL character set rules

The character sets that mysql can set are:

1. The server's character set
2. The character set of the database
3. Character set word
4. Segment character set

If the character set is not set at a level, it inherits the character set of the level above it.

How to solve the garbled problem of MySQL database (i.e. how to modify the character set)

【1】View the character sets supported by MySQL

show character set;

【2】Check the database code

show variables like 'character_set%';

【3】View the character set currently used by MySQL

show variables like 'character%';

Analysis:

character_set_client: The character set of the client request data

character_set_connection: The character set of the client and server connection

character_set_database: The character set of the default database; if there is no default database, the character set specified by character_set_server will be used (it is recommended not to change it at will)

character_set_filesystem: Convert character_set_client to character_set_filesystem (default is binary, no conversion is done)

character_set_results: character set returned to the client

character_set_server: The default character set of the database server

character_set_system: system character set, the default is utf8. (Names of database tables, columns, and functions stored in catalog tables)

character_sets_dir: MySQL character set file storage path

Causes of garbled characters

character_set_client does not match the actual
character_set_results does not match the client page

【4】View the proofreading rules of the current database

show variables like 'collation%';

Analysis:

The naming rule of value: character set name + language + suffix

collation_connection : The character set of the current connection

collation_database : The default collation for the current date

collation_server : The default collation of the server

The meaning of the suffixes:
ci: case-insensitive
cs: case-sensitive
bin: binary sort

【5】Change character set

Solve the garbled problem (generally as follows)

<1> Temporary (only effective at the moment, and will restore to the original setting after exiting and re-entering)

set character_set_client = 'utf8';
set character_set_connection = 'utf8';
set character_set_results = 'utf8';

or

set names utf8;

<2>Permanent (after modifying the configuration file, you need to restart MySQL)
Modify or add the following to the my.cnf configuration file of mysql:

[client]
default-character-set = utf8 

[mysqld]
character_set_server = utf8 


[mysql]
default-character-set = utf8

Remark

<1>
Regarding the parameters below mysqld, after verification using mysql5.5, the following writing methods are all acceptable, and the modification is not unsuccessful due to the underscore:

character_set_server = utf8
character-set-server = utf8

<2>
Regarding the modification of the configuration file, sometimes due to compilation and installation, the mysql configuration file is not necessarily /etc/my.cnf, so the modification will not be successful.

<3>
After modifying the configuration file, restart mysql. Please check whether the character set and collation rules are as follows:

The following is successful. If you use a third-party tool such as Navicat to connect, Chinese characters are still garbled. Try changing the encoding of the tool.

This is the end of this article about the causes and solutions to the garbled character set problem in MySQL database. For more related content about garbled character set in MySQL database, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!