Here's the situation:

  1. I have come across several posts for "utf8_bin vs utf8_general_ci".
  2. Similarly, "utf8_general_cs vs utf8_general_ci".

But I can't find a post for "utf8_bin vs utf8_general_cs". So, are they the same? If not, what's the difference between them?

Note: I have checked that utf8_general_cs is not available in default MySQL. May I know why?

  • The binary Collation Compared to _bin Collations. utf8_bin collation compare is based on binary codes, whereas utf8_general_ci compare is based on a character compare table. – Akina Sep 13 at 7:09
  • @Akina, I have updated my question, please update your comment accordingly – Dinesh Kumar Sep 13 at 7:54
  • My comment is still valid. Purify what is unclear for you yet... – Akina Sep 13 at 8:31
up vote 3 down vote accepted

The notion that binary collations are the same as case-sensitive collations is, unfortunately, an extremely common one.

However, they are very much not functionally equivalent. There are four areas where behavioral differences can be seen (at least four that I am aware of):

  1. Combining Characters

    Consider having a lower-case ü ("u" with diaeresis) and an upper-case Ü ("U" with diaeresis). Both types of collations will be able to distinguish them.

    Now, consider having an upper-case U and a ̈ (combining diaeresis). When the combining character is placed after the non-accented U, you get . Visually it is the same as the single Ü ("U" with diaeresis). And a case-sensitive (and even accent-sensitive) collation will seem them as being the same, even though one is a single code point and the other is a combination of two code points. But a binary collation cannot compare them as being equal since they are not the same code points (or even the same number of code points).

  2. Fullwidth Characters

    A collation that is case-sensitive but width-insensitive would be able to equate =o= and =o=. However, a binary collation cannot seem them as equal since they are different code points.

  3. Accent Insensitivity

    A collation that is case-sensitive but accent-insensitive would be able to equate o and ô. However, a binary collation cannot seem them as equal since they are different code points.

  4. Sorting

    A case-sensitive collation will sort ~ before a and A after it. But, a binary collation will sort those same characters as: A then a then ~.

All of this is documented in the following post of mine:

No, Binary Collations are not Case-Sensitive

It is presented in terms of Microsoft SQL Server, but the behavior is based on rules defined by the Unicode standard, which should be the same across RDBMSs, languages, OSs, etc (with minor variation due to which version of the Unicode Standard is being used, and who did the implementation since Unicode is only a standard and has been implemented with slight variation across vendors).


I have checked that utf8_general_cs is not available in default MySQL. May I know why?

My guess is that the "general" collations are obsolete and have been superseded by newer "unicode" and culture-specific collations. The documentation (half-way down the page, paragraph starting with "For any Unicode character set,") even states:

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.

The "unicode" collations are probably the default sort weights and collation rules. The culture-specific collations tailor the weights and rules to be correct for that culture (when the default is incorrect). For more info on why there are different collations, please see my answer to the following S.O. question:

What is the point of COLLATIONS for nvarchar (Unicode) columns?

  • can I know why utf8_general_cs is not available in default mysql build, (seems like many _cs collations not available in mysql by default). Another doubt, if utf8_general_ci/ utf8_general_cs is capable of handling wide range of languages, then why there are language specific collations. I guess, they are optimized for particular language. Am I right? – Dinesh Kumar Sep 13 at 15:24
  • 1
    @DineshKumar I have finished added examples to the answer. I have also answered your questions at the bottom of my answer. – Solomon Rutzky Sep 13 at 16:47
  • @SolomonRutzky - Check the available collations in the latest MySQL 8.0.xx – Rick James Oct 5 at 19:48
  • Language-specific variations sort of jump out in this (MySQL only). – Rick James Oct 5 at 19:50

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.