9 ms·

Database character sets and collations explained – why utf8 is not UTF-8

104 points by flokoe 5y ago

petee 5y ago
The 2002 diff got me interested in the context of why that change was made, which lead me to this 2006 bug which I think might shed light on why '3' was chosen: "The UTF-8 encoding is a variable byte length encoding. The ASCII subrange of characters is one byte long, alphabetic writings 2 bytes long, Far Eastern writings 3 bytes long per character (and some rare historical writings are mapped using 4 bytes)." https://bugs.mysql.com/bug.php?id=18560#c61151 https://bugs.mysql.com/bug.php?id=18560#c61151 UTF-8 is still something I'm learning the details about, but it looks like back in 2002 they simply didn't see the need for the supplemental plane characters, ie, playing cards, arrows, or ancient Greek numbers. It might be short sighted but we shouldn't judge so far in the future, recalling that emoji wasn't even in Unicode until 2010.
- deleted 5y ago
  [deleted]
- Diggsey 5y ago
  UTF-8 used to support up to 6 bytes to encode a unicode scalar value. Only later was the unicode range restricted such that a max of 4 bytes was required to encode any scalar value using UTF-8. It looks like when UTF-8 was originally added to MySQL it still supported up to 6 bytes per "character", so the decision to restrict it was actually made by both MySQL and RFC 3629 - unfortunately they chose different limits :(
  - tialaramex 5y ago
    What happened is Unicode committed forever to a range zero to U+10FFFF and RFC 3629 follows that. You don't need 5 or 6 byte UTF-8 to express this range, and so they're forbidden. So RFC 3629 isn't choosing arbitrarily, the choice in MySQL isn't entirely arbitrary either, it's just wrong (and should not have been named "utf8"). You could use the 3-byte UTF-8-like encoding MySQL has to do CESU-8 but that's not UTF-8. If MySQL wanted to do CESU-8 they could have called the type cesu8, giving away what's actually going on and necessitating a lot of extra work by MySQL. They did not choose to do that.
    - Diggsey 5y ago
      Right, but my point was that the "U+10FFFF" limit hadn't been decided yet at the point where MySQL first added the "UTF-8" encoding, and it was added later, so it may not have been clear to them that UTF-8 would ever be a "fixed target" so to speak. In retrospect we can see that UTF-8 has been and will continue to be static, since it's become so universally adopted.
- tialaramex 5y ago
  If it was called just "string" or something and then it quietly explained somewhere that it's actually only the BMP and not all of Unicode that's not so surprising, but they deliberately called it UTF-8 even though it just isn't. Imagine if you bought a USB mouse and then you discovered, oh, they didn't bother implementing USB, this is just a PS/2 mouse with the USB connector. Works fine so long as you use the supplied PS/2 "adaptor" but it isn't actually USB even though it says it is on the box. That mouse is defective. MySQL is defective as supplied. Which won't come as a surprise to many experienced MySQL developers. I actually rather like MySQL, but it's like bash or Excel, it is a good tool for quick estimates or prototyping, and a poor tool for actual engineering. For the cost of all the work needed to get reliable high quality results with MySQL you could have used a different database where the baseline effort to get it working was higher but what you get is more solid, and "utf8mb4" is an example of that extra work in MySQL.
  - petee 5y ago
    While searching yesterday, I recall the mysql documentation did specify this this quirk. As I gather, this change happened prior to or at the change to 4 char max utf; was supplemental plane support required at that time? 3 char is still utf8, just not fully supported, but I really do not know. Not to defend, just saying things were new and fluid back then, though it should have been fixed properly alot sooner.
    - tialaramex 5y ago
      In Modern MySQL the documentation does - if you look - tell you that utf8mb4 exists to actually do UTF-8 and if you think to check it mentions that their utf8 is not in fact UTF-8 but only "utf8mb3". But fifteen years ago most of that documentation doesn't exist, and utf8mb4 doesn't exist, and if you either read the MySQL documentation carefully and realise "utf8" isn't UTF-8, and only encodes the Basic Multilingual Plane, you are stuck anyway, MySQL doesn't have a sensible way forward for you. I would argue that by 2002 when this bug (let's call it what it is) was introduced, the writing was on the wall, UCS-2 is dead, which means the "it's just easier" justification for choosing 16-bit is also dead, so UTF-8 is the natural choice for software that isn't meanwhile committed to a 16-bit encoding anyway (as Java and Windows are). That makes shipping UTF-8 a good idea, but it makes choosing utf8mb3 a really weird idea that already seems certain to cause pain.
- chmod600 5y ago
  What we can judge harshly is that it accepted the 4-byte sequences and failed silently, corrupting data. A sane system would reject the 4-byte sequences so you could try again without the emojis. But if users got errors, they might investigate, and it would slow down the rapid growth mysql was aiming for. So they just decided not to throw errors.
  - petee 5y ago
    I agree, although the change was prior to 4-byte utf (2002 vs 2003) the same issue would have manifested with 6 byte sequences. Very curious. It would be really interesting to see a postmortem by mysql if ever possible, because the more I think about, 3 is such an odd choice (pun), and people usually do most things for a reason haha, its either lost to time, or a brainfart off-by-one.
Karellen 5y ago
* "Why MySQL/MariaDB's 'utf8' is not UTF-8"
- deleted 5y ago
  [deleted]
- flokoe 5y ago
  I changed the title to be more specific. Thanks!
  - koolba 5y ago
    It really should have MySQL in the title as it’s not a generic database abnormality. It’s specific. To MySQL (and by extension MariaDB).
somat 5y ago
What I am curious about is why couldn't they fix it? At one point the change was made from 6 to 3 bytes, I expect this made the on disk structure incompatible with prior versions. why were they able to make this change but unable to make a later change say from 3 to 4 bytes. I am not as familiar with mysql but note that postgres major versions are not disk compatible with each other(you have to dump and restore). I expect this is the exact reason. you want a system where it is possible to make changes. see also: the drama around linux abi changes vs openbsd abi changes. To meme off of raul julia: openbsd to linux: "For you, the day your abi changed was the most important day of your life. But for us, it was Tuesday."
- evanelias 5y ago
  VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case. But if these columns were silently/automatically converted to utf8mb4 upon upgrading MySQL, they would now need to store max 4N bytes, which (with a high N) can exceed internal limits such as maximum length of an index key. IIRC, there were additional problems in older versions of MySQL, situations where sort buffers were sized to a fixed length equal to the value's worst-case size or something like that. So sorting a large number of utf8mb4 values would use a lot more memory than utf8mb3 values (again, iirc, I might be wrong on this). So the safer and more backwards-compatible approach was to introduce utf8mb4 as a new separate charset, and allow users to choose. MySQL 8 is now transitioning towards deprecating utf8mb3, and will finally make the utf8 alias point to utf8mb4 sometime in the near future. That said, there are still a bunch of unpleasant uses of utf8mb3 internally in things like information_schema. I develop schema management tooling and recently lost a week to writing work-arounds for some of the more obscure ones, see https://github.com/skeema/skeema/commit/bf38edb https://github.com/skeema/skeema/commit/bf38edb :)
  - lalaithion 5y ago
    What's the problem with allowing VARCHAR(N) store a max of 3N bytes worst-case, but still allow the 4-byte characters to be present in the string? Sure, it means that you can only store 0.75 * N emojis in a VARCHAR(N), but that seems less bad than the alternative of silently corrupting data.
marginalia_nu 5y ago
> Regarding the different collation algorithms: The unicode variants should be slower than the general variant due to the more complex algorithms, but this was years ago when computers were much weaker than now. With modern hardware, the speed gain should be hardly notable. ... > Furthermore, we should prefer accuracy over speed. There is no need to use an old and quirky collation like utf8mb4_general_ci with modern hardware. This is kind of oversimplified and depends a lot on your use case. Performance can absolutely matter even with modern hardware. There is no one-size-fits-all collation that is best in all scenarios, if there was, we really wouldn't need the option to choose. If you have a thousand rows in your table, sure, use whatever. If you have a billion rows in your table, things are different and you may not be able to afford leaving performance on the table.
- flokoe 5y ago
  Thanks for your critique! I admit it may be a simple view, but I still believe it is the best choice for most of us. However, if you are in a position where you have to handle such an extensive dataset, you probably know about this detail and can decide if this is a suitable option or not.
TedShiller 5y ago
aka why I switched to Postgres
- dmz73 5y ago
  Does posgres support case insensitive collation on standard varcharrs? Last time I used it that was not an option so it made everything a lot more complicated. Postgrges also did not support hints for queries so tweaking performance was more complicated. Both MySQL and postgres performed much worse with complex queries than MSSQL even after tweaking.
acabal 5y ago
utf8 vs utf8mb4 bit me in one of the most frustrating bugs I've ever had to debug. At Scribophile members can write critiques for people's writing, with comments inserted inline. The underlying software dates back to the PHP5 days when MySQL only had the utf8 option. Everything was working fine for years, when all of a sudden users started complaining that from time to time, they would submit a critique and it would be mysteriously cut off at random places. The problem was very intermittent, didn't happen very often, and there was no seeming rhyme or reason to it. But when it did happen, it was catastrophic because members would lose hours of work, seemingly at random! All kinds of testing scaffolding and logging was put in place to try to find the problem with no luck. Then, after quite some time, we realized what the problem was. At that time, emoji keyboards were brand new; most phones/tablets had limited support, and people didn't yet use phones and tablets for everything like they do now. But they were out there. Some users who were using these new emoji keyboards were inserting emoji smiley faces as they were writing critiques. In Scribophile's web interface, everything looked fine; but when the user submitted the critique, MySQL tried to insert a multibyte Unicode character into a regular utf8 field, and SILENTLY threw that character and all of the data after it away!! Boy were we upset at MySQL about that one. But at least we figured it out!
- BoxOfRain 5y ago
  It's stories like this that stop me complaining online about a long day of futile debugging!
- gpderetta 5y ago
  You probably mean outside of the basic multilingual plane. Any non-ascii character is multibyte in utf8.
- webtopf 5y ago
  I had the pleasure of finding that same behaviour in an application around 2015. When someone commented on a food order "Extra cheese please :folded hands: but no shrimp, I'm allergic." But one thing I haven't found out is why some emojis did go through. The basic ones like a simple :smile: it seemed to me. Could it be that some only need 3 bytes and when more and more emojis got released, they went into 4 bytes? Edit: HN stripped the emojis from my comment... I would put a rolling eyes emoji here if I could.
avgcorrection 5y ago
> Even though 128 characters are enough to cover the English language and a few basic symbols You can communicate just fine in English using ASCII. But plenty of English words use non-ASCII letters.
- DemocracyFTW 5y ago
  Æsthetics do play a rôle here
- kingcharles 5y ago
  To think otherwise would be naïve.
deleted 5y ago
[deleted]
captainmuon 5y ago
I understand why this would affect collation and indices, but why can't I even store the four byte characters in an utf8mb3 table? The SQL parser and printer seems to deal with the characters fine, because it is independent from the table. I would assume it would just stuff the UTF-8 bytes in there as they are. Is it trying to validate them? Or is it using UTF-32 (or a truncated version) internally and saving everything as 3-4 bytes?
tzs 5y ago
> For example utf8mb4_general_ci does not knwo how to sort s and the German character ß (“sharp S”) in contrast to utf8mb4_unicode_520_ci which sorts ß just fine If I use utf8mb4_general_ci now, is it guaranteed to produce the same results on a given dataset each time, and to keep producing those same results under future versions of MariaDB? Or is using it on data that contains characters for which it does not define a sort order like using undefined behavior in C, and it is free to produce different results from run to run and from version to version?
tpoacher 5y ago
Is it me or does the author seem to be using utf8 and unicode interchangeably (at least in the intro)?
orthoxerox 5y ago
I wonder who that "bar@bar.mysql.r18.ru" was.
alserio 5y ago
> One last concern is the size stored on the disk because UTF-8 supports four bytes per character. But since UTF-8 is a variable-width character encoding, it only uses as many bytes as needed. For example, the first 128 characters like ASCII only use one byte I'm not sure this is true if you use CHAR instead of VARCHAR. Moreover if I'm not misremembering indexes still store the full fixed size 3 or 4 byte representation and index size might matter. However, I'll still use mb4 to avoid the fun times other have already described in the comments
chrismorgan 5y ago
utf8mb3 allows representing up to U+FFFF, but not U+10000–U+1FFFF. It’s like UCS-2 is to UTF-16, only without any actual technical rationale.
JoeAltmaier 5y ago
Speaking of expensive screwups, one of the most expensive schematic errors ever was the routing of the interrupts on the original IBM PC. It put the serial interrupt on the lowest-priority interrupt pin, instead of the highest. This spawned a trillion-dollar 'communications card' industry that was arguably not necessary. Certainly not necessary to the degree it grew. Interrupts should be prioritized by latency. Instead, the designer prioritized them by, I don't know, their most favorite to least favorite? E.g. the disk transfer completion interrupt was very high, even though it literally has infinite latency (nothing irrecoverable is going to happen if it's delayed indefinitely). If I had a time-travel machine that only allowed a 1 centimeter window into the past, I would open a window to that original schematic and swap the disk and comm interrupt lines. That would have changed silicon valley history.

104 points by flokoe 5y ago

The 2002 diff got me interested in the context of why that change was made, which lead me to this 2006 bug which I think might shed light on why '3' was chosen: "The UTF-8 encoding is a variable byte length encoding. The ASCII subrange of characters is one byte long, alphabetic writings 2 bytes long, Far Eastern writings 3 bytes long per character (and some rare historical writings are mapped using 4 bytes)." https://bugs.mysql.com/bug.php?id=18560#c61151 https://bugs.mysql.com/bug.php?id=18560#c61151 UTF-8 is still something I'm learning the details about, but it looks like back in 2002 they simply didn't see the need for the supplemental plane characters, ie, playing cards, arrows, or ancient Greek numbers. It might be short sighted but we shouldn't judge so far in the future, recalling that emoji wasn't even in Unicode until 2010.

deleted 5y ago
[deleted]
Diggsey 5y ago
UTF-8 used to support up to 6 bytes to encode a unicode scalar value. Only later was the unicode range restricted such that a max of 4 bytes was required to encode any scalar value using UTF-8. It looks like when UTF-8 was originally added to MySQL it still supported up to 6 bytes per "character", so the decision to restrict it was actually made by both MySQL and RFC 3629 - unfortunately they chose different limits :(
- tialaramex 5y ago
  What happened is Unicode committed forever to a range zero to U+10FFFF and RFC 3629 follows that. You don't need 5 or 6 byte UTF-8 to express this range, and so they're forbidden. So RFC 3629 isn't choosing arbitrarily, the choice in MySQL isn't entirely arbitrary either, it's just wrong (and should not have been named "utf8"). You could use the 3-byte UTF-8-like encoding MySQL has to do CESU-8 but that's not UTF-8. If MySQL wanted to do CESU-8 they could have called the type cesu8, giving away what's actually going on and necessitating a lot of extra work by MySQL. They did not choose to do that.
  - Diggsey 5y ago
    Right, but my point was that the "U+10FFFF" limit hadn't been decided yet at the point where MySQL first added the "UTF-8" encoding, and it was added later, so it may not have been clear to them that UTF-8 would ever be a "fixed target" so to speak. In retrospect we can see that UTF-8 has been and will continue to be static, since it's become so universally adopted.
tialaramex 5y ago
If it was called just "string" or something and then it quietly explained somewhere that it's actually only the BMP and not all of Unicode that's not so surprising, but they deliberately called it UTF-8 even though it just isn't. Imagine if you bought a USB mouse and then you discovered, oh, they didn't bother implementing USB, this is just a PS/2 mouse with the USB connector. Works fine so long as you use the supplied PS/2 "adaptor" but it isn't actually USB even though it says it is on the box. That mouse is defective. MySQL is defective as supplied. Which won't come as a surprise to many experienced MySQL developers. I actually rather like MySQL, but it's like bash or Excel, it is a good tool for quick estimates or prototyping, and a poor tool for actual engineering. For the cost of all the work needed to get reliable high quality results with MySQL you could have used a different database where the baseline effort to get it working was higher but what you get is more solid, and "utf8mb4" is an example of that extra work in MySQL.
- petee 5y ago
  While searching yesterday, I recall the mysql documentation did specify this this quirk. As I gather, this change happened prior to or at the change to 4 char max utf; was supplemental plane support required at that time? 3 char is still utf8, just not fully supported, but I really do not know. Not to defend, just saying things were new and fluid back then, though it should have been fixed properly alot sooner.
  - tialaramex 5y ago
    In Modern MySQL the documentation does - if you look - tell you that utf8mb4 exists to actually do UTF-8 and if you think to check it mentions that their utf8 is not in fact UTF-8 but only "utf8mb3". But fifteen years ago most of that documentation doesn't exist, and utf8mb4 doesn't exist, and if you either read the MySQL documentation carefully and realise "utf8" isn't UTF-8, and only encodes the Basic Multilingual Plane, you are stuck anyway, MySQL doesn't have a sensible way forward for you. I would argue that by 2002 when this bug (let's call it what it is) was introduced, the writing was on the wall, UCS-2 is dead, which means the "it's just easier" justification for choosing 16-bit is also dead, so UTF-8 is the natural choice for software that isn't meanwhile committed to a 16-bit encoding anyway (as Java and Windows are). That makes shipping UTF-8 a good idea, but it makes choosing utf8mb3 a really weird idea that already seems certain to cause pain.
chmod600 5y ago
What we can judge harshly is that it accepted the 4-byte sequences and failed silently, corrupting data. A sane system would reject the 4-byte sequences so you could try again without the emojis. But if users got errors, they might investigate, and it would slow down the rapid growth mysql was aiming for. So they just decided not to throw errors.
- petee 5y ago
  I agree, although the change was prior to 4-byte utf (2002 vs 2003) the same issue would have manifested with 6 byte sequences. Very curious. It would be really interesting to see a postmortem by mysql if ever possible, because the more I think about, 3 is such an odd choice (pun), and people usually do most things for a reason haha, its either lost to time, or a brainfart off-by-one.

Karellen 5y ago

* "Why MySQL/MariaDB's 'utf8' is not UTF-8"

deleted 5y ago
[deleted]
flokoe 5y ago
I changed the title to be more specific. Thanks!
- koolba 5y ago
  It really should have MySQL in the title as it’s not a generic database abnormality. It’s specific. To MySQL (and by extension MariaDB).

somat 5y ago

What I am curious about is why couldn't they fix it? At one point the change was made from 6 to 3 bytes, I expect this made the on disk structure incompatible with prior versions. why were they able to make this change but unable to make a later change say from 3 to 4 bytes. I am not as familiar with mysql but note that postgres major versions are not disk compatible with each other(you have to dump and restore). I expect this is the exact reason. you want a system where it is possible to make changes. see also: the drama around linux abi changes vs openbsd abi changes. To meme off of raul julia: openbsd to linux: "For you, the day your abi changed was the most important day of your life. But for us, it was Tuesday."

evanelias 5y ago
VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case. But if these columns were silently/automatically converted to utf8mb4 upon upgrading MySQL, they would now need to store max 4N bytes, which (with a high N) can exceed internal limits such as maximum length of an index key. IIRC, there were additional problems in older versions of MySQL, situations where sort buffers were sized to a fixed length equal to the value's worst-case size or something like that. So sorting a large number of utf8mb4 values would use a lot more memory than utf8mb3 values (again, iirc, I might be wrong on this). So the safer and more backwards-compatible approach was to introduce utf8mb4 as a new separate charset, and allow users to choose. MySQL 8 is now transitioning towards deprecating utf8mb3, and will finally make the utf8 alias point to utf8mb4 sometime in the near future. That said, there are still a bunch of unpleasant uses of utf8mb3 internally in things like information_schema. I develop schema management tooling and recently lost a week to writing work-arounds for some of the more obscure ones, see https://github.com/skeema/skeema/commit/bf38edb https://github.com/skeema/skeema/commit/bf38edb :)
- lalaithion 5y ago
  What's the problem with allowing VARCHAR(N) store a max of 3N bytes worst-case, but still allow the 4-byte characters to be present in the string? Sure, it means that you can only store 0.75 * N emojis in a VARCHAR(N), but that seems less bad than the alternative of silently corrupting data.

marginalia_nu 5y ago

> Regarding the different collation algorithms: The unicode variants should be slower than the general variant due to the more complex algorithms, but this was years ago when computers were much weaker than now. With modern hardware, the speed gain should be hardly notable. ... > Furthermore, we should prefer accuracy over speed. There is no need to use an old and quirky collation like utf8mb4_general_ci with modern hardware. This is kind of oversimplified and depends a lot on your use case. Performance can absolutely matter even with modern hardware. There is no one-size-fits-all collation that is best in all scenarios, if there was, we really wouldn't need the option to choose. If you have a thousand rows in your table, sure, use whatever. If you have a billion rows in your table, things are different and you may not be able to afford leaving performance on the table.

flokoe 5y ago
Thanks for your critique! I admit it may be a simple view, but I still believe it is the best choice for most of us. However, if you are in a position where you have to handle such an extensive dataset, you probably know about this detail and can decide if this is a suitable option or not.

TedShiller 5y ago

aka why I switched to Postgres

dmz73 5y ago
Does posgres support case insensitive collation on standard varcharrs? Last time I used it that was not an option so it made everything a lot more complicated. Postgrges also did not support hints for queries so tweaking performance was more complicated. Both MySQL and postgres performed much worse with complex queries than MSSQL even after tweaking.

acabal 5y ago

utf8 vs utf8mb4 bit me in one of the most frustrating bugs I've ever had to debug. At Scribophile members can write critiques for people's writing, with comments inserted inline. The underlying software dates back to the PHP5 days when MySQL only had the utf8 option. Everything was working fine for years, when all of a sudden users started complaining that from time to time, they would submit a critique and it would be mysteriously cut off at random places. The problem was very intermittent, didn't happen very often, and there was no seeming rhyme or reason to it. But when it did happen, it was catastrophic because members would lose hours of work, seemingly at random! All kinds of testing scaffolding and logging was put in place to try to find the problem with no luck. Then, after quite some time, we realized what the problem was. At that time, emoji keyboards were brand new; most phones/tablets had limited support, and people didn't yet use phones and tablets for everything like they do now. But they were out there. Some users who were using these new emoji keyboards were inserting emoji smiley faces as they were writing critiques. In Scribophile's web interface, everything looked fine; but when the user submitted the critique, MySQL tried to insert a multibyte Unicode character into a regular utf8 field, and SILENTLY threw that character and all of the data after it away!! Boy were we upset at MySQL about that one. But at least we figured it out!

BoxOfRain 5y ago
It's stories like this that stop me complaining online about a long day of futile debugging!
gpderetta 5y ago
You probably mean outside of the basic multilingual plane. Any non-ascii character is multibyte in utf8.
webtopf 5y ago
I had the pleasure of finding that same behaviour in an application around 2015. When someone commented on a food order "Extra cheese please :folded hands: but no shrimp, I'm allergic." But one thing I haven't found out is why some emojis did go through. The basic ones like a simple :smile: it seemed to me. Could it be that some only need 3 bytes and when more and more emojis got released, they went into 4 bytes? Edit: HN stripped the emojis from my comment... I would put a rolling eyes emoji here if I could.

avgcorrection 5y ago

> Even though 128 characters are enough to cover the English language and a few basic symbols You can communicate just fine in English using ASCII. But plenty of English words use non-ASCII letters.

DemocracyFTW 5y ago
Æsthetics do play a rôle here
kingcharles 5y ago
To think otherwise would be naïve.

deleted 5y ago

[deleted]

captainmuon 5y ago

I understand why this would affect collation and indices, but why can't I even store the four byte characters in an utf8mb3 table? The SQL parser and printer seems to deal with the characters fine, because it is independent from the table. I would assume it would just stuff the UTF-8 bytes in there as they are. Is it trying to validate them? Or is it using UTF-32 (or a truncated version) internally and saving everything as 3-4 bytes?

tzs 5y ago

> For example utf8mb4_general_ci does not knwo how to sort s and the German character ß (“sharp S”) in contrast to utf8mb4_unicode_520_ci which sorts ß just fine If I use utf8mb4_general_ci now, is it guaranteed to produce the same results on a given dataset each time, and to keep producing those same results under future versions of MariaDB? Or is using it on data that contains characters for which it does not define a sort order like using undefined behavior in C, and it is free to produce different results from run to run and from version to version?

tpoacher 5y ago

Is it me or does the author seem to be using utf8 and unicode interchangeably (at least in the intro)?

orthoxerox 5y ago

I wonder who that "bar@bar.mysql.r18.ru" was.

alserio 5y ago

> One last concern is the size stored on the disk because UTF-8 supports four bytes per character. But since UTF-8 is a variable-width character encoding, it only uses as many bytes as needed. For example, the first 128 characters like ASCII only use one byte I'm not sure this is true if you use CHAR instead of VARCHAR. Moreover if I'm not misremembering indexes still store the full fixed size 3 or 4 byte representation and index size might matter. However, I'll still use mb4 to avoid the fun times other have already described in the comments

chrismorgan 5y ago

utf8mb3 allows representing up to U+FFFF, but not U+10000–U+1FFFF. It’s like UCS-2 is to UTF-16, only without any actual technical rationale.

JoeAltmaier 5y ago

Speaking of expensive screwups, one of the most expensive schematic errors ever was the routing of the interrupts on the original IBM PC. It put the serial interrupt on the lowest-priority interrupt pin, instead of the highest. This spawned a trillion-dollar 'communications card' industry that was arguably not necessary. Certainly not necessary to the degree it grew. Interrupts should be prioritized by latency. Instead, the designer prioritized them by, I don't know, their most favorite to least favorite? E.g. the disk transfer completion interrupt was very high, even though it literally has infinite latency (nothing irrecoverable is going to happen if it's delayed indefinitely). If I had a time-travel machine that only allowed a 1 centimeter window into the past, I would open a window to that original schematic and swap the disk and comm interrupt lines. That would have changed silicon valley history.