8.3.13 An example of the Effect of Collation

Suppose column X in table T has these latin1 column values:

Muffler
Müller
MX Systems
MySQL

And suppose that the column values are retrieved using the following statement:

SELECT X FROM T ORDER BY X COLLATE collation_name;

The resulting order of the values for different collations is shown in this table:

The table is an example that shows what the effect would be if we used different collations in an ORDER BY clause. The character that's causing the trouble in this example is the U with two dots over it, which the Germans call U-umlaut, but we'll call it U-diaeresis.

The first column shows the result of the SELECT using the Swedish/Finnish collating rule, which says that U-diaeresis sorts with Y.

The second column shows the result of the SELECT using the German DIN-1 rule, which says that U-diaeresis sorts with U.

The third column shows the result of the SELECT using the German DIN-2 rule, which says that U-diaeresis sorts with UE.

Three different collations, three different results. That's what MySQL is here to handle. By using the appropriate collation, you can choose the sort order you want.

User Comments

Add your own comment.