Emoji expression and MySQL coding problem

Emoji, these small cartoon images that express emotion have become an integral part of modern communication, so that Emoji's word has been officially earned in 2015 by Webster's and Oxford's dictionaries. When it comes to the birth of emoji, here's a very interesting little story:
Around 1999, a Japanese young man named Kurita Water rang, like many straight men, was often misunderstood for texting his girlfriend. For example, "know" was interpreted as "angry", "impatient", then triggered the Cold War. So young Kurita water thought: "If you can insert some emoticons in the text to express feelings, we should need it!" ”
The original emoji was born.
Emoji more and more popular, a emoji expression, sometimes more than words. Especially on the mobile side, many users like to put emoji expression as part of their user name, for example, there are many friends of the user name is this, as follows:

It's really nice, but it's got some problems with our application. So far, we've come across a situation in which data cannot be stored because of a emoji expression in the user name.
The thing is, our system uses the database is MySQL, version is 5.5.31, recently we found that our program has a lot of error, error shows that some users of data can not be saved, through the error log analysis, the final location is because the user name with emoji expression, resulting in data storage failure. Why would the data not be able to save emoji expression? This involves a emoji coding problem.

The emoji character is part of the Unicode character set.
The common emoji emoji range in the Unicode character set and the specific byte mappings can be viewed in emoji Unicode tables.
Interestingly, the table also gives the same emoji expression in different systems or applications of the font (is the font right, emoji style can be changed through the font file).

The most authoritative information about emoji, can be in Unicode? Emoji charts on the website.
Our MySQL is using the UTF8 encoding, but because UTF-8 encoding can be two, three, four bytes, where emoji expression is 4 bytes, and MySQL UTF8 encoding up to 3 bytes, so the data is not plugged in. So in order for MySQL to support emoji expression storage, you must use UTF8MB4 encoding.
UTF8MB4 is completely backwards compatible as UTF8 's super set, so don't worry about character compatibility issues. The main effect of the need to worry about switching is that MySQL needs to be restarted.

There are a few things to determine before you upgrade:
1, MySQL version. The MySQL version must be 5.5.3 or higher, if you do not need to upgrade MySQL first, because the previous version does not support UTF8MB4
2. If you are using a Java server, upgrade or make sure that your MySQL connector version is higher than 5.1.13, or you will still not be able to use UTF8MB4. The rest of the language is unclear.

The steps required to modify MySQL are the following:
1. Modify the MySQL configuration file my.cnf
MY.CNF generally in the/etc/mysql/my.cnf position. When you find it, add the following three sections:
[Client]
Default-character-set = Utf8mb4
[MySQL]
Default-character-set = Utf8mb4
[Mysqld]
Character-set-client-handshake = FALSE
Character-set-server = Utf8mb4
Collation-server = Utf8mb4_unicode_ci
init_connect= ' SET NAMES utf8mb4 '

2, modify the database, table character set. Refer to the following statement:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Restart MySQL

4. Check the character set

The value of Character_set_system is UTF8, and this value cannot be changed to UTF8MB4, otherwise MySQL will not start.

Well, here, the data will support storing emoji emoticons data.

Summarize:
1, due to the rapid development of mobile internet, and emoji more and more popular, so in order to avoid upgrading the MySQL character set in business use, preferably in the deployment of MySQL, the default setting of MySQL support UTF8MB4, because UTF8MB4 is UTF8 superset, is backward compatible , so it does not affect the use of the UTF8 character set.
2, the development of the program, the construction of the database to build a table statement is best also specify the character of the library table is utf8mb4.
3, if it is already online business, and can not restart the MySQL situation, but also through the application layer to convert emoji and other special characters, in order to achieve the original data compatibility, in the acquisition, you can add a label to indicate whether the use of emoji, so that the data, the conversion can be.

Emoji expression and MySQL coding problem