MySQL character set and proofing set

One, Character set

1.mysql character set is very flexible

You can set the server default character set;

Database default character set;

Table default character set;

column character set;

If a level does not specify a character set, the previous level is inherited.

View all character Set statements: show character set;

2. In the case of a table declaration as utf-8, the data that is ultimately stored in the table is Utf-8

① we want to tell the server, what code do I send to you? character_set_client

② tell the character set converter what encoding to convert to? character_set_connection

What code does the result of ③ query use? Character_set_results

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

3. What happens when there are garbled characters?

We first create a test table that specifies the table's character set as UTF8

CREATE TABLE Temp (

Name varchar (10)

) CharSet UTF8; CharSet UTF8; Determines the encoding of the server

Then 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;

Finally, insert a piece of data into the table:

INSERT into temp values (' China ');

SELECT * from temp; the results shown are normal.

When we execute this command again: set Character_results=utf8;

There is a garbled,

This results in garbled characters when the character set of the result returned does not match the character set of the client.

There is also a situation will be garbled: clientstatement and the fact that you are not utf8, you have to call me to GBK, such as:

There may be a number of other cases that are not listed here.

4. Under what circumstances will the loss of data be caused?

Execute these three statements:

Set CHARACTER_SET_CLIENT=GBK;

Set character_set_connection=latin1;

Set CHARACTER_SET_RESULTS=GBK;

Insert a piece of data into the table: INSERT into temp values (' us ');

The results of the last query are as follows:

The data is missing.

It can be concluded that when the character set of the connection and the server is less than the client hour, the loss of the data may not be accurate, but it can be understood. Personal understanding, as in Java different data types are converted to each other, such as the double type cast to the int type, it will result in the loss of precision.

。。。。。。

Ag

Old server, similar to Google, and so on, the server uses the encoding is GBK or gb2312, now the development of the page use is UTF8, how to ensure that the data is not lost

Workaround: Where connection can be set to GBK or UTF8 can be

Second, proofing set

If a B c D is sorted by ASC, the result of the default sort is still a B c D, which is not sorted by ASCII

What is a proofing set?

Proofing set: Refers to the collation of a character set.

View all proofing Set statements: Show collation;

A character set can have one or more collations.

Show collation like ' utf8% '; all proofing sets for UTF8 are displayed

Take UTF8 as an example, the default is to use the UTF8_GENERAL_CI proofing set, you can also press binary to row, Utf8_bin

How do I declare a proofing set?

CREATE TABLE TableName

(

...

) CharSet UTF8 collate utf8_general_ci;

Note: The declared proofing set must be a valid collation set of character sets. For example, if your character set is UTF8, you cannot declare GBK's proofing set.

MySQL character set and proofing set