MySQL set names command and MySQL character encoding problem

Let's look at the following execution results:

(Root@localhost)[(None)]Mysql>Show variables like 'character%';+--------------------------+-------------------------------------------------------------+|Variable_name|Value|+--------------------------+-------------------------------------------------------------+|Character_set_client|Utf8||Character_set_connection|Utf8||Character_set_database|Utf8mb4||Character_set_filesystem| binary                                                      ||Character_set_results|Utf8||Character_set_server|Utf8mb4||Character_set_system|Utf8||Character_sets_dir| /Usr/Local/Mysql-5.6. --Linux-GLIBC2.5-i686/Share/Charsets/ |+--------------------------+-------------------------------------------------------------+8Rowsinch Set(0.01sec) (Root@localhost)[(None)]Mysql>Setnames GBK; Query OK,0Rows Affected (0.00sec) (Root@localhost)[(None)]Mysql>Show variables like 'character%';+--------------------------+-------------------------------------------------------------+|Variable_name|Value|+--------------------------+-------------------------------------------------------------+|Character_set_client|Gbk||Character_set_connection|Gbk||Character_set_database|Utf8mb4||Character_set_filesystem| binary                                                      ||Character_set_results|Gbk||Character_set_server|Utf8mb4||Character_set_system|Utf8||Character_sets_dir| /Usr/Local/Mysql-5.6. --Linux-GLIBC2.5-i686/Share/Charsets/ |+--------------------------+-------------------------------------------------------------+8Rowsinch Set(0.01sec) (Root@localhost)[(None)]Mysql>Setnames utf8mb4; Query OK,0Rows Affected (0.00sec) (Root@localhost)[(None)]Mysql>Show variables like 'character%';+--------------------------+-------------------------------------------------------------+|Variable_name|Value|+--------------------------+-------------------------------------------------------------+|Character_set_client|Utf8mb4||Character_set_connection|Utf8mb4||Character_set_database|Utf8mb4||Character_set_filesystem| binary                                                      ||Character_set_results|Utf8mb4||Character_set_server|Utf8mb4||Character_set_system|Utf8||Character_sets_dir| /Usr/Local/Mysql-5.6. --Linux-GLIBC2.5-i686/Share/Charsets/ |+--------------------------+-------------------------------------------------------------+8Rowsinch Set(0.00Sec

1.character_set_system

The character set used by the server for storing identifiers. The value utf8 is always.

Character_set_system is the coded character set used when the system metadata (field name, and so on) is stored, regardless of the data stored in the field. Always fix the constant--utf8. We can not take care of it.

2. Character_set_server

charset_name Use as the default server character set. See section 10.5, "Character Set Configuration". If You use this option to specify a nondefault character set, you should also use to --collation-server specify the collation.

This variable sets the character set at the server level (Mysqld level) . That is, the set is a mysqld, the encoded character set used when all characters are last stored.

The default value is Lantin1. We are generally set to: UTF8, utf8mb4, GBK equivalent.

There are also server-level collations that are set up together:

collation_server:

Utf8mb4_bin, Utf8mb4_general_ci, Utf8_bin, Utf8_general_ci

CI represents: CaseSensitive Ignore is not considered case when sorting, while _bin end-of-order is considered case-sensitive.

3. Character_set_database

Every database has a database character set and a database collation. The CREATE DATABASE and ALTER DATABASE statements has optional clauses for specifying the database character set and collation:

db_name    charset_name]    collation_namedb_name    charset_name]    collation_name]

Character_set_database is a single database-level set of character sets that allows us to use different character sets for different databases under the same MYSQD.

Like what:

Create DATABASE db1 Character Set utf8mb4 collate Utf8mb4_bin;

This sets the database-level character set. If the CREATE DATABASE statement does not have the character and collate parameters, they are used by default:

The values for Character_set_server and character_collation as default values.

Similarly, there should be a database-level collation parameter:

Collation_database

4. Character_set_client

The character set for statements This arrivefrom the client. The session value of this variable are set using the character set requested by the client if the client connects to the Server. (Many clients support a, --default-character-set option to enable this character set to be specified explicitly. See also section 10.1.4, "Connection Character sets and collations".)

This is the coded character set used by the MySQL client to send the statement to mysqld.

You can use the--default-character-set parameter to display the settings.

5. Character_set_connection

The character set used for literals and does not has a character set introducer and for number-to-string Conv Ersion.

The coded character set for numeric to character conversion.

(Use Introducer to specify the character set of the text string:
– Format: [_charset] ' string ' [COLLATE collation]
For example
Select _latin1 ' string ';
select _utf8 ' Hello ' COLLATE utf8_general_ci;
– Text strings modified by Introducer are converted directly to internal character set processing without excessive transcoding during the request. ）

In fact, we generally no one to use introducer, so there is no introducer, so will use character_set_connection to encode.

6. Character_set_results

The character set used for returning query results such as result Sets or error messages to the client.

Mysqld the coded character set used when returning a query result set or error message to the client.

7. Set names ' xxx ' command

Can see the change is Character_set_client, Character_set_connection, Character_set_results

They are all related to the client. While the real server-side coded character set, Character_set_server and character_set_database, set names ' xxx ' cannot be modified at all.

set names ' xxx ' command can make character_set_client, Character_set_connection, character_set_results three unified :

Client (character_set_client)-----> character_set_connection-------> Mysqld------> Client (character_set_ Results

Reduce the need for encoding conversions.

8. Character_set_server and Character_set_database

The effect of both is the same, which is the coded character set used when the character is finally stored to disk. But the levels are different. Character_set_server sets the storage encoding character set for the mysqld level, and Character_set_database sets the storage encoding character set for a single database in Mysqld. and the default value of Character_set_database is the value of Character_set_server.

There are three times the encoding conversion process:

1) MySQL client uses character_set_client encoded characters------> character_set_connection encoded characters

------> MYSQLD: Here we need to decode the binary stream from the Character_set_connection encoded format into characters , and then use the character_set_server/character_set_ The database encodes the character again , generating a binary stream that, when stored, stores the binary stream data that is encoded again.

2) When reading the data, it uses Character_set_server/character_set_database to decode the two-level stream read into characters and then encode the character two times using Character_set_results. Generates a binary stream, which is sent to the MySQL client.

So using the set names ' xxx ' command, combined with the Character_set_server parameter, can set the entire character set of the process to the same, there will be no encoding conversion process.

9. Default-character-set = Charset_name Configuration Parameters


charset_name Use as the default character set for the client and connection (in fact there are character_set_results) .

A common issue that can occur when the operating system uses utf8 or another multibyte character set are that out Put from the MySQL client was formatted incorrectly, due to the fact that the MySQL client uses the latin1 Char Acter set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.

See sections 10.5, "Character Set Configuration", for more information.

Default-character-set can specify both the client side and the connection character, which is thevalue of Character_set_client and Character_set_connection, The value of the character-set-results is also actually set .

So the role of Default-character-set and set names ' xxx ' function is the same .

MySQL set names command and MySQL character encoding problem