1:character_sets First look at the results of the first 10 queries: [email protected] [information_schema]>select * from Character_sets ORDER BY MaxLen DESC Limit 10;+--------------------+----------------------+---------------------------------+--------+| Character_set_name | Default_collate_name | DESCRIPTION | MaxLen |+--------------------+----------------------+---------------------------------+--------+| Utf32 | Utf32_general_ci | UTF-32 Unicode | 4 | | Utf16le | Utf16le_general_ci | Utf-16le Unicode | 4 | | GB18030 | Gb18030_chinese_ci | National Standard GB18030 | 4 | | UTF8MB4 | Utf8mb4_general_ci | UTF-8 Unicode | 4 | | UTF16 | Utf16_general_ci | UTF-16 Unicode | 4 | | Eucjpms | Eucjpms_japanese_ci | Ujis for Windows Japanese | 3 | | Ujis | Ujis_japanese_ci | EUC-JP Japanese | 3 | | UTF8 | Utf8_general_ci | UTF-8 Unicode | 3 | | GBK | Gbk_chinese_ci | GBK Simplified Chinese | 2 | | UCS2 | Ucs2_general_ci | UCS-2 Unicode | 2 |+--------------------+----------------------+---------------------------------+--------+ look at the official explanation:
| information_schema Name |
SHOW name |
Remarks |
| character_set_name |
charset Character set |
|
tr>
| default_collate_name |
default collation sort |
|
| descriptio N |
Description Description |
MySQL extension |
| maxlen |
maxlen Maximum length, number of bytes |
MySQL Extension |
This table includes all of the character sets supported by MySQL, a total of 41 character sets, with UTF8, the default sort Utf8_general_ci, a character up to three bytes. Chinese characters occupy three bytes under the UTF8. Show CREATE Table a bit: | Character_sets | CREATE temporary TABLE ' character_sets ' (' character_set_name ' varchar (+) not NULL DEFAULT ' ', ' default_collate_name ' varchar (+) NOT null default ' ', ' DESCRIPTION ' varchar ($) NOT null default ' ', ' MaxLen ' bigint (3) NOT null default ' 0 ') ENG Ine=memory Default Charset=utf8 | As we can see, engine=memory defaults to the MEMORY of the engine, that is, each reboot will regenerate an identical table 2:collations first look at the results of the first 10 queries: [Email protected] [Information_schema]>select * from collations order by ID limit 10;+-------------------+--------------------+----+- -----------+-------------+---------+| Collation_name | Character_set_name | ID | Is_default | is_compiled | Sortlen |+-------------------+--------------------+----+------------+-------------+---------+| Big5_chinese_ci | Big5 | 1 | Yes | Yes | 1 | | Latin2_czech_cs | latin2 | 2 | | Yes | 4 | | Dec8_swedish_ci | Dec8 | 3 | Yes | Yes | 1 | | Cp850_general_ci | cp850 | 4 | Yes | YEs | 1 | | Latin1_german1_ci | Latin1 | 5 | | Yes | 1 | | Hp8_english_ci | HP8 | 6 | Yes | Yes | 1 | | Koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | | Latin1_swedish_ci | Latin1 | 8 | Yes | Yes | 1 | | Latin2_general_ci | latin2 | 9 | Yes | Yes | 1 | | Swe7_swedish_ci | Swe7 | 10 | Yes | Yes | 1 |+-------------------+--------------------+----+------------+-------------+---------+ the same ritual, post the official explanation:
| information_schema Name |
SHOW name |
Remarks |
| collation_name |
COLLATION wire proofing |
|
tr>
| character_set_name |
charset corresponding character set |
MySQL extension |
| ID |
ID Sort the first, this should be MySQL itself, do not delve into |
MySQL Extension |
| is_default |
DEFAULT
Indicates whether the character set is compiled to the server |
MySQL extension |
| Is_co mpiled |
Compiled
involves the amount of string that is required to sort strings in a character set expressed in memory. |
MySQL extension |
| sortlen |
sortlen
The amount of string required to sort strings in the memory that is involved in the character set. |
MySQL extension |
In general, we can use show collation to look at this statement. Show CREATE TABLE A moment:------------------------------------------------------------------------------------------------------------ +| Collations | CREATE temporary TABLE ' collations ' (' collation_name ' varchar (+) not NULL DEFAULT ' ', ' character_set_name ' varchar (+) N OT null default ' ', ' ID ' bigint (one) NOT null default ' 0 ', ' is_default ' varchar (3) NOT null default ' ', ' is_compiled ' Varch AR (3) NOT null default "', ' Sortlen ' bigint (3) NOT null default ' 0 ') engine=memory default Charset=utf8 |+------------+--- ----------------------------------------------------the memory table, the system automatically generates and does not change. 3:collation_character_set_applicability look at the first 10 data, we query according to the conditions. [Email protected] [Information_schema]>select * from collation_character_set_applicability where character_set_name like '%utf% ' Limit 10;+-------------------+--------------------+| Collation_name | Character_set_name |+-------------------+--------------------+| Utf8_general_ci | UTF8 | | Utf8_bin | UTF8 | | Utf8_Unicode_ci | UTF8 | | Utf8_icelandic_ci | UTF8 | | Utf8_latvian_ci | UTF8 | | Utf8_romanian_ci | UTF8 | | Utf8_slovenian_ci | UTF8 | | Utf8_polish_ci | UTF8 | | Utf8_estonian_ci | UTF8 | | Utf8_spanish_ci | UTF8 |+-------------------+--------------------+10 rows in Set (0.00 sec) The usual, post the official explanation:
| information_schema Name |
SHOW name |
Remarks |
| collation_name |
COLLATION |
|
| Character_set_name |
Charset |
|
Obviously, it is a correspondence between a character set and a line proofreading. There is no doubt that this is also a memory table, which is automatically generated based on the version of the database when initialized. Let's talk about the difference between character sets and collations: the character set (character sets) stores the string, which refers to the smallest of the semantic symbols in the human language. such as ' A ', ' B ', and so on; line Proofing (collations) Rules compare strings, collations refers to the comparison rules between characters within the same character set each word Fu She uniquely corresponds to a character set, but a character set can correspond to multiple characters Fu She, one of which is the default word Fu She ( The word Fu She name in the Default Collation) mysql follows the naming convention: start with the character Fu She corresponding to the charset name, _ci (for case insensitive), _cs (for case sensitivity), or _bin (which means comparison by encoded value). For example: Under the word Fu She "Utf8_general_ci", the characters "a" and "a" are equivalent to look at the MySQL variables related to character set and proofreading: – character_set_server: Default internal operation character Set – Character_set_client: Character set used by client source data – character_set_connection: Connection layer Character Set – character_set_results: Query result Character Set – character_set_database: The default character set for the currently selected database – character_set_system: System metadata (field name, etc.) character set again look at the character set conversion process in MySQL: 1. When MySQL server receives the request, it converts the request data from Character_set_client to character_set_connection;2. To convert the request data from character_set_connection to the internal operation character set before doing an internal operation, the method is determined as follows: • Use the character set value for each data field; • If the above value does not exist, the default of the corresponding data table is used CHARACTER set setpoint (MySQL extension, non-SQL standard); • If the above value does not exist, the default CHARACTER set value for the corresponding database is used, and if the above value does not exist, the Character_set_server setting value is used. 3. Convert the operation result from the internal operation character set to character_set_results. which has to learn from others blog, the address of the following easy to understand, but also thank the Blogger's contribution spirit: http://www.laruence.com/2008/01/05/12.html
Information_schema series Character Set checksum (character_sets,collations,collation_character_set_applicability)
