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

21–30 of 170 posts

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

#21

Post by chrismorgan »

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, because it had a VARCHAR(255) primary key: https://github.com/celery/django-celery/issues/259.

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

#22

Post by philbarr »

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

[0]: https://en.wikipedia.org/wiki/Plane_(Unicode)#Basic_Multilin...

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

#23

Post by morgo »

im3w1l wrote: post #19
morgo wrote: post #4

Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... The default for MySQL 8.0 (current GA) is utf8mb4. I also recommend reading this introduction post here: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...

Do you have a collation were no two different strings compare equal? Have you considered imposing an arbitrary order where you don't know which one "should" go first?

Yes - there is a binary collation.

w.r.t. arbitrary:

The ordering must be deterministic so that indexes can maintain sorted order. It's possible to create a custom collation to have your own ordering - but I would say most users don't do that.

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

#24

Post by bri3d »

Quarrelsome wrote: post #16

Friend of mine just recounted a story of when this bug killed all their MySql instances across an entire enterprise installation because a user sent an email with the hamster face emoji in the title.

I worked at large company where an emoji or other 4-byte UTF character in a commit message would take down multiple systems simultaneously, including JIRA and various internal auditing and pipeline monitoring tools. It happened surprisingly rarely considering the lack of safeguard against the issue.

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

#25

Post by pilif »

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.

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

#27

Post by cosarara »

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…

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

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

#28

Post by JdeBP »

morgo wrote: post #4

Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... The default for MySQL 8.0 (current GA) is utf8mb4. I also recommend reading this introduction post here: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...

> An optimization was chosen to limit utf8 to 3 bytes

You chose not to define the UNICODE_32BIT macro that was in the original source from 2002?

* https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b0...

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

#29

Post by ainar-g »

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.

I agree that that point sounds too rough. I would write something like

>Every software has issues; so know your software's gotchas.

With that said, the perception I get is that PostgreSQL has less such gotchas and works better "out of the box". Though still not optimally, according to our Ops.

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

#30

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]
Post reply on HN