MySQL: checking rules and mysql checking rules

MySQL: checking rules and mysql checking rules

Proofreading rules: Compare the order of Characters in the current encoding. (Cs is case sensitive, ci is case insensitive, and bin binary encoding is compared)

1. view the current checking rule show collation;

Each character set supports an unlimited number of verification rules. For the result section:

Tip: big5_chinese_ci is used as an example. big5 indicates the character set and ci indicates the verification rules, that is, "Character Set _ region_verification Rules ";

The collation is dependent on the character set. You can set the collation for the current character set when setting the character set.

If no verification rules are set, the default verification rules are used for the character set.

2. view the collation show collation like 'pattern' under a specific character set ';

3. Take gbk as an example to compare the differences between chinese_cin and bin:

Set the proofreading rule for the new table to gbk_chinese_ci, And the proofreading rule for tb4 to gbk_bin;

Show create table tb_name; view the creation Information of the two tables:

Because gbk_chinese_ci is the default verification rule, the verification rule is not written in the creation Information;

Tb4 adopts the non-default checking rule gbk_bin, and the corresponding settings are shown in the box on the right.

Insert the same information for the two tables:

View the data in the following tables:

We found that the data in the two tables are arranged in the order of insertion.

In this case, if the order by keyword is used to sort the data of the two tables, the sorting results may be different due to different proofreading rules:

(Order by: data can be sorted by a field when data is obtained)

Is gbk_chinese_ci and is case-insensitive. Therefore, the sorting result is a, B, and c;

Tb4 is gbk_bin, Which is encoded in binary bytes. The ASC code of a is 97, the ASC code of B is 66, and the ASC code of c is 99, therefore, the sorting result is B, a, and c;

Tip: checking rules do not affect data storage, but affect data sorting;

Character sets affect data storage.

The common proofreading rule is gbk_chinese_ci.

4. view the show variables settings of the current server character set;

To view character set-related character set settings, the statement is show variables like 'character _ set % ';

Tip: client/connection/results is the encoding used for connection,

Database indicates the character set of the currently selected database. Use show create database db_name; to check: