4 ms·

MySQL finally ships with utf8mb4 by default in MySQL 8, but anything older still uses latin1 / latin1_swedish_ci by default and should be converted to utf8mb4.

hinkley 5y ago
It's been years and I still get mad every time I think about the fact that 'utf8' is a broken encoding and they had to make a new one called utf8mb4. I have to trust my database. If I can't trust the authors of the database, then how do I trust the database?
- josephg 5y ago
  To save a Google for anyone like me - there’s nothing wrong with utf8. The problem was that MySQL had a broken implementation of utf8 (“utf8mb3”) which only supported Unicode characters which fit in 3 bytes. As a result you couldn’t save any data in your database which contained emoji. This is upsetting because until recently if you asked for a utf8 column, this is what you got instead. You got something which was almost, but not quite compatible with utf8.
  - ChrisSD 5y ago
    Interestingly, this was a mistake even the creators of UTF-8 wanted to make > The 4, 5, and 6 byte sequences are only there for political reasons. I would prefer to delete these Obviously 5 and 6 byte sequences are consigned to history but 4 byte sequences turned out to be useful for more than "political reasons". http://doc.cat-v.org/bell_labs/utf-8_history http://doc.cat-v.org/bell_labs/utf-8_history (scroll right to the bottom)
jillesvangurp 5y ago
I ran into this in Amazon's RDS not so long ago (3 years?) where I wasted a good few hours figuring out why mysql was mangling our emojis. A good test for this is trying to insert the character, which will only work with utf8mb4 as it uses the full 4 bytes, which mysql's broken 3 byte utf-8 implementation simply does not support. This is also the reason for 191 character limit as 191 is 3/4 of the 255 bytes you'd be able to store with their broken implementation. Which of course 1/3rd of the maximum entry size for innodb of 764 bytes (the last one is \0), or 4*191. They never changed the underlying implementation. Hence the weird limit.

hinkley 5y ago

It's been years and I still get mad every time I think about the fact that 'utf8' is a broken encoding and they had to make a new one called utf8mb4. I have to trust my database. If I can't trust the authors of the database, then how do I trust the database?

josephg 5y ago
To save a Google for anyone like me - there’s nothing wrong with utf8. The problem was that MySQL had a broken implementation of utf8 (“utf8mb3”) which only supported Unicode characters which fit in 3 bytes. As a result you couldn’t save any data in your database which contained emoji. This is upsetting because until recently if you asked for a utf8 column, this is what you got instead. You got something which was almost, but not quite compatible with utf8.
- ChrisSD 5y ago
  Interestingly, this was a mistake even the creators of UTF-8 wanted to make > The 4, 5, and 6 byte sequences are only there for political reasons. I would prefer to delete these Obviously 5 and 6 byte sequences are consigned to history but 4 byte sequences turned out to be useful for more than "political reasons". http://doc.cat-v.org/bell_labs/utf-8_history http://doc.cat-v.org/bell_labs/utf-8_history (scroll right to the bottom)

jillesvangurp 5y ago

I ran into this in Amazon's RDS not so long ago (3 years?) where I wasted a good few hours figuring out why mysql was mangling our emojis. A good test for this is trying to insert the character, which will only work with utf8mb4 as it uses the full 4 bytes, which mysql's broken 3 byte utf-8 implementation simply does not support. This is also the reason for 191 character limit as 191 is 3/4 of the 255 bytes you'd be able to store with their broken implementation. Which of course 1/3rd of the maximum entry size for innodb of 764 bytes (the last one is \0), or 4*191. They never changed the underlying implementation. Hence the weird limit.