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.
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
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.
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.
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.
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.
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?
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.
23 comments
[ 2.4 ms ] story [ 52.4 ms ] threadIn 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.
I was just pointing out that even if it was limited support, it wasn't a set of characters that MySQL came up with.
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.
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.
You really SHOULD always say something equivalent to charset=utf-8 in your HTML head (naming your actual encoding, of course).
Seriously - is there ever any reason to use anything else on the Internet?
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?
And then other collations like case-insensitive or accent-insensitive only when required, e.g. because the field is used for search.