Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between utf8mb4_unicode_ci and utf8mb4_unicode_520_ci collations in MariaDB/MySQL?

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?

like image 754
Flux Avatar asked May 18 '16 18:05

Flux


People also ask

What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?

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.

What is utf8mb4_unicode_ci in MySQL?

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.

Does MariaDB support utf8mb4?

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.

Is utf8mb4_bin case sensitive?

utf8_bin is case-sensitive because it compares the binary values of the character.


2 Answers

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

like image 132
StuiterSlurf Avatar answered Sep 17 '22 19:09

StuiterSlurf


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.

like image 43
Kamil Kiełczewski Avatar answered Sep 19 '22 19:09

Kamil Kiełczewski