Getting Out of MySQL Character Set Hell (2009)

https://www.bluebox.net/insight/blog-article/getting-out-of-mysql-character-set-hell

by scintill76 • 11 years ago

33 21 11 years ago

riffraff2• 11 years ago

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.

dspillett• 11 years ago

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.

riffraff2• 11 years ago

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

masklinn• 11 years ago

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

morgo3• 11 years ago

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

Zr40• 11 years ago

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.

sly010• 11 years ago

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.

imron• 11 years ago

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

donatj3• 11 years ago

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.

TazeTSchnitzel2• 11 years ago

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

donatj3• 11 years ago

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.

tempodox• 11 years ago

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

stephen_g• 11 years ago

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

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

davidgerard• 11 years ago

WTF-8

utefan001• 11 years ago

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.

morgo3• 11 years ago

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

jcampbell1• 11 years ago

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?

crazygringo• 11 years ago

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.

Getting Out of MySQL Character Set Hell (2009)

Related Stories