MySQL Character Set details

MySQL Character Set details

Character garbled characters are common and frequently asked questions in the database. In view of the fact that at most my level can only be attributed to non-streaming and so on, I am very worried about misleading everyone when writing this article. The content is for reference only. If any error occurs, please point it out in time. I can learn to improve it!

MySQL character set has four levels: Server-level, database-level, table-level, and field-level.

I. Server-level Character Set

(1) You can set it in my. cnf.

[mysqld]default-character-set=gbk (5.1)character-set-server=gbk (5.5)

(2) You can set it in the startup options.

mysqld --default-character-set=gbk

(3) it can be set during compilation.

./Configure -- with-charset = gbk or cmake.-default-charset = gbk

If no server character set is specified, latin1 is the server character set by default.

(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/ |+--------------------------+-----------------------------------------+

Ii. Database Character Set

The character set of the database is specified when the database is created. You can also use the alter database statement to modify the character set after the database is created. If data already exists in the Database, modifying the database character set cannot store the existing data by the new character set. Therefore, you cannot modify the data content by modifying the database character set.

Set database character set rules:

(1) If character sets and verification rules are specified, the specified rules are used;

(2) If only the character set is specified but not the checking rule is specified, the specified character set and the default checking rule are used;

(3) If no character set or verification rule is specified, the server's Character Set and verification rules are used;

Iii. Table Character Set

The character set of the table is specified when the table is created. You can use the alter table statement to modify the character set. Similarly, for the existing data in the table, modifying the character set does not affect the original record, and the original character set will still be used.

The rules for setting character sets for tables are the same as those for setting character set rules for databases.

Iv. Column Character Set

You can specify the column Character Set and proofreading rules when creating a table or when modifying the table. (This is not commonly used, just record it)

V. set names command

In addition to the above four character sets, for actual application access, there are also character sets for interaction between the client and the server, as shown below:

(1) character_set_client: client Character Set
(2) character_set_connection: Connection Character Set
(3) character_set_resluts: Result Character Set

Generally, these three character sets must be the same to ensure that the data written by the user is correctly read, especially for Chinese characters.

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

6. Experiment on inserting Chinese character sets

Inconsistent character sets are the culprit for garbled Chinese content in the database.

Lab environment:

Server version: 5.6.28, it can only be attributed to different versions .)

Lab 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(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin11 row in set (0.00 sec)

As we can see from the above, the char_test table's character set is latin1. If the correct character set is not set, the following error will inevitably occur when Chinese characters are inserted:

Mysql> insert into char_test (name) values ('John '); ERROR 1366 (HY000): Incorrect string value: '\ xE5 \ xB0 \ x8F \ xE7 \ x8E \ x8B' for column 'name' at row 1

 

Solution

(1) first set names latin1 and then insert data.

Mysql> set names latin1; Query OK, 0 rows affected (0.00 sec) mysql> insert into char_test (name) values ('John '); Query OK, 1 row affected (0.01 sec) mysql> select * from char_test; + ---- + -------- + | id | name | + ---- + -------- + | 1 | Tom | 2 | James | 3 | Tom | + ---- + -------- + 3 rows in set (0.00 sec)

(2) specify set names latin1 In the data. SQL file, and then use the source command to import data. SQL.

# Vi data. sqlset names latin1; insert into char_test (name) values ('lily'); 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 set names latin1 In the data. SQL file, and then use the mysql command to import

# Vi data. sqlset names latin1; insert into char_test (name) values ('zhangzhang'); # mysql-uroot-p test1 <data. SQL # mysql-uroot-p-e "set names latin1; select * from test1.char _ test ;"

(4) Implement the -- default-charset-set = character set by specifying the character set parameters of the mysql Command

# Vi data. sqlinsert into char_test (name) values ('zhangzhang'); # 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' for column 'name' at row 1 # correct method # mysql-uroot-p -- default-character-set = latin1 test1 <data. SQL Enter password: ****** # mysql-uroot-p-e "set names 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 character set of the client 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)

This article permanently updates the link address: