Basic tutorials for setting operations on character set in Mysql _mysql

1. View character Set related 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 ——— – Services
GBK ———— Gbk-utf8-utf8 ———-UTF8
GBK ————-Gbk-gbk-utf8 ———-UTF8
The client tells the server what encoding is used, and then tells the "converter" without direct access
The character set converter receives the client information to see what the server is encoded and then converts the character set from the client to the set of server settings
The server sends the message to the client and tells the converter first.

3. Character Set Variable interpretation

Character_set_client
The client character set, which connects to MySQL's client program's character set, which tells the converter client to send the character set.

Character_set_connection
Character set to be converted by converter conversion

Character_set_server
The character set used by the service side, the character set used in storing data

Character_set_results
What is the result of the query encoding

Inference: When there will be garbled

1:client What is inconsistent with the actual, Microsoft command prompt default is GBK, if set to UTF8 will appear garbled

When the 2:results is inconsistent with the client page

When will you lose data?

Connection and the server's character set are more likely to lose data than the client hours, such as UTF8 and converters GBK

Set when the client is UTF8 and the server is GBK

Set Character_set_client=utf8

Set Character_set_connection=gbk/utf8 you can do anything here, because the final converter converts to the type of the server

Set Character_set_results=utf8

4. Set the character Set variable

Set names UTF8 Enter the character set used for MySQL settings

Add in my.conf

[Client]
Default-character-set=utf8
[mysqld]
Default-character-set=utf8

5. Modify MySQL Character set
the process of modifying a database of the latin1 character set to a database in the GBK character set
(1), export table structure

mysqldump-uroot-p–default-character-set=gbk-d database_name > Createtable.sql

–DEFAULT-CHARACTER-SET=GBK indicates what character set to connect
-D indicates that only the table structure is exported and data is not exported
(2), modify the table structure in the Createtable.sql file defines the character set 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 the large table. It forces mysqldump to retrieve rows from the server one line at a time instead of retrieving all rows and caching it in memory when the money is exported
–extended-insert: Use multiple-line insert syntax that includes a list of several values. This dump file is smaller and can be accelerated when overloading files
–no-create-info: CREATE TABLE statement for each dump table is not recreated
–default-character-set=latin1: Install the original character set to export all the data, so that the exported file, all Chinese are visible, will not be saved into garbled
(4), bandwidth Data.sql, the set names Latin1 modified into set names GABK
(5) Creating a new database using the new character set

CREATE DATABASE database_name default CharSet GBK;

(6), CREATE TABLE, execute Createtable.sql

MYSQL-UROOT-P database_name < Createtable.sql

(7), import data, perform Data.sql

MYSQL-UROOT-P database_name < Data.sql