3 ms·

> MySQL's 'utf8' encoding, perversely, supports only a subset of utf8 and will break if you try to write an emoji. Almost every programming platform makes the

> MySQL's 'utf8' encoding, perversely, supports only a subset of utf8 and will break if you try to write an emoji. Almost every programming platform makes the same mistake. Characters outside of the basic multilingual plane (i.e., characters taking more than two bytes to store) will break certain string functions. It's due to using (fixed length) ucs2 for in-memory storage, instead of variable-length utf8. Imagine, for example, if you tried to replace a one-byte ascii character at a given index with a 4 byte cuneiform character - you would need to reallocate, copy+transpose the entire string buffer. Every string function would need to take variable byte length into account and would need to traverse the entire string with a state machine to do any operation, plus maybe a full reallocate+strcpy. So instead we can make every character a fixed byte length. We could make every character 4 bytes in memory, but since strings are 99% ascii that seems wasteful, so instead let's just make every character a fixed-length 2 bytes (ucs2), that should cover 99.9% of it.

dcposch 9y ago
Well, sort of. Java and Javascript use 2-byte strings in memory, yet both can represent an emoji just fine. (They do so via a hack that uses multiple indices in a String to represent a single character. If you want to go down the rabbit hole of how much this sucks, check out the MDN pages for charCodeAt vs codePointAt ...) Modern languages like Go just represent strings as UTF8 in memory. This has lots of advantages: - Most strings are mostly 1-byte-per-character, saving memory - Full Unicode support. - Faster IO, since you don't have to re-encode UTF8 strings to/from the network or disk. MySQL did something weird and enormously stupid with its `utf8` encoding. It supports up-to-3-byte-per-character UTF8. This is idiosyncratic, nobody else does this. It supports some emoji and some Chinese characters but not others. Fortunately, you don't have to worry about it or learn about it. When using MySQL, just always use utf8mb4 ; never use utf8.
- nerfhammer 9y ago
  huh, I had remembered unicode as being broken in javascript, python and mysql in the same way. I hadn't remembered three-byte utf8. mysql also is the only platform that I can think of that supports three byte integers.

dcposch 9y ago

Well, sort of. Java and Javascript use 2-byte strings in memory, yet both can represent an emoji just fine. (They do so via a hack that uses multiple indices in a String to represent a single character. If you want to go down the rabbit hole of how much this sucks, check out the MDN pages for charCodeAt vs codePointAt ...) Modern languages like Go just represent strings as UTF8 in memory. This has lots of advantages: - Most strings are mostly 1-byte-per-character, saving memory - Full Unicode support. - Faster IO, since you don't have to re-encode UTF8 strings to/from the network or disk. MySQL did something weird and enormously stupid with its `utf8` encoding. It supports up-to-3-byte-per-character UTF8. This is idiosyncratic, nobody else does this. It supports some emoji and some Chinese characters but not others. Fortunately, you don't have to worry about it or learn about it. When using MySQL, just always use utf8mb4 ; never use utf8.

nerfhammer 9y ago
huh, I had remembered unicode as being broken in javascript, python and mysql in the same way. I hadn't remembered three-byte utf8. mysql also is the only platform that I can think of that supports three byte integers.