Understanding MySQL's character set

In the database, character garbled is a common, multi-issue. In view of my level can only be attributed to the non-inflow of such, writing this article when the inner fear, really worried about misleading everyone. Content for reference only, if there are errors, please point out in time, I also good learning to improve!

The MySQL character set has 4 levels of settings, namely server-level, database-level, table-level, and field-level.

One, server-level character set

(1), can be set in MY.CNF

[MYSQLD]DEFAULT-CHARACTER-SET=GBK (5.1) CHARACTER-SET-SERVER=GBK (5.5)

(2), can be set in the startup options

Mysqld--DEFAULT-CHARACTER-SET=GBK

(3), can be set at the time of compilation

./configure--WITH-CHARSET=GBK          or CMake.-DEFAULT-CHARSET=GBK

If you do not specify a character set for the server, the latin1 is used by default as the server's character set.

(4), view the character set of the current server

Mysql> Show variables like '%char% '; +--------------------------+-----------------------------------------+| 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       |/usr/local/mysql-5.6.28/share/charsets/|+------------------- -------+-----------------------------------------+

Second, the database character set

The character set of the database is specified when the database is created, or it can be modified by the ALTER DATABASE statement after it has been created. If data already exists in the database, modifying the database character set does not hold the existing data in the new character set. Therefore, the contents of the data cannot be modified by modifying the database character set.

To set the rules for the database character set:

(1), if a character set and proofing rules are specified, the specified rules are used;

(2), if only the character set is specified and no proofing rules are specified, the specified character set and the default proofing rules are used;

(3), if you do not specify a character set and proofing rules, then use the server character set and proofing regulations;

Third, table character set

The character set of the table is specified when the table is in progress and can be modified by the ALTER TABLE statement. Similarly, for data that already exists in the table, modifying the character set does not affect the original record, and the original character set will still be used.

Sets the rules for the character set of the table with the setting of the database.

Four, column character sets

The character set and proofing rules for a column can be specified at the time of the table, or can be adjusted when the table is modified. (This is not used, just record it)

V. SET names command

In addition to the above four character sets, for actual application access, there is also a character set that interacts between the client and the server, as follows:

(1), Character_set_client: Client Character Set
(2), character_set_connection: Connection Character Set
(3), character_set_resluts: result character Set

Typically, these 3 character sets should be identical in order to ensure that the data written by the user is read correctly, especially for Chinese characters.

The Set names command is used to modify the values of these 3 parameters at the same time.

Vi. an experiment on Chinese character set insertion

Character set inconsistency is the culprit that causes the Chinese content in the database to be garbled.

Lab Environment:

Server version:5.6.28 (This shows that the experimental environment is due to the learning process, from the online reference to some of the data, the experimental process and data described a little discrepancy, not found the cause, can only be attributed to the version of the difference. ）

Experimental object:

Mysql> Show CREATE TABLE Char_test\g *************************** 1. Row ***************************       table:char_testcreate table:create Table ' char_test ' (  ' id ' smallint (6) not Null auto_increment,  ' name ' char (a) not NULL,  PRIMARY KEY (' id ')) engine=innodb auto_increment=3 DEFAULT Charset=latin11 row in Set (0.00 sec)

From the above, the character set of the Char_test table is latin1, if you do not set the correct character set, the following error will inevitably occur when inserting Chinese characters:

mysql> INSERT into Char_test (name) VALUES (' Xiao Wang '); ERROR 1366 (HY000): Incorrect string value: ' \xe5\xb0\x8f\xe7\x8e\x8b ' for column ' name ' at row 1

Solution Solutions

(1), set names Latin1 first, and then insert the data.

mysql> set names latin1; Query OK, 0 rows Affected (0.00 sec) mysql> insert into char_test (name) VALUES (' Xiao Wang '); Query OK, 1 row affected (0.01 sec) mysql> Select * from char_test;+----+--------+| ID | Name   |+----+--------+|  1 | Tom    | |  2 | Xiao Ming   | |  3 | Xiao Wang   |+----+--------+3 rows in Set (0.00 sec)

(2), specify set names Latin1 in the Data.sql file, and import data.sql through the source command.

# VI data.sqlset names Latin1;insert into char_test (name) values (' Xiao Li ');mysql> source Data.sqlquery OK, 1 row affected (0.00 sec) mysql> SELECT * from char_test;+----+--------+| ID | Name   |+----+--------+|  1 | Tom    | |  2 | Xiao Ming   | |  3 | Xiao Wang   | |  4 | Xiao Li   |+----+--------+4 rows in Set (0.00 sec)

(3), specify the set names Latin1 in the Data.sql file, and then import it via the MySQL command

# VI data.sqlset names Latin1;insert into char_test (name) values (' Xiao Zhang '); # mysql-uroot-p Test1 < Data.sql
# Mysql-uroot-p-E "set names Latin1;select * from Test1.char_test;"

(4) Implementation of the--default-charset-set= character set by specifying the character set parameters of the MySQL command

# VI Data.sqlinsert into char_test (name) values (' Xiao Zhang '); # error Method # Mysql-uroot-p  test1 < data.sql                      Enter password  : ******error 1366 (HY000) at line 1:incorrect string value: ' \xe5\xb0\x8f\xe8\xb5\xb5 ' to column ' name ' at row # correct method # Mysql-uroot-p  --default-character-set=latin1 test1 < data.sql Enter Password: ******# mysql-uroot-p-E "Set NA Mes latin1;select * from test1.char_test; " Enter Password: ******+----+--------+| ID | Name   |+----+--------+|  1 | Tom    | |  2 | Xiao Ming   | |  3 | Xiao Wang   | |  4 | Xiao Li   | |  5 | Xiao Zhang   | |  6 | Xiao Zhao   |+----+--------+

(5), specify the client's character set in the configuration file

VI my.cnf[client]default-character-set=latin1mysql> INSERT INTO char_test (name) VALUES (' Pony '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from char_test;+----+--------+| ID | Name   |+----+--------+|  1 | Tom    | |  2 | Xiao Ming   | |  3 | Xiao Wang   | |  4 | Xiao Li   | |  5 | Xiao Zhang   | |  6 | Xiao Zhao   | |  7 | Pony   |+----+--------+7 rows in Set (0.00 sec)

Understanding MySQL's character set