Ssh remote connection to the MySQL database character set in linux and prevent garbled characters, sshmysql
Lab environment:
Local windows 8.1
Remote Connection Tool SecureCRT 7.3
CentOS 6.7 x86_64-bit Linux, kernel version 2.6.32-573
Mysql version mysql-5.5.32
1.1 MySQL database character set Introduction
Character Set is a set of character symbols, encoding rules, and comparison rules.
MySQL database CHARACTER set includes two concepts: CHARACTER set (CHARACTER) and COLLATION. Character set is used to define the storage method of MySQL Data strings, and the collation is used to define the method of comparing strings.
1.1.1 in the Internet environment, the character sets frequently used by MySQL include
Common Character sets |
Length of a Chinese character |
Description |
GBK |
2 bytes |
It is not an international standard and has good support for Chinese environments. |
UTF-8 |
3 bytes |
We recommend that you use this character set in a mix of Chinese and English environments. |
Latin1 |
1 byte |
Default Character Set of MySQL |
Utf8mb4 |
4 bytes |
UTF-8 Unicode for mobile Internet |
1.1.2 how does MySQL select an appropriate character set?
1) if the processing of a variety of text, published to countries in different languages, should select the Unicode Character Set, For mysql is the UTF-8 (each Chinese Character 3 bytes), more suitable for English less Chinese
2) If you only need to support Chinese characters, and the data size is large and the performance requirements are high, you can select GBK (fixed length, each Chinese Character occupies dual byte, and English also occupies dual byte ), more suitable for a large number of operations, comparative sorting, fixed-length character sets, high performance
3) to process mobile Internet services, you may need to use the utf8mb4 character set.
Teacher suggestion: no special requirements, please select UTF-8
1.1.3 view character sets supported by the current MySQL System
Mysql> show character set;
1.2 view the character set currently used by the System
Mysql> show variables like 'character _ set % ';
To avoid garbled characters, you must unify the above character sets.
1.3 change the character set of the client 1.3.1 after entering the database
Set names gbk;
Change the character set (1, 2, and 5) of the three clients to gbk.
Enter the following three commands:
Set character_set_client = gbk;
Set character_set_results = gbk;
Set character_set_connection = gbk;
1.3.2 change the character set of the client when logging on to the database.
[Root @ db02 ~] # Mysql-uroot-poldboy123-S/data/3306/mysql. sock -- default-character = gbk;
Mysql> show variables like 'character _ set % ';
1.3.3 modify the configuration file my. cnf
Valid permanently.
[Client]
Default-character-set = gbk
Note: In case of multiple instances, this step modifies the character set and/etc/my. cnf
1.4 change server Character Set 1.4.1 modify configuration file my. cnf
[Mysqld]
Default-character-set = utf8 # Applicable to versions 5.1 and earlier
Character-set-server = uft8 # Applicable to 5.5
It takes effect permanently after the service is restarted.
This modification affects 3rd and 6th character sets, which is equivalent to modifying
Character_set_database
Character_set_server
1.4.2 specify the server character set during compilation
-DDEFAULT_CHARSET = utf8 \
-DDEFAULT_COLLATION = utf8_general_ci \
-DEXTRA_CHARSETS = gbk, gb2312, utf8, ascii \
1.4.3 modify the character set of the database using the command line
We do not recommend that you modify the data in a database. The following command does not take effect on the existing data and affects the updated data.
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 using the command line
It is not recommended to modify a table with data. The following command is invalid for the existing data and affects the updated data.
Mysql> alter table oldboy. test character set latin1;
Mysql> show create table oldboy. test \ G
1.5 prevent garbled characters and unify character sets when creating a table store in other places 1.5.1
Database creation
Create database oldboy_utf8 default character set utf8 collate utf8_general_ci
Create a table
Create table 'student '(
'Id' int (4) not null auto_increment,
'Name' char (20) not null,
Primary key ('id '),
) ENGINE = InnoDB AUTO_INCREMENT = 10 default charset = utf8
1.5.2 Program
The code of the program should be unified with the database table, and the UTF-8 has no signature.
1.5.3 linux Server
Cat/etc/sysconfig/i18n
$ LANG character set must be consistent with the database
1.5.4 Character Set of the Connection Tool CRT
Change the default Character Set of SecureCRT to UTF-8.
1.5.5 how to execute SQL statements in the database
1) operation habits
Try not to insert data directly in the MySQL Command Line (influenced by the SSH client) and put the SQL statement into the file.
2) SQL File Format
Unified use of "uft8 without signature"
3) File Import Method
You can use source to execute the SQL file in the MySQL command line.
Command to import data mysql-uroot-poldboy123 oldboy <test. SQL
4) set the client character set when importing SQL statements
Add set names utf8 to the SQL file;
Or mysql-uroot-poldboy123 oldboy -- default-character-set = uft8 <test. SQL
1.6 change the character set of an existing database
Idea: export the table structure and data of the original database, delete the original database, create a new database, and import the table structure and data into the new database.
1.6.1 export table structure
Mysqldump-uroot-poldboy123 -- default-character-set = latin1-d dbname> alltable. SQL
-- Default-character-set = uft8 indicates to connect to the utf8 character set, and-d indicates to only export the table structure.
1.6.2 edit table structure statement
Table Structure alltable. SQL changes all latin1 strings to utf8 (you can replace them with sed)
1.6.3 export data
Make sure that the database is no longer updated and all data is exported (without the 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, force mysqldump to retrieve data from one row on the server instead of all rows, and CACHE the data to the memory before output.
-- No-create-info: do not CREATE a create TABLE statement
-- Extended-insert: Use the multi-row INSERT syntax that includes several VALUES lists. This makes the file smaller, IO smaller, and data import faster.
-- Default-character-set = latin1 export data according to the original character set. In this way, all Chinese characters in the exported file are visible and will not be saved as garbled characters.
1.6.4 modify my. cnf Configuration
Adjust the character set of the client and server, and restart and take effect.
1.6.5 create a database through utf8
Delete the original database, and then create database dbname default charset utf8;
1.6.6 import table structure
The imported table structure of the Modified Character Set
Mysql-uroot-poldboy123 dbname <alltable. SQL
1.6.7 import data
Mysql-uroot-poldboy123 dbname <alldata. SQL
PS: when selecting a directory character set, it is better to be greater than or equal to the source character set (larger font). Otherwise, unsupported data may be lost.