MySQL Collation Method _mysql

The problem is this:
A test table, the character set used by the latin1.

Select to_id from test where to_id= ' cn like _ King ';
+---------------+
| to_id |
+---------------+
| CN Ceramics _ Pottery |
| cn like _ King |
+---------------+
2 rows in Set (0.00 sec)

Take CN like the king of the data, incredibly the CN tao _ Pottery data also taken back.
This is clearly not allowed.

To view their encodings:

(ROOT@IM_OFFLOG1A) [test]> Select Hex (' CN pottery ');
+----------------+
| Hex (' CN pottery ') |
+----------------+
| 636ECCD55FCCD5 |
+----------------+
1 row in Set (0.00 sec)

(ROOT@IM_OFFLOG1A) [test]> Select Hex (' cn like _ King ');
+----------------+
| Hex (' cn like _ King ') |
+----------------+
| 636ecff35fcdf5 |
+----------------+
1 row in Set (0.00 sec)

The coding is really different, but why does MySQL think the two records are the same?

At first we fixed the problem with the problem that collation caused.

Show Variables View
| collation_connection | Latin1_swedish_ci
| Collation_database | Latin1_swedish_ci
| Collation_server | Latin1_swedish_ci


Manually modify these parameters to Latin1_bin, the result is the same. This feels really strange.

Here's a first explanation of the MySQL collation naming rules:
They begin with their associated character set name, usually including a language name, and end with _ci (case-insensitive), _cs (case sensitive), or _bin (two yuan)

For example, the latin1 character set has the following correction rules:

Proofing rules Meaning
Latin1_german1_ci German DIN-1
LATIN1_SWEDISH_CI Sweden/Finland
Latin1_danish_ci Denmark/Norway
Latin1_german2_ci German DIN-2
Latin1_bin conforming to latin1 encoded binary
Latin1_general_ci Multiple languages (Western Europe)
Latin1_general_cs Multiple languages (Western European ISO), case sensitive
LATIN1_SPANISH_CI Modern Spain


Finally, we rebuild the table and manually specify the table-level collation as Latin1_bin.
This problem has been solved.


Then the question comes again, why do I not take effect when I test latin1_bin manually?

It turns out that MySQL chooses the table character set and the collation rule in the following way:
If character set X and collate y are specified, then character set X and collate y are used.
If you specify character set x without specifying collate Y, the default collation rules for character set X and character set X are used.
Otherwise, the server character set and the server proofing rules are used.

We specified character set when we were building the table, so it always takes the corresponding default collation.

Of course, we do not need to rebuild the form, just ALTER TABLE db_allot convert to CHARACTER SET latin1 COLLATE latin1_bin.

In addition, it is recommended that collation should try to use the corresponding bin type collation rules of character set, so it is not easy to make mistakes.


Tell me about my own experience.

Feel character set Latin1 collate Latin1_bin is the old version of the VARCHAR BINARY improvement, but the new version of the first with the character set set, and then use this character set name plus _bin set proofing rules for the binary, from And make sure the Chinese query is correct.
Test again, change this field property to do not take BINARY
ALTER TABLE ' comment_content_1_01 ' change ' thread ' thread ' VARCHAR DEFAULT NULL
Then see that the table structure does become ' thread ' varchar default NULL, that is, without character set latin1 collate Latin1_bin, visible character set Latin1 Collat E Latin1_bin is the old version of the VARCHAR BINARY improvements.

Also read more convenient practice, do not change the field properties individually, and as long as the table level of collation for Latin1_bin on the line.
Test:
ALTER TABLE comment_content_1_01 CONVERT to CHARACTER SET latin1 COLLATE latin1_bin
After

To export the table structure again


CREATE TABLE comment_content_1_01 (
content_id Int (one) not NULL auto_increment,
Thread varchar (m) Collate latin1_bin default NULL,
uname varchar (MB) Collate latin1_bin default NULL,
Nick varchar (MB) Collate latin1_bin default NULL,
UID Int (one) unsigned default NULL,
Content Text Collate Latin1_bin,
Post_time datetime default NULL,
POST_IP Int (ten) unsigned default NULL,
The ' status ' enum (' Unaudit ', ' normal ', ' deleted ') collate latin1_bin not NULL default ' Unaudit ',
PRIMARY KEY (content_id)
) Engine=myisam DEFAULT charset=latin1 Collate=latin1_bin;

Even if the original did not set the fields of collate, now are all collate latin1_bin.