Avoid Garbled Emoji in MySQL, UTF8MB4, the Complete Guide

It’s really a pain to deal with the utf8 encode stuff in early version of MySQL, There are two different explanation for utf8 in MySQL. In the early version of MySQL 5. MySQL resolve utf8 as utf8mb3 by default. But in newest 5.7 and 8, it resolves utf8 to utf8mb4 as default.

But it doesn’t mean everything will go well without concern. In my experience in using 5.7. It is still easy to get trapped with the encoding problem.

I didn’t found an easy way on mac OS to set the global default as utf8mb4.

But fortunately, I figured out some useful method to get around this problem.

1.When you create a database, add two command options to set the default character set for whole database. character set & collate. Like this.

mysql > create database <dbname> character set utf8mb4 collate utf8mb4_general_ci

2.When using connector to connect MySQL, add two parameters to the connect link url as follows.

…&useUnicode=yes&characterEncoding=UTF-8

3.Recently, I came across some really unbelievable none sense problem. When I backup data use mysqldump. but when it restored to another system. The emoji got garbled. At first, I guess it is the problem was caused by operating systems’ distinction in interpret some special character. Since I was doing cross platform database migration. But finally, I found I’m wrong. It has nothing to do with it. I solved this problem easily by adding a command option to mysqldump. As this.

$ mysqldump — default-character-set=utf8mb4 -p — databases <dbname> > <dumpfilename>

MySQL is really deal with incomprehensible in dealing with dump encoding. I already set the database’s default character set, but when dump it, it still use the wrong character set to encode the character. Since it is a free software, I’m not going to complain much. If any others encounter similar problems. This three methods might help you.