SSH remote connection MySQL database character set under Linux and prevent garbled

Lab Environment:

Local Windows 8.1

Remote Connection Tool SECURECRT 7.3

Linux distributions CentOS 6.7 x86_64 bit linux system, kernel version is 2.6.32-573

MySQL version mysql-5.5.32

1.1 MySQL Database Character Set introduction

Character set is a set of text symbols and codes and comparison rules.

The MySQL database character set includes two concepts: Character set (CHARACTER) and proofing Rules (COLLATION). Where the character set is used to define how MySQL data strings are stored, proofing rules define how strings are compared.

1.1.1 In the Internet environment, the character sets commonly used in MySQL are

Common character Sets	A Chinese character length	Description
GBK	2 bytes	Not an international standard, good support for Chinese environment
UTF-8	3 bytes	Chinese and English mixed environment, it is recommended to use this character set, with more
Latin1	1 bytes	Default character set for MySQL
Utf8mb4	4 bytes	UTF-8 Unicode, for mobile internet

1.1.2 MySQL How to choose the right character set

1) If the processing of a variety of text, published to different languages country region, should choose the Unicode character set, for MySQL is UTF-8 (3 bytes per kanji), more suitable for more English less Chinese

2) If only need to support Chinese, and the volume of data is very large, high performance requirements, optional GBK (fixed length, each Chinese character account for double-byte, English also account for double-byte), more suitable for a large number of operations, comparative sorting, fixed long character set, high performance

3) Handling mobile Internet services, may require the use of the UTF8MB4 character set

Teacher suggestion: No special needs, please choose UTF-8

1.1.3 View the character sets supported by the current MySQL system

Mysql> show Character set;

1.2 Viewing the character set currently used by the system

Mysql> Show variables like ' character_set% ';

If you want to not garbled, you should make the above character set unified

1.3 Changing the character set of the client 1.3.1 changing the client character set after entering the database

Set names GBK;

Change the character set of 3 clients (1, 2, 5) to GBK

Equivalent to entering the following 3 commands:

Set character_set_client = GBK;

Set character_set_results = GBK;

Set character_set_connection = GBK;

1.3.2 Changing the client character set when logging in to the database

[Email protected] ~]# mysql-uroot-poldboy123-s/data/3306/mysql.sock--DEFAULT-CHARACTER=GBK;

Mysql> Show variables like ' character_set% ';

1.3.3 Modifying a configuration file my.cnf

Permanent effect, above

[Client]

Default-character-set=gbk

Note: In the case of multiple instances, this step modifies the character set, to modify the/ETC/MY.CNF

1.4 Changing the service-side character set 1.4.1 modifying the configuration file my.cnf

[Mysqld]

Default-character-set=utf8 # #适合5.1 and Previous versions

CHARACTER-SET-SERVER=UFT8 # #适合5.5

Permanent after service restart

This modification affects the 3rd and 6th character sets, equivalent to modifying

Character_set_database

Character_set_server

1.4.2 specifying the service-side character set at compile time

-ddefault_charset=utf8 \

-DDEFAULT_COLLATION=UTF8_GENERAL_CI \

-DEXTRA_CHARSETS=GBK,GB2312,UTF8,ASCII \

1.4.3 the command line to modify the library's character set

It is not recommended to modify on a library with data, the following command is not valid for data that has existed before, which affects the updated data after

mysql> ALTER DATABASE Oldboy character Set latin1 collate = LATIN1_SWEDISH_CI;

Mysql> Show CREATE Database oldboy\g

1.4.4 Modify the character set of a table on the command line

It is not recommended to modify on a table with data, the following command is invalid for data that has existed before, which affects the data that is updated later

mysql> ALTER TABLE oldboy.test character set latin1;

Mysql> Show CREATE TABLE Oldboy.test\g

1.5 Prevent garbled characters, unify other local 1.5.1 when building a table to build a database note the unified character set

Build Library

Create DATABASE Oldboy_utf8 default character set UTF8 collate utf8_general_ci

Build table

CREATE TABLE ' student ' (

' ID ' int (4) NOT NULL auto_increment,

' Name ' char (a) is not NULL,

Primary KEY (' ID '),

) Engine=innodb auto_increment=10 DEFAULT Charset=utf8

1.5.2 Program

The coding of the program code should be unified with the library table, UTF-8 no signature

1.5.3 Linux Server

cat/etc/sysconfig/i18n

$LANG character set to be unified with the database

1.5.4 Connection Tool The character set of the CRT

Change the default character set for the SecureCRT tool to Utf-8

1.5.5 methods for executing SQL statements in a database

1) Operating Habits

Try not to insert data directly from the MySQL command line (SSH client impact) and put SQL statements in the file

2) format of SQL file

Unified use "Uft8 no signature"

3) How to import files

SQL file can be executed with source on the MySQL command line

Command mode import data mysql–uroot–poldboy123 Oldboy <test.sql

4) Setting the client character set when importing SQL statements

Add set names UTF8 in SQL file;

or mysql–uroot–poldboy123 Oldboy--default-character-set=uft8 <test.sql

1.6 Changing the database character set for existing data

Idea: Export the table structure and data of the original library, delete the original library, create a new library, import the table structure and data into the new library

1.6.1 Export Table Structure

mysqldump–uroot–poldboy123--default-character-set=latin1–d dbname >alltable.sql

--default-character-set=uft8 for connection in UTF8 character set,-D only Guide table structure

1.6.2 Edit Table Structure statements

Table Structure Alltable.sql change all latin1 strings to UTF8 (can be replaced with SED)

1.6.3 Exporting data

Make sure the database is no longer updated, export all data (without table structure)

mysqldump–uroot–poldboy123--quick--no-create-info--extended-insert--default-character-set =latin1 dbname > Alldata.sql

Parameter description:

--quick is used to dump large tables, forcing mysqldump to retrieve data from the server one row at a time instead of retrieving all rows, and outputting the front cache into memory

--no-create-info CREATE TABLE statement is not created

--extended-insert uses a multiline insert syntax that includes several values lists, so that the file is smaller, the IO is small, and the data is imported very quickly

--default-character-set =latin1 Export Data according to the original character set so that all Chinese is visible in the exported file and will not be stored as garbled

1.6.4 Modifying the MY.CNF configuration

Adjust client and server character set, restart effective

1.6.5 building a library through UTF8

Delete the original library and create database dbname default CharSet UTF8;

1.6.6 Import Table Structure

The table structure of the changed character set is imported

Mysql–uroot–poldboy123 dbname <alltable.sql

1.6.7 Importing Data

Mysql–uroot–poldboy123 dbname <alldata.sql

PS: When choosing a directory character set, be aware that it is better to be greater than or equal to the source character set (font size), or you may lose unsupported data

SSH remote connection MySQL database character set under Linux and prevent garbled