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.

UnicodeDecodeError: ‘ascii’ codec can’t decode byte 0xd1 in position 1: ordinal not in range(128) (Why is this so hard??)
One of the toughest things to get right in a Python program is Unicode handling. If you’re reading this, you’re probably in the middle of discovering this the hard way.

The main reasons Unicode handling is difficult in Python is because the existing terminology is confusing, and because many cases which could be problematic are handled transparently. This prevents many people from ever having to learn what’s really going on, until suddenly they run into a brick wall when they want to handle data that contains characters outside the ASCII character set.