Emoji is not stored properly in MySQL 5.6 with collation utf8mb4

Question

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).

Answer 1

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.