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

Question

I am using mysql 5.5.10, and its character_sets are

| 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 from utf8 for iOS5's emoji. They are represented by 4byte code.

But when I inserted 3 smiley emojis, '???' is in mysql.

They are 3F 3F 3F (Hex).

I can store iOS4's emojis well, but not iOS5's.

How could I store iOS5's emojis?

Please help me.

Pulsation

mysql phpadmin shows ???, but iOS app can show emoji. – Runin

See also #38364066 , especially discussion of "question marks". – Bairam

See also #39463634 – Tibiotarsus

Answer 1

4 byte Unicode characters aren't yet widely used, so not every application out there fully supports them. MySQL 5.5 works fine with 4 byte characters when properly configured – check if your other components can work with them as well.

Here's a few other things to check out:

• Make sure all your tables' default character sets and text fields are converted to utf8mb4, in addition to setting the client & server character sets, e.g. ALTER TABLE mytable charset=utf8mb4, MODIFY COLUMN textfield1 VARCHAR(255) CHARACTER SET utf8mb4,MODIFY COLUMN textfield2 VARCHAR(255) CHARACTER SET utf8mb4; and so on.

  If your data is already in the utf8 character set, it should convert to utf8mb4 in place without any problems. As always, back up your data before trying!

• Also make sure your app layer sets its database connections' character set to utf8mb4. Double-check this is actually happening – if you're running an older version of your chosen framework's mysql client library, it may not have been compiled with utf8mb4 support and it won't set the charset properly. If not, you may have to update it or compile it yourself.

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

Once every level of your application can support the new characters, you should be able to use them without any corruption.

Nameplate

grep -R "SET NAMES" * – Accumulator

If textfield1 is indexed, you may have troubles because of indexes being restricted to 767 bytes. (There are many threads discussing this; look for "191".) – Bairam

Changing application layer character set to utf8mb4 , solved the issue. – Gladdie

set names utf8mb4; solved my problem with this, thanks! – Gylys

damn.... you even have to use set names utf8mb4; if your working in MySQL Workbench... who would have thought ! – Vedetta

Answer 2

I’ve recently written a detailed guide on how to switch from MySQL’s utf8 to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process:

• Step 1: Create a backup
• Step 2: Upgrade the MySQL server
• Step 3: Modify databases, tables, and columns
• Step 4: Check the maximum length of columns and index keys
• Step 5: Modify connection, client, and server character sets
• Step 6: Repair and optimize all tables

Hope this helps.

Mesitylene

Hi, on step 3, do I have to do that for every single table, column in my db? or just to the table and column I want to store emojis in? because right now I am changing just the one column and as soon as I set SET character_set_results = utf8mb4; I can't execute any queries on any tables. I'm metting a Server sent uknown charsetnr. Please report error! (even for select queries) – Democritus

@Democritus - Don't set just ...results, set all 3 things, as with SET NAMES or the connection parameters. – Bairam

Great tutorial! Do you know why after setting character-set-server = utf8mb4 in my.cnf, after running the query on the database the result is that character-set-server is actually latin1? Do you know why is not updating? Thank you very much! – Nakitanalani

If you want to take a look here is my similar problem: #39326625 – Nakitanalani

Hi Mathias, can I ask, if there's actually just one column that I'd like to support emoji, can I just change the database, one table, and one column's character set? can I remain all other columns as normal UTF-8? – Consistory

@Chor Wai Chun: Sure. But IMHO it’s better to follow best practices and enable full Unicode everywhere. That way, if you ever need it for any remaining columns, you can just start using it without having to mess around with the database/table/column settings. – Mesitylene

@MathiasBynens If I am planning to have few tables using utf8mb4, should I make all other tables and entire database to use utf8mb4 ? – Frydman

@MathiasBynens Also how to set a table to use utf8mb4 while creating the table? – Frydman