mysqlunicode

What's the difference between utf8_unicode_ci and utf8mb4_0900_ai_ci


What is the difference between utf8mb4_0900_ai_ci and utf8_unicode_ci database text coding in mysql (especially in terms of performance) ?

Update:

There are similar differences between utf8mb4_unicode_ci and utf8mb4_0900_ai_ci?


Solution

    • The encoding is the same. That is, the bytes look the same.
    • The character set is different. utf8mb4 has more characters.
    • The collation (how comparisions are done) is different.
    • The perfomance is different, but it rarely matters.

    utf8_unicode_ci implies the CHARACTER SET utf8, which includes only the 1-, 2-, and 3-byte UTF-8 characters. Hence it excludes most Emoji and some Chinese characters.

    utf8mb4_unicode_ci implies the CHARACTER SET utf8mb4 is the corresponding COLLATION for the 4-byte CHARACTER SET utf8mb4.

    The Unicode organization has been evolving the specification over the years. Here are the mappings from its "versions" to MySQL Collations:

    4.0   _unicode_
    5.2.0 _unicode_520_ (Unicode 2009; MySQL GA 5.6 2013)
    9.0   _0900_
    14.0  _uca1400_ai_ci etc.  as/ai and cs/ci (MariaDB-10.10, not MySQL)
    

    Most of the differences will be in areas that most people never encounter. One example: At some point, a change allowed Emoji to be distinguished and ordered in some manner.

    The suffix (MySQL doc):

    _bin      -- just compare the bits; don't consider case folding, accents, etc
    _ci       -- explicitly case insensitive (A=a) and implicitly accent insensitive (a=á)
    _ai_ci    -- explicitly case insensitive and accent insensitive
    _as (etc) -- accent-sensitive (etc)
    

    Performance:

    _bin         -- simple, fast
    _general_ci  -- fails to compare multiple letters; eg ss=ß, so somewhat fast
    ...          -- slower
    _900_        -- (8.0) much faster because of a rewrite
    

    However: The speed of collation is usually the least of the performance issues in queries. INDEXes, JOINs, subqueries, table scans, etc are much more critical to performance.