UTF8 in MySQL encodes the Unicode BMP . The Unicode Basic Multilingual Plane, or BMP, is the original 65536 character plane of Unicode 1.0. It was thought to be enough for all scripts in the world. It wasn’t. Unicode was extended, as early as with the 2.0 release, to have more characters than that, in more code planes. There can now be up to 17 Unicode planes.

There are various ways to encode codepoints of Unicode.

Some of them are fixed with. For example, Windows uses internally a 16 bit character encoding, also from the time when people thought that Unicode 1.0 would solve the worlds writing problems. This is not only limited, but also wasteful: When writing western text, every other byte in Windows Unicode text is a null byte.

TLDR

We learned:

• Your programming languages utf8 is called utf8mb4 in MySQL.
• utf8 and utf8mb4 sort differently due to changes in the Unicode Collation Algorithm (UCA).
• Indexes are physically materialized sort orders of column sets. They can become pretty large.
• MySQL never changes sort orders of character sets (collations), even if they are buggy. Instead a new collation with a new name is created.
• That is, because changing a sort order may require dropping and recreating an index, which is expensive if the index is large.
• Sorting things can be done in many different ways, depending on data set size, column size and other considerations.
• Implementation details can make sorting even more complicated.
• MySQL 8 is a worthwhile upgrade.
• Databases manage state so you don’t have to. If you think databases are complicated, consider what you would have to do if the database and your DBA would not be there for you.

utf8mb4In this blog post, we’ll look at options for migrating database charsets to utf8mb4.

Migrating charsets, in my opinion, is one of the most tedious tasks in a DBA’s life. There are so many things involved that can screw up our data, making it work is always hard. Sometimes what seems like a trivial task can become a nightmare very easily, and keeps us working for longer than expected.