How to easily master connected character sets and proofread _ MySQL

It helps you easily master connected character sets and proofread rule system variables, which are related to the interaction between the client and the server. Some content has been mentioned in the previous chapter:

? Server character sets and verification rules can be used as the value of character_set_server and collation_server variables.

? The character set and collation rules of the default database can be used as the values of character_set_database and collation_database variables.

Character set and checking rule variables are also involved in client-to-server connection. Each client has a connection-related character set and checking rule variable.

Consider what a "connection" means when connecting to the server. The client sends SQL statements, such as queries, to the server through connections. The server sends a response to the client through a connection, such as a result set. For client connections, this will cause some problems concerning the character set and verification rules of connections. these problems can be solved through system variables:

? When the query leaves the client, which character set is used in the query?

The server uses the character_set_client variable as the character set used in the query sent by the client.

? What character set should the server convert to after receiving the query?

During conversion, the server uses character_set_connection and collation_connection system variables. It converts the query sent by the client from the character_set_client system variable to character_set_connection (unless the string text has a prefix like _ latin1 or _ utf8 ). Collation_connection is important for comparing text strings. It is not important to compare the column value strings because the column has a higher priority for checking rules.

? Which character set should the server convert to before sending a result set or returning an error message to the client?

The character_set_results variable indicates that the server returns the query result to the character set used by the client. Includes result data, such as column values and result metadata (such as column names ).

You can adjust the settings of these variables or rely on the default values (in this way, you can skip this chapter ).

Two statements affect the character set:

Set names 'charset _ name'
Set character set charset_name

Set names displays the character sets used in the SQL statements sent by the client. Therefore, the set names 'cp1251 'statement tells the server that "the information sent from this client will use the character SET cp1251 ". It also specifies the character set for the results sent from the server back to the client. (For example, if you use a SELECT statement, it indicates the character set used by the column value .)

The set names 'X' statement is equivalent to the three statements:

MySQL> SET character_set_client = x;
Mysql> SET character_set_results = x;
Mysql> SET character_set_connection = x;

Setting x to character_set_connection sets the default checking rule that collation_connection is x.

The set character set statement is similar, but sets the connection character set and proofreading rules for the default database. The set character set x statement is equivalent to the three statements:

Mysql> SET character_set_client = x;
Mysql> SET character_set_results = x;
Mysql> SET collation_connection =collation_database;

When a client connects, it sends the desired character set name to the server. The server sets character_set_client, character_set_results, and character_set_connection variables for that character set. (In fact, the server performs a set names operation for this character SET .)

For mysql clients, if you want to use a character SET different from the default character SET, you do not need to execute the set names statement at each startup. You can add a -- default-character-set option in the mysql statement line or the option file. For example, each time you run mysql, the following option file sets three character set variables to koi8r:

[Mysql]
Default-character-set = koi8r

For example, suppose column1 is defined as CHAR (5) character set latin2. If set names or set character set is not SET, for SELECT column1 FROM t, after the connection, the server returns all values of column column1 using the character set specified by the client. On the other hand, if you set names 'latin1' or set character set latin1, the server converts latin2 to latin1 before sending the result. Conversion may lose characters that do not belong to two character sets.

If you do not want the server to perform any conversions, set character_set_results to NULL:

Mysql> SET character_set_results = NULL;