Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

medium.com

Post reply on HN
Print view View original

31–40 of 170 posts

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#31

Post by morgo »

kreetx wrote: post #11

Given that the article says that everyone should use utf8mb4, then why don't/haven't they just fixed it? Should be easy enough, and probably safe, too.

The desire for backwards compatibility/easier upgrades is why utf8mb4 could not just replace utf8:

The sorting order for the utf8 collations is not identical to utf8mb4 ones, and so an upgrade would require indexed columns to be rebuilt (remember, indexes are sorted).

I think with hindsight it would have been better to rip the bandaid off. 8.0 took a middle of the road approach by switching the default to utf8mb4.

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#33

Post by chrismorgan »

chrismorgan wrote: post #17

Possibly my favourite thing about the rise of emoji is that they’re not Basic Multilingual Plane, and so stupid hacks like MySQL’s utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2002–2004) are now obviously insufficient for full Unicode support, and enough people care about them that adoption of this basic level of Unicode is driven, and so non-English speakers are inadverten…

While I’m contemplating why people might have thought utf8mb3 was wise in 2004, I’ll ask a similar question that I’d love to know the answer to, of events from my early childhood: why did UTF-16 gain popularity? I get UCS-2 existing, but once it became evident it wasn’t enough, why make UTF-16 and ruin Unicode with surrogate pairs (which still cause trouble today, leading to things like WTF-8 to make it possible for web browsers to abandon their UTF-16/UCS-2 mess in favour of almost-UTF-8)? UTF-8 was around by early 1993, while UTF-16 was developed several years later and seems fairly obviously the worst of both worlds—twice as large for normal text, ASCII-incompatible, and not even a fixed-width encoding, the only thing it almost had over UTF-8. The only thing it had going for it was that it was roughly compatible with UCS-2; but was UCS-2 that popular at the time?

(Concerning UCS-2, hindsight says it should have been obvious 65,536 code points was going to be insufficient, but I’ll overlook that as an easy mistake to make; that part is better documented than the rise of UTF-16.)

Possibly the most helpful description I have is from https://en.wikipedia.org/wiki/UTF-16#History, but even that doesn’t explain why it became popular. Why did Java adopt it, Windows, browsers, &c.? Why?

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#34

Post by chrismorgan »

pilif wrote: post #25
chrismorgan wrote: post #21

One troublesome thing about utf8mb4: if you have a VARCHAR( n ) column, this means 4​ n bytes are required; this doesn’t mix particularly well with indexes, as you can only index columns of up to 767 bytes; VARCHAR(192) is thus 768 bytes, and too long to be indexed—so you’re limited to VARCHAR(191) or less. This leads to things like django-celery flat-out not working on utf8mb4 without a (history-rewriting) patch, be…

then again, indexes on VARCHAR() can only really be used for equality and start matches. Now I'm not saying it's impossible, but I have a feeling you're not likely going to do a lot of `where string_column =… ` or `where string_column like '…%'` where … is 192 characters in length. If you need to do substring search or full text search, a traditional index won't help you anyways.

Yeah, string primary keys are the only place I expect people to actually hit this, as seen in the django-celery case.

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#36

Post by philbarr »

cosarara wrote: post #27
philbarr wrote: post #22

Earlier quoted context omitted.

> not Basic Multilingual Plane, and so stupid hacks like the utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2004) are now obviously insufficient for full Unicode support From [0]: UTF-8 was designed with a much larger limit of 231 (2,147,483,648) code points (32,768 planes), and can encode 221 (2,097,152) code points (32 planes) even if limited to 4 bytes What is wrong with U…

Not with utf-8 but the "utf8" mysql encoding.

ok - but parent referred to "utf8 character set". As if things weren't confusing enough already!

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#37

Post by ainar-g »

Strilanc wrote: post #32

Wait, isn't 4 bytes per character also not enough to store UTF8? Can't each character be up to 8 bytes long, because the number of leading 1s in the first byte determines the length of the character?

According to the Wiki, the original UTF-8 proposal used up to 6 bytes.[1] But the Unicode standards people decided that 21 bit should be enough for everyone:

>Since the restriction of the Unicode code-space to 21-bit values in 2003, UTF-8 is defined to encode code points in one to four bytes, depending on the number of significant bits in the numerical value of the code point.

So, if they ever decide to use those extra two bytes, it will probably not be called UTF-8. Perhaps something like "UTF-8-MB-6".

[1] https://en.wikipedia.org/wiki/UTF-8#History

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#38

Post by [deleted] »

chrismorgan wrote: post #17

Possibly my favourite thing about the rise of emoji is that they’re not Basic Multilingual Plane, and so stupid hacks like MySQL’s utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2002–2004) are now obviously insufficient for full Unicode support, and enough people care about them that adoption of this basic level of Unicode is driven, and so non-English speakers are inadverten…

[deleted]

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#39

Post by jeltz »

MrBuddyCasino wrote: post #15

"An optimization was chosen to limit utf8 to 3 bytes, enough to handle almost all modern languages." I can't for the life of me imagine how, when discussing this in a room full of competent developers, everyone just went "great idea, lets do this". Just adds to the long list of questionable MySQL engineering decisions.

I am pretty sure it was not because of any optimization but that the real reason was to make sure a VARCHAR(255) would still fit within the maximum field size of InnoDB (768 bytes) at the time.

Since then MySQL has added ROW_FORMAT=DYNAMIC to InnoDB which allows for larger values, but that probably required major changes to InnoDB.

Re: In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

#40

Post by [deleted] »

philbarr wrote: post #22
chrismorgan wrote: post #17

Possibly my favourite thing about the rise of emoji is that they’re not Basic Multilingual Plane, and so stupid hacks like MySQL’s utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2002–2004) are now obviously insufficient for full Unicode support, and enough people care about them that adoption of this basic level of Unicode is driven, and so non-English speakers are inadverten…

> not Basic Multilingual Plane, and so stupid hacks like the utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2004) are now obviously insufficient for full Unicode support From [0]: UTF-8 was designed with a much larger limit of 231 (2,147,483,648) code points (32,768 planes), and can encode 221 (2,097,152) code points (32 planes) even if limited to 4 bytes What is wrong with U…

[deleted]
Post reply on HN