91

Possible Duplicate:
What's the difference between utf8_general_ci and utf8_unicode_ci

I've got two options for unicode that look promising for a mysql database.

utf8_general_ci unicode (multilingual), case-insensitive
utf8_unicode_ci unicode (multilingual), case-insensitive

Can you please explain what is the difference between utf8_general_ci and utf8_unicode_ci? What are the effects of choosing one over the other when designing a database?

Cœur
  • 37,241
  • 25
  • 195
  • 267
reconbot
  • 5,138
  • 6
  • 45
  • 63
  • See also http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci – unor Aug 28 '12 at 20:24

2 Answers2

146

utf8_general_ci is a very simple — and on Unicode, very broken — collation, one that gives incorrect results on general Unicode text. What it does is:

  • converts to Unicode normalization form D for canonical decomposition
  • removes any combining characters
  • converts to upper case

This does not work correctly on Unicode, because it does not understand Unicode casing. Unicode casing alone is much more complicated than an ASCII-minded approach can handle. For example:

  • The lowercase of “ẞ” is “ß”, but the uppercase of “ß” is “SS”.
  • There are two lowercase Greek sigmas, but only one uppercase one; consider “Σίσυφος”.
  • Letters like “ø” do not decompose to an “o” plus a diacritic, meaning that it won’t correctly sort.

There are many other subtleties.

  1. utf8_unicode_ci uses the standard Unicode Collation Algorithm, supports so called expansions and ligatures, for example: German letter ß (U+00DF LETTER SHARP S) is sorted near "ss" Letter Œ (U+0152 LATIN CAPITAL LIGATURE OE) is sorted near "OE".

utf8_general_ci does not support expansions/ligatures, it sorts all these letters as single characters, and sometimes in a wrong order.

  1. utf8_unicode_ci is generally more accurate for all scripts. For example, on Cyrillic block: utf8_unicode_ci is fine for all these languages: Russian, Bulgarian, Belarusian, Macedonian, Serbian, and Ukrainian. While utf8_general_ci is fine only for Russian and Bulgarian subset of Cyrillic. Extra letters used in Belarusian, Macedonian, Serbian, and Ukrainian are sorted not well.

The cost of utf8_unicode_ci is that it is a little bit slower than utf8_general_ci. But that’s the price you pay for correctness. Either you can have a fast answer that’s wrong, or a very slightly slower answer that’s right. Your choice. It is very difficult to ever justify giving wrong answers, so it’s best to assume that utf8_general_ci doesn’t exist and to always use utf8_unicode_ci. Well, unless you want wrong answers.

Source: http://forums.mysql.com/read.php?103,187048,188748#msg-188748

törzsmókus
  • 1,799
  • 2
  • 21
  • 28
Timotei
  • 1,909
  • 2
  • 22
  • 31
21

From Unicode Character Sets in the MySQL documentation:

For any Unicode character set, operations performed using the _general_ci collation are faster than those for the _unicode_ci collation. For example, comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci. The reason for this is that utf8_unicode_ci supports mappings such as expansions; that is, when one character compares as equal to combinations of other characters. For example, in German and some other languages “ß” is equal to “ss”. utf8_unicode_ci also supports contractions and ignorable characters. utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

Gumbo
  • 643,351
  • 109
  • 780
  • 844