CODEWITHSUNDEEP
mysql
Bob
Bob

Reputation: 8724

Storing emojies in mysql

I would like to store emojies in mysql (version 5.7.18).

My table structure looks like this:

 CREATE TABLE `message_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `is_read` tinyint(1) NOT NULL,
  `chat_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I am trying to save emojies in message field only and I can see that it gets saved with question marks (?☺️???).

Is there a way for me to read these values directly from the table (actually I would like to see emojies in table viewer). I am using SequelPro for viewing table (if that matters).

Exact mysql query that I am running INSERT INTO message_message(message, created_at, msg_sender_id, chat_id, is_read) VALUES ('💁👍', UTC_TIME(), 110, 164, False)

If I run select query on this table, it looks like this: +---------------------------------------------------------------------+ | message | +---------------------------------------------------------------------+ | 😁 | | 😁💁👍 | | 💁👍 | | 💁👍 | | 💁👍 | | 💁👍

Does this looks like data is stored correctly?

Upvotes: 1

Views: 191

Answers (3)

zerkms
zerkms

Reputation: 255155

Apparently, your data is stored correctly.

You provided this string F09F9281F09F918D as a result for SELECT hex(message) for the data inserted with

INSERT INTO message_message(message, created_at, msg_sender_id, chat_id, is_read) VALUES ('💁👍', UTC_TIME(), 110, 164, False)

And if one checks the UTF8 for both emojis:

  • F0 9F 92 81 for 💁
  • F0 9F 91 8D for 👍

then you would find that those exactly match with what you already have.

It means your code is correct and if you have any problems with your GUI application - it's a GUI application configuration or unicode support issues and is a bit out of topic for the stackoverflow.

References:

Upvotes: 2

Cole
Cole

Reputation: 441

Make sure your table collation is CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, to update this (in your case), the query would be:

ALTER TABLE message_message CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

Make sure your database's default collation is utf8mb4, to update this, the query would be:

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "DBNAME";

Upvotes: 1

Tommaso Belluzzo
Tommaso Belluzzo

Reputation: 23695

I think your table collation must be properly configured too:

 CREATE TABLE `message_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `is_read` tinyint(1) NOT NULL,
  `chat_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Upvotes: 1

Related Questions