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

71–80 of 170 posts

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

#71

Post by pwagland »

bradleyjg wrote: post #60
chrismorgan wrote: post #33

Earlier quoted context omitted.

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 the…

Except that with utf-16 you still have to give up any hope of O(1) string operations, unless you don't care about accuracy. Only UTF-32 allows O(1) index based operations.

For example, 🧐 (aka face with monocle) needs 2 UTF-16 'characters', since the UTF code point is U+1F9D0, or, in UTF-8: F0 9F A7 90.

And this ignored the point that others have made about combining characters, such as ‍ woman farmer Unicode: U+1F469 U+1F3FD U+200D U+1F33E, UTF-8: F0 9F 91 A9 F0 9F 8F BD E2 80 8D F0 9F 8C BE

That beast needs 7 UTF-16 or 15 UTF-8 "characters". I.e. 14 bytes vs 15 bytes. And this is still only one displayed character.

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

#72

Post by twoodfin »

gpvos wrote: post #51
chrismorgan wrote: post #33

Earlier quoted context omitted.

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 th…

I think folks who weren't there might not realize how big a deal the original Unicode standard was and how quickly technology vendors raced to adopt it. Windows NT, for example, must have been developed with Unicode in mind even before the standard was official. In those circumstances the broad adoption of UCS-2 was effectively inevitable.

Even if UTF-8 had been widely known, I don't think the eventual necessity for > 16-bit code points was clear enough to have given it a chance against the relatively easy architectural shift of replacing one fixed-length character encoding with another.

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

#73

Post by willvarfar »

> I’ll make a sweeping statement here: all MySQL and MariaDB users who are currently using “utf8” should actually use “utf8mb4”. Nobody should ever use “utf8”.

I completely agree with this sentiment, although I recall being bitten by it in indexes. The article goes on to talk about how big a CHAR(1) is if its utf8 vs utf8mb4. Well, the same applies to varchars and so a varchar(64) is potentially requiring 64, 192 or 384 bytes, depending on the charset. This can bite you because the MySQL query planner limits the longest prefix used for index scans to iirc 767 or so bytes. So I migrated some columns from utf8 to utf8mb4 and suddenly my compound indices stopped being used because the varchar contents could legally exceed that maximum. Something like that.

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

#74

Post by skywhopper »

This is a fascinating tale. But the #1 takeaway by the author is dangerous: "Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems."

This is correct as far as it goes, but it's poor advice. You may as well say "Software has subtle bugs and oddities, and you can avoid a lot of bugs by avoiding software." True! But not helpful! If you don't need a relational database, then yes, don't use one. But there's nothing specific about databases as a category that deserves a special callout. You can also avoid a lot of bugs by avoiding TLS, containers, threads, coroutines, blocking calls, asynchronous calls, networks, filesystems, operating systems, and computers.

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

#75

Post by pwagland »

bhaak wrote: post #47
chrismorgan wrote: post #33

Earlier quoted context omitted.

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…

I would disagree with the statement that UTF-16 gained popularity. UTF-16 is mostly used where it provided an easy upgrade path from the 16 bit restricted UCS-2 to enabling full Unicode coverage. Switching from UCS-2 to UTF-8 would have been a pain either because of dropping backward compatibility or ridiculous wrappers to get it working. Microsoft was one of the first companies to implement Unicode, so they didn't h…

Java 1.0 was made back in the days when "65K characters would be enough for anyone!" hence they chose UCS-2. Java only supported UTF-16 as of Java 5. I suspect that if they could turn back time, they would have chosen UTF-8 from the get-go, but, as you say, that was a battle lost long ago.

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

#76

Post by msl »

bradleyjg wrote: post #60
chrismorgan wrote: post #33

Earlier quoted context omitted.

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 the…

> With utf-16 you can get O(1) string operations at a cost of doing them on code points rather than graphemes.

No you cannot. UTF-16 is a variable-length encoding (up to two 16-bit code units per code point).

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

If we limit ourselves to Latin 1, UTF-16 requires 100% more memory than UTF-8. How often do you need to randomly access a long string anyway?

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

#77

Post by gpvos »

muglug wrote: post #6

Counterpoint: emojis are bad and should be discouraged

The nice thing about emojis (maybe the only nice thing) is that they are so popular that they force companies and developers to implement proper support for all planes of Unicode, which means that all characters and symbols in those planes that are actually useful are then also supported.

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

#78

Post by bradleyjg »

msl wrote: post #76
bradleyjg wrote: post #60

Earlier quoted context omitted.

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 the…

> With utf-16 you can get O(1) string operations at a cost of doing them on code points rather than graphemes. No you cannot. UTF-16 is a variable-length encoding (up to two 16-bit code units per code point). > 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. If we limit ourselves to Latin 1, UTF-16 requires 100% more memory than UTF-8. How…

Right, code units not code points. My mistake. Nonetheless that’s what you get back from e.g. java’s charAt.

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

#79

Post by morgo »

jeltz wrote: post #48
morgo wrote: post #43

Earlier quoted context omitted.

The optimization was made at a different time in history. As I wrote in another comment here: while the decision is regrettable, because of a desire for backwards compatibility and simplified upgrade there wasn't an easy way out of it. The utf8[mb4] support in MySQL 8.0 is much better than prior releases. The team worked really hard on eliminating the use cases for utf8mb3 (performance) so that "just use the default…

Is it really an optimization? I always assumed they picked 3 bytes to make sure a varchar(255) still would fit within the InnoDB field size limit of 768 bytes, so they could avoid/postpone having to fix InnoDB to support variable size fields.

This decision was made before InnoDB was the default.

While the performance differences are historical now, consider the case that because of fixed sized buffers, one query could switch from a temp table in memory to a temp table on disk. The profile of the query has effectively changed.

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

#80

Post by morgo »

masklinn wrote: post #59
chrismorgan wrote: post #33

Earlier quoted context omitted.

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 VARCH…

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

The utf8mb3 optimization was made before InnoDB was the default storage engine (2010).

Post reply on HN