Storage Emoji with MySQL

If you want your website or App to support Emoji, there are some details you need to know when setting up MySQL for the first time.

Emoji

The special point of the Emoji character is that it takes 4 bytes to store it. This collate of the UTF8 character set, which is common in MySQL, utf8_general_ci supports only 3 bytes. So to be able to store Emoji, you need to use a utf8mb4 character set instead.

When creating a table, use a statement like this:

CREATE TABLE ' tbl ' (...) Engine=innodb DEFAULT charset=utf8mb4 COLLATE utf8mb4_general_ci;

MySQL version

utf8mb4support for character sets is a new feature of MySQL 5.5, so you need to make sure that you are using at least 5.5 of the MySQL version. Basically, new projects after 2014 should be 5.6 straight.

MySQL Backup and Import

utf8mb4after the character set is enabled, the default parameters are no longer available for backup and import.

When backing up with mysqldump, you need to include:

Mysqldump--DEFAULT-CHARATER-SET=UTF8MB4

When restoring a backup or connecting through a program, you need to send the following SQL command after each connection is opened:

SET CHARSET UTF8MB4

Storage Emoji with MySQL