Basic tutorial on setting character sets in MySQL _ MySQL

This article mainly introduces the basic tutorial on setting character sets in MySQL, and focuses on how to modify the MySQL character set. For more information, see 1. View character set variables

mysql> show variables like 'character%';

+————————–+——————————-+| Variable_name      | Value             |+————————–+——————————-+| character_set_client   | latin1            || character_set_connection | latin1            || character_set_database  | latin1            || character_set_filesystem | binary            || character_set_results  | latin1            || character_set_server   | latin1            || character_set_system   | utf8             || character_sets_dir    | c:\wamp\mysql\share\charsets\ |+————————–+——————————-+

2. character set conversion process
Client --- character set converter ---- service
Gbk ---- gbk-utf8-utf8 ---- utf8
Gbk ----- gbk-gbk-utf8 ---- utf8
The client needs to tell the server what the encoding is, and then doesn't tell the server directly to tell the "converter"
After receiving the client information, the character set converter can view the server encoding and convert the character set sent from the client to the character set by the server.
When the server sends a message to the client, it first tells the "converter"

3. interpretation of character set variables

Character_set_client
The character set of the client that connects to the mysql client program. this variable tells the character set sent by the converter client.

Character_set_connection
Character set to be converted to by converter

Character_set_server
Character set used by the server and used for data storage

Character_set_results
What encoding is the query result?

Inference: when will garbled characters appear?

1: What is different from the actual client? by default, the Microsoft command prompt is gbk. if it is set to utf8, garbled characters will appear.

2: when results does not match the client page

When will data be lost?

If the character set of connection and server is less than the client hour, data will be lost. for example, if the client is utf8 and the converter is gbk

Set this parameter when the client is utf8 and the server is gbk.

Set character_set_client = utf8

Set character_set_connection = gbk/utf8 everything can be set here, because the final converter will convert according to the server type

Set character_set_results = utf8

4. set character set variables

Set names utf8 to enter the character set used by mysql settings

Add in my. conf

[client]default-character-set=utf8[mysqld]default-character-set=utf8

5. modify the mysql character set
The process of modifying the database of the latin1 character set to the database of the gbk character set
(1) export the table structure

mysqldump -uroot -p –default-character-set=gbk -d DATABASE_NAME > createtable.sql

-Default-character-set = gbk indicates the character set used for connection.
-D indicates that only the table structure is exported, and data is not exported.
(2) modify the character set defined by the table structure in the createtable. SQL file as the new character set.
(3) export all records

mysqldump -uroot -p –quick –no-create-info –extend-insert –default-character-set DATABASE_NAME > data.sql

-Quick: change the message to dump large tables. It forces mysqldump to retrieve not all rows from the table in a row at a time on the server, and caches it to the memory
-Extended-insert: Use the multiline insert syntax that includes several values lists. In this way, the dump file is smaller, which can be accelerated when files are overloaded.
-No-create-info: do not recreate the create table statement for each dump table.
-Default-character-set = latin1: install the original character set to export all data. in this way, all Chinese characters in the exported file are visible and will not be saved as garbled characters.
(4) bandwidth data. SQL, change set names latin1 to set names gabk
(5) use a new character set to create a new database

create database DATABASE_NAME default charset gbk;

(6) create a table and execute createtable. SQL

mysql -uroot -p DATABASE_NAME < createtable.sql

(7) import data and execute data. SQL

mysql -uroot -p DATABASE_NAME < data.sql