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

51–60 of 170 posts

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

#51

Post by gpvos »

chrismorgan wrote: post #33
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…

Windows NT 3.1 was released in July 1993. Early versions of Windows NT used UCS-2. At that time, UTF-8 was not very well known yet, and it was certainly too late to change all that code, and especially the APIs.

I'm not sure about Java and Javascript, but I think they were developed before it became clear to most people that UCS-2 was not going to be enough (which was around, or likely even sometime after the time that UTF-16 was published in 1996).

Windows NT, Java and Javascript became popular, and none of them could easily change the width of their standard character encoding, which was also used internally in all of their code.

The goal of UTF-8 was to have an 8-bit encoding compatible with ASCII; that it could also encode more than 65.536 code points was more like a lucky accident. Maybe there was already talk about the need to extend Unicode beyond 2^16 code points, but nothing had been standardized at that time.

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

#52

Post by morgo »

jeltz wrote: post #39
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.

There are various buffers that were* not variable length in query execution (sorting, temp tables). It is not just about the on-disk format in this case.

* Use MySQL 8.0 :-)

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

#53

Post by philbarr »

philbarr wrote: post #36
cosarara wrote: post #27

Earlier quoted context omitted.

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!

Can't reply to the sibling comment by Chris, but there isn't a "utf8 character set". There's just different encodings of a character set.

Even after hunting around after going to the 404 you pointed me to, I still get this:

utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.

utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character.

So I don't think they are "different for SQL", whatever that means.

Sorry - not trying to sound snarky but terminology is important and this is an area people easily get tripped up in. Especially me.

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

#54

Post by Arie »

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…

This is supposedly fixed in databases created with MySQL 5.7 and up.

> MySQL 5.7 (2015) added some optimizations such as a variable length sort buffer, and also changed InnoDB’s default row format to DYNAMIC. This allows for indexes on VARCHAR(255) with utf8mb4; something that made migrations more difficult prior.

https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov...

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

#55

Post by MrBuddyCasino »

jeltz wrote: post #39
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.

There is usually a good reason for doing these kind of things. But then don't call it UTF8 encoding! It doesn't do that. It stores the most popular characters out of a larger address space. Call it UTF8-MB3 maybe from the start and let the user decide if having varchar(255) is worth the tradeoff. Thats the trouble with MySQL: doing silently the wrong thing, and thus violating the principle of least surprise. There is software where this might be ok, but a database is not that kind of software.

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

#56

Post by gmac »

chx wrote: post #10

> If you need a database, don’t use MySQL or MariaDB. Use PostgreSQL. This is stupid. There are pros and cons to every database. For example, MySQL allows you to set a trigger on an op, PostgreSQL requires you to write a function first.

Thing A having specific advantages over Thing B in no way implies that Thing B is not clearly superior overall.

I have used both MySQL and Postgres, although admittedly I haven't touched MySQL for a few years (the last time I tried to use MySQL for something, I found it would happily serve me up an arbitrary result for a clearly nonsensical query).

I thought this comment on an earlier thread summed up my feelings about the two systems rather beautifully: https://news.ycombinator.com/item?id=15173100

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

#57

Post by morgo »

billpg wrote: post #8

Dear database developers, please don't get hung up on string lengths. I'd be very happy to ask the DB for (say) a UTF-8 string of up-to 80 bytes. That means I could store 80 ASCII characters, 40 two-byte characters, 20 four-byte characters or some mixture as I wish.

While I agree with you, as I understand the SQL standard defines the N in varchar(N) as characters (not bytes).

There is a pathological case which is not common: max length of all 4-byte characters. Indexes are used to enforce features like unique constraints, it's important that everything can fit in the buffer length.

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

#58

Post by danso »

The article mentions it, but one of the massive frustrations with this issue is the huge amount of misinformation surrounding it (at least back in 2013), with many well-intended users on StackOverflow and the MySQL forums giving the exactly wrong advice on collation. Naming things, and not getting confused by bad names, is damned hard. This issue led me to quit MySQL for good, I can’t think of a more trivial issue that caused me such confusion and frustration as a software engineer.

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

#59

Post by masklinn »

chrismorgan wrote: post #33
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…

> While I’m contemplating why people might have thought utf8mb3 was wise in 2004

Because this is 2004 MySQL, they weren't exactly known for technical acumen and forward thinking. The root of their decision is that behind the scenes "UTF8MB4" stores codepoints in fixed-size areas.

And MySQL could not index columns larger than 767 bytes.

Guess what doesn't fit in 767 bytes? VARCHAR(255) in UTF8MB4. Anything above VARCHAR(191) would not be indexable.

Meanwhile VARCHAR(255) with 3 bytes per codepoint fits just right.

So rather than fix their crap, they just decided to limit their "UTF8" to the BMP and voilà no problem.

> 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?

The foundations of major systems (e.g. WinNT or Java) had long been laid out (in fact WinNT was released in 1993), and while UTF8 did exist there were few use cases for going beyond the BMP; and people had (and by and large still have) the misguided view that O(1) indexation in a string is a valuable property, and so their POV was that their choice was UCS2+surrogates (which few people would ever encounter) or UCS4.

> 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?

What they adopted was UCS2, this was "backronymed" to a bastard (non-transparent, you'd see every code unit) UTF16 later on. Java didn't even support getting codepoints out of strings until Java 5 in the mid aughts: https://docs.oracle.com/javase/8/docs/api/java/lang/String.h...

I'm pretty sure most string processing in Java and C# remains unaware of surrogate pairs and can corrupt proper UTF16 data rather interestingly. That is most definitely an extant issue in Javascript (not that properly handling that is even remotely sufficient to do actual unicode-aware string processing).

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

#60

Post by bradleyjg »

chrismorgan wrote: post #33
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…

With utf-8 you give up any hope of O(1) string operations. With utf-16 you can get O(1) string operations at a cost of doing them on code points rather than graphemes. If greater than 95% of the strings your language/program will ever see are going to be a single code point that trade off seems worth it.

I think a lot of people totally discount the enormous cost that Latin-1 users are paying for CJK (etc) support they don’t use. Maybe it’s the right call, but it isn’t obviously so.

Personally, at least for internal use, I like the idea of specialized implementations. At (immutable) string creation time decide whether to construct a Latin-1 string or a UTF-8 string (and possibly other fixed width encodings) depending on what code points are present. Expose the same set of operations (i.e. slicing on grapheme clusters) but with better performance characteristics where possible.

For storage at rest and especially communication with external programs I agree you probably should emit UTF-8.

Post reply on HN