Emoji is not stored correctly in MySQL 5.6 with utf8mb4 setting

Question

I am trying to save emoji in a database on my server. I am using an AWS EC2 instance as a 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 an emoji table on a server with the 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 the following insertion, a warning appears and the data is not saved properly:

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

Entered string id: 3

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

Value stored in the text column: ?????????????????

The same script works for my local database, and the values ​​are stored correctly. Almost all configurations are similar to my local ones, except for the OS (Windows).

Answer 1

I was able to recreate your problem using SqlWorkbench.

Most likely, you have established a connection with db whose character set does not match the character set of the table:

run this statement before running the insert statement to align the character set and match the connection:

 SET NAMES utf8mb4 COLLATE utf8mb4_general_ci 

Hope this helps, character sets can be complicated.