mysql charset=utf8 do you really understand what it means

1. Let's look at a table creation statement first

create table student(
  sid int primary key aotu_increment,
  sname varchar(20) not null,
  age int
)charset=utf8;

Consider a question:

• When we create a table, if we do not specify charset=utf8, why does an error occur when inserting Chinese characters?
• After specifying charset=utf8, why can Chinese be inserted again without garbled characters?

2. Check the character set of the CMD black window

Open the CMD black window –> place the mouse at the top of the window –> right click –> properties –> click options

From the above picture, we can know that the character encoding used for inputting text in CMD is GBK. At the same time, when you save files, you will often see the ANSI character set, which represents the local character set. In China, the local character set uses GBK encoding.

3.Have you noticed this problem?

The characters input by the client are all encoded in GBK. The characters stored by the mysql server are encoded in UTF8. Then, we add, delete, modify and query the database and table, and finally return to the client interface. In order to ensure that the characters are not garbled, they must go through the "encoding conversion process" . What I want to ask is, what exactly completes this encoding conversion process?

4. Several MySQL operation commands that you are not familiar with

-- View all character sets supported by the database (this command is executed by itself).
mysql> show character set;
-- Check the current status of the system, where you can see some character set settings.
mysql> status;
-- View the system character set settings, including all character set settings mysql> show variables like '%char%';

The operation results are as follows:

From the picture above we can see that there is something called "connection", and its Chinese name is "connector". The "connector" is used to perform the "encoding conversion process" .

1) Characteristics of connectors

① “Function of connector”:

Connect the client and server to convert character sets. The connector has this automatic conversion function.

② “Connector workflow”:
Ⅰ First, the client's characters are sent to the connector, and the connector selects an encoding to convert them (the encoding after conversion is consistent with the connector's encoding format) for temporary storage.
Ⅱ Then, the connector converts it into the encoding consistent with the server again and finally stores it in the server.
III Then, the result returned by the server passes through the connector again. The connector still selects an encoding to convert it (the encoding after conversion is consistent with the encoding format of the connector) for temporary storage.
IV Finally, the connector converts the result into a character set consistent with the client, so that it can be displayed normally on the client.

2) Illustration of the function of the connector

Figure 1:

Figure 1 is described as follows:

Figure 2:

Figure 2 is described as follows:

5. Practical demonstration of the above two figures

1) First, understand the following codes.

-- 1) Set the client's character set.
set character_set_client=gbk;
-- 2) Set the character set of the connector.
set character_set_connection=utf8;
-- 3) Set the character set of the returned result.
set character_set_results=gbk;

2) The code demonstration process is written in detail in the sql file in the following link, which you can download and view by yourself.

http://note.youdao.com/noteshare?id=3fe60a490637d1a51ac78bf4a9e7e4d0&sub=511D73BDDEA34D9BAC565249035D74A8

6. Two reasons for garbled characters

The garbled characters caused by the inconsistency between decoding and actual encoding can be repaired .

During the transmission process, due to inconsistent encoding, some bytes are lost, resulting in garbled characters that cannot be repaired.

1) Garbled characters caused by inconsistent encoding and decoding

2) Garbled characters are caused by lost bytes during transmission.

7. Analysis of the actual situation (what is the system default if nothing is set?)

1) Look carefully at the following pictures

According to the above picture, we can know (understand the following text description well):

Figure 2:

2) The meaning of set names gbk

-- When the character sets of the client, connector, and return value are the same and are all gbk, we can use the following abbreviation:
 set names gbk;
 -- The above SQL statement actually contains the following three meanings:
 set character_set_client=gbk;
 set character_set_connection=gbk;
 set character_set_results=gbk;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM