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.