If you require German DIN-2 (phone book) ordering, use the utf8_german2_ci  collation, which compares the following sets of characters equal: ˜ = ˘ = AE 

Ö = Œ  = OE Ü = UE ß = ss 

utf8_german2_ci  is similar to latin1_german2_ci , but the latter does not compare ˘  equal to AE or Œ  equal to OE . There is no utf8_german_ci  corresponding to latin1_german_ci  for German dictionary order because utf8_general_ci  suffices. 

## Character Collating Weights 

A character’s collating weight is determined as follows: 

•For all Unicode collations except the _bin  (binary) collations, MySQL performs a table lookup to find a character’s collating weight. 

•For _bin  collations except utf8mb4_0900_bin , the weight is based on the code point, possibly with leading zero bytes added. 

•For utf8mb4_0900_bin , the weight is the utf8mb4  encoding bytes. The sort order is the same as for utf8mb4_bin , but much faster. 

Collating weights can be displayed using the WEIGHT_STRING()  function. (See Section 12.8, “String Functions and Operators” .) If a collation uses a weight lookup table, but a character is not in the table (for example, because it is a “new”  character), collating weight determination becomes more complex: 

•For BMP characters in general collations ( xxx _general_ci ), the weight is the code point. 

•For BMP characters in UCA collations (for example, xxx _unicode_ci  and language-specific collations), the following algorithm applies: 

if (code >= 0x3400 && code <= 0x4DB5) base= 0xFB80; /* CJK Ideograph Extension */ else if (code >= 0x4E00 && code <= 0x9FA5) base= 0xFB40; /* CJK Ideograph */ else base= 0xFBC0; /* All other characters */ aaaa= base + (code >> 15); bbbb= (code & 0x7FFF) | 0x8000; 

The result is a sequence of two collating elements, aaaa  followed by bbbb . For example: 

mysql> SELECT HEX(WEIGHT_STRING(_ucs2 0x04CF COLLATE ucs2_unicode_ci)); +----------------------------------------------------------+ | HEX(WEIGHT_STRING(_ucs2 0x04CF COLLATE ucs2_unicode_ci)) | +----------------------------------------------------------+ | FBC084CF | +----------------------------------------------------------+ 

Thus, U+04cf CYRILLIC SMALL LETTER PALOCHKA  is, with all UCA 4.0.0 collations, greater than U +04c0 CYRILLIC LETTER PALOCHKA . With UCA 5.2.0 collations, all palochkas sort together. 

•For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER . For supplementary characters in UCA 4.0.0 collations, their collating weight is 0xfffd . That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters. 

An example with Deseret characters and COUNT(DISTINCT) : 

