Additional languages
MySQL 3.23, 4.0, 4.1 Reference Manual :: 10 Character Set Support :: 10.5 Collation Issues :: 10.5.6 An Example of the Effect of Collation
« 10.5.5 Collations Must Be for the Right Character Set
10.6 Operations Affected by Character Set Support »
Section Navigation      [Toggle]

10.5.6. An Example of the Effect of Collation

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

Muffler
Müller
MX Systems
MySQL

Suppose also that the column values are retrieved using the following statement: 

SELECT X FROM T ORDER BY X COLLATE collation_name;

The following table shows the resulting order of the values if we use ORDER BY with different collations:

latin1_swedish_ci latin1_german1_ci latin1_german2_ci
Muffler Muffler Müller
MX Systems Müller Muffler
Müller MX Systems MX Systems
MySQL MySQL MySQL

The character that causes the different sort orders in this example is the U with two dots over it (ü), which the Germans call “U-umlaut.”

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

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

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