Storing emojis in MySQL

Question

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    |
|--------------------------+--------------------|

Answer 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).