Invalid string value without the character "? Encoding = UTF-8"

Question

I am trying to insert some text into a field in my database and I have a problem with emojis. It happens that if I did not set the connection url jdbc:mysql://localhost:3306/MyDatabase?characterEncoding=UTF-8 , then the server will store emojis just fine, but it will also store non-Latin characters as question marks.

Now, if I set my connection URL, then the server will not like emojis and it will display an error:

Incorrect string value: '\xF0\x9F\x98\xB1\xF0\x9F...' for column 'fullTweet' at row 1

I followed all the necessary steps for utf8 compatibility on my local server:

• I added the character-set-server=utf8mb4 to my.ini
• The query show variables like 'character_set_server' returns utf8mb4
• I create my database with the query CREATE DATABASE twitter DEFAULT CHARACTER SET utf8mb4
• and by default all my tables and fields of my tables use utf8mb4_general_ci (as I see in phpmyadmin )

What is missing? I am sure that I have taken all the necessary steps, and I still can’t get this to work, either it will only store Latin characters, or it will not store emoji.

---

Further information from the previous question:

I can manually enter emoji into the database and they are displayed exactly the same as they are displayed in the debugger (as fields). I ran this query:

 INSERT INTO `tweets`(`id`, `createdAt`, `screenName`, `fullTweet`, `editedTweet`) VALUES (450,"1994-12-19","john",_utf8mb4 x'F09F98B1',_utf8mb4 x'F09F98B1') 

and here is what the table looks like:

Answer 1

This is explained in detail in How to support full Unicode in MySQL databases · Mathias Bynens Basically, I think you will skip this (from Step 5 )

 [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 

Then you must remove characterEncoding=UTF-8 from your url. Also check that MySQL Connector / J 5.1.13+ (see JDBC URL for MySQL configuration to use utf8 character encoding )

Answer 2

Try it →

 convert('string' using utf8) 

or

 convert('string',char) 

or

 convert('string' using latin1) 

or use any character encoding that you set in the table. The code I provided is mysql functions.