What are the differences between utf8_general_ci and utf8_unicode_ci? [duplicate]

by

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

More Related Contents:

  1. Trouble with UTF-8 characters; what I see is not what I stored
  2. How to convert an entire MySQL database characterset and collation to UTF-8?
  3. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  4. Change MySQL default character set to UTF-8 in my.cnf?
  5. How do I see what character set a MySQL database / table / column is?
  6. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  7. How to display UTF-8 characters in phpMyAdmin?
  8. How can I find non-ASCII characters in MySQL?
  9. Save Data in Arabic in MySQL database
  10. How to store unicode in MySQL?
  11. MySQL VARCHAR Lengths and UTF-8
  12. How to detect UTF-8 characters in a Latin1 encoded column – MySQL
  13. Fetching UTF-8 text from MySQL in R returns “????”
  14. How to fix double-encoded UTF8 characters (in an utf-8 table)
  15. Does MySQL Regexp support Unicode matching
  16. MySQL treats ÅÄÖ as AAO?
  17. How to change the default charset of a MySQL table?
  18. MySQL distinction between e and é (e acute) – UNIQUE index
  19. MySQL matching unicode characters with ascii version
  20. MySQL: Querying for unicode entities
  21. MySQL: Get character-set of database or table or column?
  22. ERROR 1115 (42000): Unknown character set: ‘utf8mb4’
  23. Can we use CONCAT and JOIN together in MySQL? [closed]
  24. Retrieving the last record in each group – MySQL
  25. Solution to “subquery returns more than 1 row” error
  26. How to access a RowDataPacket object
  27. MySQL password function
  28. MySQL database with unique fields ignored ending spaces
  29. Set value to NULL in MySQL
  30. MySQL OPTIMIZE all tables?

Leave a Comment