0

I have a form on my MVC website that allows the user to enter emoji characters as part of the text they submit.

My MySQL 5.6 database is primarily set to UTF8 but on the table that accepts this data I have set the field and table to:

Character Set: utf8mb4

Collation: utf8mb4_unicode_ci

However, when I insert the data, I can see at the point of entry into the table it encodes the emoji character as ?????. All other standard text remains intact.

Am I missing anything? I've seen articles like this which I've used to guide me: http://blog.arkency.com/2015/05/how-to-store-emoji-in-a-rails-app-with-a-mysql-database/

Any help would be greatly appreciated!

Thanks

scgough
  • 5,099
  • 3
  • 30
  • 48
  • 1
    Possible duplicate of [How to insert utf-8 mb4 character(emoji in ios5) in mysql?](http://stackoverflow.com/questions/7814293/how-to-insert-utf-8-mb4-characteremoji-in-ios5-in-mysql) – James P Nov 24 '16 at 18:41
  • @JamesP thanks. A bit of two things actually. The db is hosted on google cloud sql and `character-set-server` is set in the console on google. – scgough Nov 25 '16 at 07:21
  • Please provide `SHOW CREATE TABLE`. – Rick James Nov 25 '16 at 23:51
  • If `SELECT HEX(col)` shows `3F`, then the `INSERT` is at fault, and the Emoji cannot be recovered. – Rick James Nov 25 '16 at 23:53
  • Skip down to Rails in [_here_](http://mysql.rjweb.org/doc.php/charcoll#other_computer_languages). – Rick James Nov 25 '16 at 23:55

1 Answers1

0
ALTER TABLE mytable charset=utf8mb4, MODIFY COLUMN textfield1 VARCHAR(255) CHARACTER SET utf8mb4,MODIFY COLUMN textfield2 VARCHAR(255) CHARACTER SET utf8mb4;

First set the connection encoding to utf8mb4 and then change the database’s character set and collation to it:-

SET NAMES utf8mb4;
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Next we need to convert the relevant tables to utf8mb4/utf8mb4_unicode_ci (you will need to run this for each table):-

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(Replacing table_name with the name of the database table.)

Finally we need to update the character set and collation for the column(s):-

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(140) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Based on linky from here, credit to my carter

Transformer
  • 6,963
  • 2
  • 26
  • 52