Detailed explanation of the solution to garbled characters in sql command query

This article describes the solution to the garbled code in the sql command query . If you don’t know the solution to the garbled code in the sql command query or are interested in the solution to the garbled code in the sql command query, then Let’s take a look at this article together. Okay, without further ado, let’s get to the point

MySQL command line query Solution to garbled characters:

The reason why MySQL will have Chinese garbled characters is nothing more than the following points:
1. The server itself is set Certain problems, such as still stuck in latin1

2. Table language setting problem (including character and collation)

3. Connection language setting problem of client program (such as php)

It is strongly recommended to use utf8, utf8 is compatible with all characters in the world

Step 1: Find the directory where mysql is installed and find the my.ini file;

Step 2: Use Notepad to open the my.ini file,

Find this place and see if the default-character-set is utf8. If not, just change it to utf8! (Previous versions may not have this sentence and just add it directly!)

# MySQL client library initialization. [client] port=3306 [mysql] default-character- set=utf8

Step 3: Enter in mysql databasecmd: show variables like'%char%';

Step 4: Change the original There is a database encoding method:

Step 5: Changed encoding method:

Step 6: View query results:

Step 6: Try inserting Chinese data! OK! It can be inserted!

Step 7: Solve the garbled characters when selecting * from table. In fact, only the fourth and seventh parts are used.

The above encoding method Change:

set character_set_results=gb2312; and it will display normally!

About GBK, GB2312, UTF8

UTF-8: Unicode Transformation Format-8bit, BOM is allowed, but usually not Contains BOM. It is a multi-byte encoding used to solve international characters. It uses 8 bits (that is, one byte) for English and 24 bits (three bytes) for Chinese. UTF-8 contains characters that are used by all countries in the world. It is an international encoding and has strong versatility. UTF-8 encoded text can be displayed on browsers in various countries that support UTF8 character set. For example, if it is UTF8 encoding, Chinese can also be displayed on foreigners' English IE, and they do not need to download IE's Chinese language support package.

GBK is a standard that is compatible with GB2312 after expansion based on the national standard GB2312. The text encoding of GBK is represented by double bytes, that is, both Chinese and English characters are represented by double bytes. In order to distinguish Chinese characters, the highest bits are set to 1. GBK contains all Chinese characters and is a national encoding. It is less versatile than UTF8, but UTF8 occupies a larger database than GBD.

GBK, GB2312, etc. must be converted to UTF8 through Unicode encoding:
GBK, GB2312--Unicode--UTF8
UTF8--Unicode--GBK, GB2312

For a website or forum, if there are many English characters, it is recommended to use UTF-8 to save space. However, many forum plug-ins now generally only support GBK.

GB2312 is a subset of GBK, GBK is a subset of GB18030
GBK is a large character set including Chinese, Japanese and Korean characters
If it is a Chinese website recommending GB2312 GBK sometimes still has some problems
In order to avoid all garbled code problems, UTF-8 should be used. It will also be very convenient to support internationalization in the future.
UTF-8 can be regarded as a large character set, which contains the encoding of most text.
One benefit of using UTF-8 is that users in other regions (such as Hong Kong and Taiwan) can view your text normally without garbled characters without installing Simplified Chinese support.

gb2312 is the code of Simplified Chinese
gbk supports Simplified Chinese and Traditional Chinese
big5 supports Traditional Chinese
utf-8 supports almost all characters

First analyze the situation of garbled characters

Several character sets involved in MySQL

character-set-server/default-character-set: Server character set, used by default.
character-set-database: Database character set.
character-set-table: Database table character set.
Priorityincreases in sequence. Therefore, under normal circumstances, only character-set-server needs to be set, and the character set is not specified when creating the database and table, so the character-set-server character set is uniformly used.
character-set-client: The client’s character set. Client default character set. When a client sends a request to the server, the request is encoded in this character set.
character-set-results: Result character set. When the server returns results or information to the client, the results are encoded in this character set.
On the client side, if character-set-results is not defined, the character-set-client character set is used as the default character set. So you only need to set the character-set-client character set.

To process Chinese, you can set both character-set-server and character-set-client to GB2312. If you want to process multiple languages ​​at the same time, set it to UTF8.

About Chinese issues with MySQL

The way to solve the garbled characters is to set the following three system parameters of MySQL to match the server character set character- before executing the SQL statement. The same character set as set-server.
character_set_client: Character set of the client.
character_set_results: Result character set.
character_set_connection: connection character set.
Set these three system parameters by sending statements to MySQL: set names gb2312

The above is all the content of this article. If you don’t know much about it, you can implement both sides by yourself. It’s easy to master!

Related recommendations:
sql queryHow to use in in an array

SQL query speed-up tips to prevent database code from locking the database

MySQL query statements for complex queries

##1 2 3 4

set character_set_database=utf8; set character_set_server=utf8; set character_set_client=gb2312; set character_set_connection=gb2312;

The above is the detailed content of Detailed explanation of the solution to garbled characters in sql command query. For more information, please follow other related articles on the PHP Chinese website!