Difference between utf8mb4_unicode_ci and utf8mb4_unicode_520_ci collations in MariaDB/MySQL?

Question

I logged into MariaDB/MySQL and entered:

SHOW COLLATION;

I see utf8mb4_unicode_ci and utf8mb4_unicode_520_ci among the available collations. What is the difference between these two collations and which should we be using?

Answer 1

http://mysql.rjweb.org/utf8mb4_collations.html shows the differences between those two collations, plus many other collations.

Unicode provides a standard that is evolving with the following numbers:

4.0.0 - utf8mb4_unicode_ci
5.2.0 - utf8mb4_unicode_520_ci
9.0.0 - utf8mb4_0900_ai_ci

It is generally better to use the latest standard that is available.

On that page, for example, are

utf8mb4_unicode_ci: 
 A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å=Ā=ā=Ă=ă=Ą=ą  Aa  ae     az  Æ=æ
utf8mb4_unicode_520_ci and utf8mb4_0900_ai_ci:
 A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å=Ā=ā=Ă=ă=Ą=ą  Aa  ae=Æ=æ az

That shows one difference with "A", namely that "æ" used to come after "az", but is treated as equal to "ae" in 5.2.0 and 9.0.0.

"æ" may be the only change in accented letters among those collations. Even "œ" was consistently equal to "oe"

Answer 2

Well, you can read about the differences in the documentation. I can't tell you what you should be using because every project is different.

10.1.3 Collation Naming Conventions

MySQL collation names follow these conventions:

A collation name starts with the name of the character set with which it is associated, followed by one or more suffixes indicating other collation characteristics. For example, utf8_general_ci and latin_swedish_ci are collations for the utf8 and latin1 character sets, respectively.

A language-specific collation includes a language name. For example, utf8_turkish_ci and utf8_hungarian_ci sort characters for the utf8 character set using the rules of Turkish and Hungarian, respectively.

Case sensitivity for sorting is indicated by _ci (case insensitive), _cs (case sensitive), or _bin (binary; character comparisons are based on character binary code values). For example, latin1_general_ci is case insensitive, latin1_general_cs is case sensitive, and latin1_bin uses binary code values.

For Unicode, collation names may include a version number to indicate the version of the Unicode Collation Algorithm (UCA) on which the collation is based. UCA-based collations without a version number in the name use the version-4.0.0 UCA weight keys. For example:

utf8_unicode_ci (with no version named) is based on UCA 4.0.0 weight keys >(http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt).

utf8_unicode_520_ci is based on UCA 5.2.0 weight keys (http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt).

For Unicode, the xxx_general_mysql500_ci collations preserve the pre-5.1.24 ordering of the original xxx_general_ci collations and permit upgrades for tables created before MySQL 5.1.24. For more information, see Section 2.11.3, “Checking Whether Tables or Indexes Must Be Rebuilt”, and Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”.

Source

Answer 3

I will develop @StuiterSlurf answer and focus on details of utf8mb4_unicode_ci/utf8mb4_unicode_520_ci:

As you can read here (Peter Gulutzan) there is problem with sorting/comparing polish letter "Ł" (L with stroke) (lower case: "ł"; html esc: ł and Ł ) - we have following assumption in coding (same with mb4):

utf8_polish_ci      Ł greater than L and less than M
utf8_unicode_ci     Ł greater than L and less than M
utf8_unicode_520_ci Ł equal to L
utf8_general_ci     Ł greater than Z

In polish language letter Ł is after letter L and before M. And for different coding system you will get different sorting results. No one of this coding is better or worse - it depends of your needs.

Answer 4

To see a bit more discussion of the actual differences, you can go to https://dev.mysql.com/worklog/task/?id=2673 and click "High Level Architecture".