Unbeknown Mysql character set is installed

Recently, in the Mysql database that uses in project group, insert data to appear to be piled up in disorder, did next summary about this problem, here we speak of from the basiccest place, the account of deep administrative levels that produces to the mistake and settle way.

? Character (Character) is the smallest in pointing to human language watch justice symbol. For example ' A' , ' B' ;

? Give a series of character, gift to every character a numerical value, will represent corresponding character with numerical value, the encode that this one value is character (Encoding) . For example, we give character ' A' gifts numeric 0, to character ' B' gifts numeric 1, criterion 0 it is character ' the encode of A' ;

? Give a series of character and gift after corresponding encode, these character and all encode are character set to compositive gather (Character Set) . For example, give character list to be {' A' , ' when B'} , {' A'=>0, ' B ' =>1} is a character set;

? Character foreword (Collation) is to show the comparison between the character inside same character set is regular;

? After affirmatory character foreword, it is equivalent that what ability defines on a character set character, and the size between character concerns;

? Every character foreword is exclusive and corresponding a kind of character set, but a character set is OK and corresponding a variety of character foreword, having among them is acquiescent character foreword (Default Collation) ;

? The comply with of character foreword name in MySQL names a convention: With the begin of character set name of character foreword correspondence; State with _ci(big the ordinary form of a Chinese numeral is not sensitive) , _cs(states big the ordinary form of a Chinese numeral is sensitive) or _bin(states by encode the value is compared) terminal. For example: In character foreword " Utf8_general_ci " below, character " A " and " A " it is equivalent;

There were 6 crucial positions to use the concept of character set in MySQL server, they are: Client, Connection, Database, Results, Server, System. MySQL has idea of two character set: One is character set itself, one is regulation of character set desired result. Influence data is in character set to transmit and store the process handles way mediumly, and character set desired result affects ORDER BY and GROUP BY these sort means.

1, the system is variable:

– Character_set_server: Acquiescent interior operates character set

– Character_set_client: The client carries the character set that origin data uses

– Character_set_connection: Join layer character set

– Character_set_results: Inquire result character set

– Character_set_database: The acquiescent character set of database of current pitch on

– Character_set_system: Systematic metadata (field name) character set

– still has the variable of the Alexandrine face correspondence with Collation_ begin, use descriptive character order.

2, the character set that appoints text string with Introducer:

– format is: [_charset] ' String ' [COLLATE Collation]

– for example:

 SELECT _latin1 ' String ' ; SELECT _utf8 ' hello ' COLLATE Utf8_general_ci;

The text string that – decorates by Introducer is not passed in requesting a process turn redundantly code, direct changeover is processing of in-house character set.

Server character set (@@character_set_server)

Library character set (@@character_set_database)

Express character set

Field character set

Character_set_server: The set of acquiescent character set that when the server is installed, appoints.

Character_set_database: The character set set that a certain library in database server uses, if build,is demonstrate when the library, the character set setting that when use server is installed, appoints.

Character_system: The character set set that database system uses.

When founding a watch, every field Binary of if it were not for, can have a character set. If be not appointed, so in SHOW CREATE TABLE when, it is to won't be shown those who come.

Build watch time, of field character set choose means to be as follows:

If the character set * Else If @@character_set_database* Else @@character_set_server (that the watch of character set * Else If that * If field appoints appoints does not have set, this value is Latin1)

@@character_set_connection

@@character_set_results

@@character_set_client

Character_set_connection: The character set of join database sets a kind, if Php does not have the character set type that demonstrate joins the database is used to carry acquiescent character to install according to the server

Character_set_results: The database carries the character set set that when returning, uses to the client, if do not have demonstrate, the character set with use acquiescent server

Character_set_client: The client carries use character set, be equivalent to the character set setting in the webpage

The corrector of character set is regular set parts by the Character_set_connection above, character_set_database, character_set_server decision

Collation_connection: The corrector of join character set is regular

Collation_database: The corrector that acquiescent database uses is regular. When acquiescent database is changed the server installs this variable. If do not have acquiescent database, variable value is the same as Collation_server

Collation_server: The acquiesce of the server proofreads regulation

The Character_set_client in above content, character_set_connection, character_set_results suffers a client to carry acquiescent character set to affect, among them the influence of Mysql trends library that the acquiescent character set when Php compiles Mysql module also is linked likewise by it, affect the Character_set_connection of Php thereby, character_set_client set. Identifying character set when silent is not Utf8 when, install My.cnf

[Mysqld]default-character-set=utf8default-collation=utf8_general_ci

Default-character-set can change pair of memory layers only (Server, database, table, column, system) set, to the client end and the communication layer that the server carries do not have any influences.

Mysql>SHOW VARIABLES LIKE 'character_set_%';

1.Data will request to be Character_set_connection from Character_set_client changeover when MySQL Server receives a request;

2.Before having in-house operation, change request data from Character_set_connection operate character set for interior, its decide the method is as follows:

1) the value of CHARACTER SET set that uses every data field;

2) if afore-mentioned values are nonexistent, the set of DEFAULT CHARACTER SET that uses corresponding data list is worth (MySQL is patulous, be not SQL standard) ;

3) if afore-mentioned values are nonexistent, use the value of set of DEFAULT CHARACTER SET of corresponding database;

4) if afore-mentioned values are nonexistent, use Character_set_server set value.

3.the operation the result operates character set changeover to be Character_set_results from interior.

1, the field did not set character set, because this uses the data set of the watch

2, the watch did not appoint character set, the character set of inventory of acquiescent service data

3, the database did not appoint character set when found, because this uses Character_set_server set value

4, appoint character set without what go revising Character_set_server designedly, because this uses Mysql acquiesce

5, the character set with acquiescent Mysql is Latin1, accordingly, we used Latin1 character set, and the character set of our Character_set_connection is UTF-8, insert Chinese chaos to pile up also again place hard to avoid.

Anyhow, character set is appointed when founding Database, do not go achieve a goal through revising acquiescent configuration, of course the form that you also can use the character set that appoints a watch, but appear very easily omit, participate in a design in a lot of people especially when, easier careless mistake.

The content of more Devops and DBA respect can be shared from the back, interested friend can pay close attention to ~