Search code examples
mysqlencodingutf8mb4

MySQL can select litral emoji but wont store emoji into table

I am trying to insert some emoji characters into a table in MySQL, but values are stored as question marks (????).

I made sure to create the database with the proper utf8mb4 encoding:

enter image description here enter image description here

mysql> describe users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(191) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Then I tried to make sure, does MySql understand emoji or not, so I did this:

mysql> select '🌰';

+------+
| 🌰     |
+------+
| 🌰     |
+------+
1 row in set (0.00 sec)

Then I did this:

mysql> insert into users (name) values ('🌰');
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> select * from users;
+----+------------+
| id | name       |
+----+------------+
| 21 | فاضل       |
| 30 | سلاحف      |
| 46 | ????       |
| 47 | ????       |
| 48 | ????       |
| 49 | ????       |
+----+------------+
6 rows in set (0.01 sec)

I don't know what to do to fix that..

** EDIT ** : as requested in the comments, I ran the following command:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+-------------------------+
| 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                    |
| character_sets_dir       | /static/share/charsets/ |
+--------------------------+-------------------------+
8 rows in set (0.00 sec)
Solution

  • Your connection is set up for utf8; it needs to be set up for utf8mb4.

    How did you set it? Change to whichever of these applies.

    • SET NAMES utf8mb4
    • PDO(... charset=utf8mb4)
    • mysqli::set_charset('utf8mb4')
    • etc

    Emoji are 4-byte utf8 codes, hence the four question marks.