I am trying to insert some text in a field in my database and I have a problem with emojis. What happens is that if I don't set my connection url to jdbc:mysql://localhost:3306/MyDatabase?characterEncoding=UTF-8
then the server will store the emojis just fine but will also store non-latin characters as question marks.
Now if I set my connection url like that then the server will not like the emojis and will output the error:
Incorrect string value: '\xF0\x9F\x98\xB1\xF0\x9F...' for column 'fullTweet' at row 1
I have done all the necessary steps for utf8 compatibility on my local server:
character-set-server=utf8mb4
to my.ini
show variables like 'character_set_server'
returns utf8mb4
CREATE DATABASE twitter DEFAULT CHARACTER SET utf8mb4
utf8mb4_general_ci
(as I can see in phpmyadmin
)What is missing? I am sure I did all the necessary steps and I still can't get this to work, either it will store only latin characters or it won't store emoji.
Additional information from a previous question of mine:
I can manually enter the emoji in the database and they show exactly as they show in the debugger (as boxes). I run this query:
INSERT INTO `tweets`(`id`, `createdAt`, `screenName`, `fullTweet`, `editedTweet`) VALUES (450,"1994-12-19","john",_utf8mb4 x'F09F98B1',_utf8mb4 x'F09F98B1')
and this is what the row in the table looks like:
This is explained in details in How to support full Unicode in MySQL databases · Mathias Bynens Basically, I think you miss 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 should remove characterEncoding=UTF-8
from your URL.
Also check you have MySQL Connector/J 5.1.13+ (see JDBC url for MySQL configuration to use utf8 character encoding)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With