9

But I've been reading up on the importance of MySQL Collation and what I have learned so far regarding compatibility and accuracy is these 4 seem to be my best bet.

  • utf8_unicode_ci
  • utf8_unicode_520_ci
  • utf8mb4_unicode_ci
  • utf8mb4_unicode_520_ci

From my understanding uft8mb4 would be good for character with mutli language (character) support (japanese for example). uf8 only supports 3 bytes while uf8mb4 supports 4 bytes. So it sounds like the obvious choice would be uf8mb4, but the catch seems to be that you have a length limit (Damn it! I want my cake and eat it too), which is a little concern (I think).

Then you take into account about the 520 standard; which offers more, from what little I could find on it. But that is of course the issue, I could find very little about it. Only that people say it's an improvement, yet being very vague on how that is.

I do want the most I can get with as few restrictions as possible... I figured someone here may know a thing or two. The official MySQL site wasn't as informative as I had hoped it was.

Of the 4 which would offer the most compatibility, accuracy, and the most storage length? Also what truly is the big difference between the 502 standard?

8

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. A collation name such as utf8_unicode_520_ci is based on UCA 5.2.0 weight keys.

See https://dev.mysql.com/doc/refman/5.6/en/charset-collation-names.html.

  • To further explain on this: the collation will not modify the encoding used (what MySQL wrongly calls character set or charset), so utf8 will provide you 3-byte (non-standard) UTF-8 and utf8mb4 will provide you 4-byte UTF-8. – jynus Sep 18 '14 at 14:15
  • 1
    So in simple English what is the difference between UCA 4.0.0 and UCA 5.2.0 other than one is newer. What would the advantage or disadvantage be? – Adam Sep 18 '14 at 16:12
  • 3
    It affects sorting. If you absolutely need to sort against a more recent (but still outdated) version of the Unicode standard, then you may want to use 520_ci. For my websites I couldn’t care less. – Mathias Bynens Sep 18 '14 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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