http://www.jeppesn.dk/utf-8.html
17 years later and the website is still there...
http://www.jeppesn.dk/utf-8.html
17 years later and the website is still there...
I did a mysqldump/restore last year with default options and it ate all emoji in the database. This is because MySQL's "utf-8" isn't real UTF-8, it's "utf8mb3" which does not support non-BMP characters. You have to explicitly ask for utf8mb4 to get sane behavior. In my case the database was configured correctly, but mysqldump still defaulted to utf8mb3. I found out that day all my database backups had been subtly corrupted forever.
This is one reason I use PostgreSQL everywhere I can get away with it. It has infinitely saner tooling. But WordPress insists on being married to MySQL...
Here's more information about the difference between utf8 and utfmb4 in MySQL, in case anyone is wondering: https://stackoverflow.com/a/30074553
I'd say that in most modern circumstances you probably should just go with utfmb4, unless you have a good reason for doing otherwise (apart from only ever needing to use the BMP, or having some odd storage restrictions).
Can anyone come up with an actual good reason to use utf8mb3?
These days the defaults tend to be slightly more sane. PHP is still messed up with the various mb_ string functions though.
You set up the database, you know you want Unicode, so you set it to use utf8 (we'll ignore for now MySQL's legacy BMP issue), your tables, your columns are using it: great.
You run your site for a while and everything is fine: characters all display fine, though you do occasionally notice some searches with non-ascii characters don't work quite as you expect, and one day you use an admin tool and you see things are messed up...
What you didn't realize was that you set up all that stuff but not the connection charset, which used to default to latin1. So MySQL sees your UTF-8 data coming in, thinks "this is latin1" because that's what the connection charset is set to, sees your tables want utf8, and happily "converts" your UTF-8 text to UTF-8. You don't notice it because it does the same thing in reverse when sending results back to you, so you think everything's fine.
Things got wise to setting the connection charset, avoiding this problem, and eventually the defaults also changed. So it's been pretty uncommon for new databases for a while now.
But, you still see people in this poster's situation, discovering their database is stored in this mis-encoded form when they move to new servers or versions of PHP that changed the default connection charset.
It's fairly easy to fix the data so it's stored correctly and you don't need the incorrect latin1 parameter to hide the problem, but just hope you didn't run a while without noticing the charset change so you now have a mix of good and bad encodings in the same tables.
But I don't know about MySQL. What I do know is that PHP by itself doesn't have this problem anymore.