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?
Key differencesutf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages. utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed.
Next in the list of "better" collations for general use (as opposed to Spanish-specific, etc) is utf8mb4_unicode_ci . This matches the Unicode Collation Algorithm version 4.0, written several years ago.
MariaDB 10.6. 1 changed the utf8 character set by default to be an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode system variable.
utf8_bin is case-sensitive because it compares the binary values of the character.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With