Search code examples
mysqlunicodeutf-8emojiutf8mb4

Saving emoji characters in mysql database

I am trying to save some data on mysql database, input contains emoji characters like this : '\U0001f60a\U0001f48d' and I'm getting this error:

1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x8A\\xF0\\x9F...' for column 'caption' at row 1"

I searched over net and read a lot of answers include these: MySQL utf8mb4, Errors when saving Emojis or MySQL utf8mb4, Errors when saving Emojis or https://mathiasbynens.be/notes/mysql-utf8mb4#character-sets or http://www.java2s.com/Tutorial/MySQL/0080__Table/charactersetsystem.htm but nothing worked !!

I have different problems:

here is mydb info:

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

I tried to change character_set_server value to utf8mb4 by 

mysql>SET character_set_server = utf8mb4
Query OK, 0 rows affected (0.00 sec)

But when restart mysqld everything revert !

I don't have any /etc/my.cnf file in also, and I edited /etc/mysql/my.cnf file instead.

What should I do? How can I save emoji file in my database?

Solution

  • 1st or 2nd line in source code (to have literals in the code utf8-encoded: # -- coding: utf-8 --

    Your columns/tables need to be CHARACTER SET utf8mb4

    The python package "MySQL-python" version needs to be at least 1.2.5 in order to handle utf8mb4.

    self.query('SET NAMES utf8mb4') may be necessary.

    Django needs client_encoding: 'UTF8' -- I don't know if that should be 'utf8mb4`.

    References:
    https://code.djangoproject.com/ticket/18392
    http://mysql.rjweb.org/doc.php/charcoll#python