Analysis of problems caused by collation rules of Mysql _mysql

Source: Internet
Author: User
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 all try to use the corresponding bin type of collation rules, so it is not easy to make mistakes
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.