MySQL Character set (Character set) and proofing Set (Collate)

Encountered a SQL statement that created the database

CREATE DATABASE testdb DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Note : This specifies that the character set is used UTF8instead of utf-8.

Interested in character SET and collate in the SQL statement above.

In a nutshell, CHARACTER set refers to a character set, whereas collate refers to a proofing set (which can be understood as a collation).

1. Character Set

1.1, 4 levels of the character set

mysql4.1 and its subsequent versions, support for character sets is divided into four levels:

Servers (server), database, data tables (table), and connections (connection):

Character_set_server: This is the set of character sets used by the server character_set_client: This is the set of character sets used by the client send query Character_set_connection : This is the set of character sets that the server needs to convert the received query string to Character_set_results: This is the character set that the server wants to convert the resulting data to, and the conversion is sent to the client.

You can view these 4 values by using the following SQL statement

SHOW VARIABLES like '%character_set% ';

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/85/53/wKioL1egHlLw1hjsAAAR0_bCgPs489.png "title=" Show_ Character_set.png "alt=" Wkiol1eghllw1hjsaaar0_bcgps489.png "/>

1.2, the whole process of character set

-the client sends a query;-the server receives the query, converts the query string from character_set_client to Character_set_connection, and executes the converted query;-the server converts the resulting data to Character_ The set_results character set is sent back to the client.

1.3. Set character sets

1, tell the server what type of data is sent character_set_client

2. Tell the converter what code to convert character_set_connection

3, the query results with what code character_set_results

If the above three are character set N, can be abbreviated to set names n;

For example, execute these three commands:

Set Character_set_client=gbk;set CHARACTER_SET_CONNECTION=GBK;SET_CHARACTER_SET_RESULTS=GBK;

These three sentences can be abbreviated as

Set names GBK;

1.4. View all character sets

SHOW CHARACTER SET;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/85/53/wKiom1egIWnQ9SfZAAApInGCGgM995.png "title=" Show_ Character_set.png "alt=" Wkiom1egiwnq9sfzaaapingcggm995.png "/>

2, Collate

Collate can be understood as, sort rules, and so on. A character set may have multiple proofing sets;

See all collation

SHOW COLLATION;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/85/53/wKioL1egJdbBOPuxAAAk09ENpeY334.png "title=" Show_ Collation.png "alt=" Wkiol1egjdbbopuxaaak09enpey334.png "/>

Reference Address:

Http://blog.sina.com.cn/s/blog_9707fac301016wxm.html

Http://www.2cto.com/database/201310/248493.html

Http://www.cnblogs.com/zzwlovegfj/archive/2012/06/25/2560649.html

http://blog.csdn.net/ACMAIN_CHM/article/details/4174186

MySQL Character set (Character set) and proofing Set (Collate)