content_image/828741.jpg
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. 

<img src='content_image/828741.jpg'>

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. 

Suppose that you have three tables that differ only by the character set and collation used: 

mysql> SET NAMES utf8; mysql> CREATE TABLE german1 ( c CHAR(10) ) CHARACTER SET latin1 COLLATE latin1_german1_ci; mysql> CREATE TABLE german2 ( c CHAR(10) ) CHARACTER SET latin1 COLLATE latin1_german2_ci; mysql> CREATE TABLE germanutf8 ( c CHAR(10) ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Each table contains two records: 

mysql> INSERT INTO german1 VALUES (’Bar’), (’Bär’); mysql> INSERT INTO german2 VALUES (’Bar’), (’Bär’); mysql> INSERT INTO germanutf8 VALUES (’Bar’), (’Bär’); 

Two of the above collations have an A = ˜  equality, and one has no such equality ( latin1_german2_ci ). For that reason, you’ll get these results in comparisons: 

mysql> SELECT * FROM german1 WHERE c = ’Bär’; +------+ | c | +------+ | Bar | | Bär | 

