How to insert utf-8 mb4 character (emoji in ios5) in mysql?

Question

I am using mysql 5.5.10 and its character_sets

| character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | 

I changed utf8mb4 to utf8 for iOS5 emoji. They are represented by a code of 4 bytes.

But when I inserted 3 emojis smiles, '???' located in mysql.

This is 3F 3F 3F (Hex).

I can store iOS4 emojis well, but not iOS5.

How can I store iOS5 emojis?

Please help me.

Answer 1

4 bytes Unicode characters are not yet widely used, so not all applications there fully support them. MySQL 5.5 works fine with 4 byte characters when configured correctly - check to see if your other components can work with them.

Here are some more things to check:

• Make sure that all standard characters and text fields of your tables are converted to utf8mb4, in addition to setting client and server character sets, for example. ALTER TABLE mytable charset=utf8mb4, MODIFY COLUMN textfield1 VARCHAR(255) CHARACTER SET utf8mb4,MODIFY COLUMN textfield2 VARCHAR(255) CHARACTER SET utf8mb4; etc.

  If your data is already in the utf8 character set, it should be converted to utf8mb4 without any problems. As always, back up your data before you try!

• Also make sure your application level sets the database connection character set in utf8mb4. A double check of this actually happens - if you are using an older version of your mysql client library of the selected client base, it may not have been compiled with utf8mb4 support and it will not configure the encoding correctly. If not, you may need to update it or compile it yourself.

• When viewing your data through the mysql client, make sure that you are on a machine that can display emoji and run SET NAMES utf8mb4 before running any queries.

As soon as each level of your application can support new characters, you can use them without any damage.

Answer 2

I recently wrote a detailed guide on switching from MySQL utf8 to utf8mb4 . If you follow the steps there, everything should work correctly. Here are direct links to each individual step of the process:

• Step 1. Back up
• Step 2: upgrade your MySQL server
• Step 3. Modify Databases, Tables, and Columns
• Step 4: Verify the Maximum Column and Index Key Lengths
• Step 5: Change the character set of the connection, client, and server
• Step 6: Repair and optimize all tables

Hope this helps.