Storing emojis in MySQL

All we need is an easy explanation of the problem, so here it is.

I’m new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don’t want to lose it.

I don’t know the proper way of storing the utf8mb4 character-set using the MySQL console. Is there any way of doing that using mysql-administrator?

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 5.7.33                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 5.7.33-log            |

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR 
Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8               |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

I changed my.cnf file to

[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

It shows no error, but neither does it store the emojis – when enter emojis it shows as ? this question mark.

I convert the utf-8 encoding to utf8mb4_general_ci:

|--------------------------+--------------------|
| character_set_system     | utf8               |
|--------------------------+--------------------|
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
|--------------------------+--------------------|

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Do SHOW CREATE TABLE for one of the tables where you need Emoji. It probably says ‘utf8’ on the columns and/or default for the table. The columns must be utf8mb4.

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

Will blindly change all the columns to utf8mb4. This is probably the action you need.

Also, the connection needs to specify utf8mb4. Since I don’t know what client you are using, I can’t provide specific information. If necessary, run this right after connection: SET NAMES utf8mb4;

Terminology:

  • "character set", such as utf8 or utf8mb4 is the encoding for the characters. This is where you are having trouble with storing Emoji.
  • "collation" is the ordering, such as whether ‘a’ < ‘b’ and whether there is case folding to make ‘a’ = ‘A’. Collation may be important when comparing (WHERE) or sorting (ORDER BY).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply