Saving emoji characters in mysql database

Question

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?

Well, yes, these settings revert. You need to set your ***connection encoding*** whenever you connect to the database as a matter of course. How exactly are you trying to insert what value exactly? — deceze, Apr 20 '16 at 14:32
I'm developing a Django app and using python `django.db.backends.mysql` as engine to connect to MySQL server. — Hosein Remezan, Apr 20 '16 at 14:39
Actually I could not edit exiting db, but when I create a new with utf8mb4 collation everything workes fine. — Hosein Remezan, Oct 31 '16 at 11:04

score 4 · Accepted Answer · answered Apr 22 '16 at 22:19

4

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

answered Apr 22 '16 at 22:19

Rick James

135,179
13
127
222

Warning. This will cause an error on Windows: https://stackoverflow.com/questions/38813689/cant-initialize-character-set-utf8mb4-with-windows-mysql-python – User Dec 09 '16 at 19:22

Saving emoji characters in mysql database

1 Answers1