CODEWITHSUNDEEP
mysqlcharacter-encodingcollationemojiutf8mb4
Code Help
Code Help

Reputation: 72

Emoji is not stored properly in MySQL 5.6 with collation utf8mb4

I am trying to store emoji to the database in my server. I am using AWS EC2 instance as server, my server details are listed below:

OS: ubuntu0.14.04.1

MySQL version: 5.6.19-0ubuntu0.14.04.1 - (Ubuntu)

Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503

I created a database test and table emoji in the server with following SQL:

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `test`;

CREATE TABLE IF NOT EXISTS `emoji` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

When I tried to execute the following insert, a warning appears and data are not stored properly:

INSERT INTO  `test`.`emoji` (`id` , `text`) VALUES (NULL ,  '👆 👇 👈 👉');

Inserted row id: 3

Warning: #1366 Incorrect string value: '\xF0\x9F\x91\x86 \xF0...' for column 'text' at row 1

The value stored in the text column is: ???? ???? ???? ????

The same scenario work for my local database and the values are stored properly. Almost all configurations are similar in my local except the OS (Windows).

Upvotes: 5

Views: 4722

Answers (3)

user2823085
user2823085

Reputation: 1

Migration from MSSQL to MySQL using workbench always getting problem.

Workbench already sets utf8mb4, and still getting error.

Then i follow @Haisum Usman's suggestion:

  1. Set column as Blob on migration sql generated.
  2. Migrate data
  3. Change column to LONGTEXT!

Lots of time invested to get this working.

Upvotes: -1

Haisum Usman
Haisum Usman

Reputation: 516

Trying to save emoji's in my existing database table using the following stack Node-Js 12.13.x , Mysql 5.6.

Way around:

  1. Either follow this solution
  2. Or change the column data type to BLOB i.e

ALTER TABLE table_name CHANGE column column BLOB NULL

Hope this trick will work for you!

Upvotes: 0

Bob Lukens
Bob Lukens

Reputation: 720

I was able to recreate your issue using SqlWorkbench.

You're client most likely has established a connection to the db whose character set does not match the character set of the table:

run this statement before you run the insert statement to align the character set and collation of the connection:

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci

Hope this helps, character sets can be tricky.

Upvotes: 5

Related Questions