MySQL InnoDB tables don't accept Emoji's with utf8mb4 encoding

687 Views Asked by At

I have been trying to get my DB to accept Emoji's by following steps from this tutorial as it appears to be the one that is linked to the most.

https://mathiasbynens.be/notes/mysql-utf8mb4

Even with a test DB I can't get this to work and so far I can't find the answer. Here is some test code that I'm running.

CREATE DATABASE testDB;

ALTER DATABASE testDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE testDB.test_table (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(191) NULL,
    PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

INSERT INTO testDB.test_table (data) VALUES ('foobar');

And the error that I recieve

Error Code: 1366. Incorrect string value: '\xF0\x9D\x8C\x86ba...' for column 'data' at row 1

There must be something else that I'm missing to make this work. So.. what is it.

Another clue to the puzzle is

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Returns

character_set_client    utf8
character_set_connection    utf8
character_set_database  utf8mb4
character_set_filesystem    binary
character_set_results   utf8
character_set_server    utf8mb4
character_set_system    utf8
collation_connection    utf8_general_ci
collation_database  utf8mb4_unicode_ci
collation_server    utf8mb4_unicode_ci

Which isn't correct but when I check the Options file in MySQL Workbench is see that under the General/International I have the following settings

Character-set-filesystem = binary
Character-set-server = utf8mb4
Collation-server = utf8mb4_unicode_ci

Contents of my.ini

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Thanks for your time and any help that you can provide.

0

There are 0 best solutions below