Getting Out of MySQL Character Set Hell (2009) | Hacker News Reader

Getting Out of MySQL Character Set Hell (2009)

bluebox.net

33 pointsscintill7621 comments

ctrl+f doesn't show mention of utf8mb4 except in comments.

In short: you should only use utf8mb4 in mysql as charset, cause utf8 in mysql is a broken invented 3-byte charset that will explode when you input 4-byte characters (i.e. an emoji).

utf8mb4 is what the rest of the world actually calls utf8.

That was added in 5.5 IIRC, which wasn't released until the end of 2010 - so if would be understandable if the writer of this article did not have know about it back in mid 2009, or knew about it (if it was a much talked about plan the a release that was happening "soon") but chose to ignore it in favour of concentrating on what is available for production systems.

thanks, I didn't mean to fault the author, I was just trying to be helpful to the casual readers.

Here's a document on utf8 v utf8mb4, how to migrate from one to the other, and the potential pitfalls involved (due to utf8 being stored as fixed-size 3 bytes and utf8mb4 being stored as fixed-size 4 bytes): https://mathiasbynens.be/notes/mysql-utf8mb4

To clarify: the 3-byte subset is not a MySQL invention. It is the Basic Multilingual Plane (BMP): http://en.wikipedia.org/wiki/Plane_%28Unicode%29#Basic_Multi...

That's not correct. The Basic Multilingual Plane (like Unicode itself) is not an encoding, it is a subset of the Unicode codespace. UTF-8 restricted to sequences of 3 bytes can only represent characters between U+0000 and U+FFFF inclusive, which happens to exactly contain the characters defined by the BMP.

Most smileys now ubiquitous on smart phone keyboards are valid Unicode characters outside of the BMP set, yet being used constantly on the web. BMP just doesn't make sense anymore, nor does mysql utf8.

I totally agree! It also makes sense for countries that have their own character sets to use utf8 too (as they may borrow words, characters from other languages).

I was just pointing out that even if it was limited support, it wasn't a set of characters that MySQL came up with.

MySQL's 3-byte 'utf8' is the reason I first switched to PostgreSQL. I never looked back.

The authors actual understanding of MySQLs encoding is only half way there. Collation specifically is the algorithm it will use when ordering by the column for instance.

The article contains numerous half truths and generalizations (the kind someone in the process of learning would say) and not expertise.

Take what he says with a grain of salt.

Collation is both. It combines both the locale-specific sorting approach, and the encoding.

Collations are encoding specific, eg: utf_general_ci works specifically on UTF8 values, that said, the collation does not determine the encoding, the encoding determines the potential collations.

But the collation field specifies both the collation and the encoding?

CREATE TABLE tbl_name (column_list) [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]

Character set is specified separately from collation. It's like having a ford vs a ford headlight. The collation is a part of the character set.

“Sometimes forcing the browser to treat the page as being encoded in UTF-8 will show you the characters as they're supposed to be.”

You really SHOULD always say something equivalent to charset=utf-8 in your HTML head (naming your actual encoding, of course).

Naming your encoding? So always charset=utf-8 then, right?

Seriously - is there ever any reason to use anything else on the Internet?

I use utf8mb4 with binary collation as a default.

When I then get to a column type like email, I don't know what I should use. Dropping down to 7-bit case-insensitive sounds plausible, but another option would be to stick with utf8mb4 but case-insensitive?

Is there a best practice?

I almost exclusively use utf8mb4_bin (for "human" text) and ascii_bin (for ID's or other "computer" text) -- they're basically the two 'extremes', and it's rare to need anything in between.

And then other collations like case-insensitive or accent-insensitive only when required, e.g. because the field is used for search.

Does MySQL have a dump(column_name) function like Oracle? The dump function was a life saver when I needed to verify if some non-ascii columns had transferred correctly or were now full of upside down question marks.

Easiest way is probably to run a HEX() function on the column, and compare output.

WTF-8

Reply on news.ycombinator.com