7

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:

  1. I added the line character-set-server=utf8mb4 to my.ini
  2. The query show variables like 'character_set_server' returns utf8mb4
  3. I create my database with the query CREATE DATABASE twitter DEFAULT CHARACTER SET utf8mb4
  4. and by default all of my tables and fields of my tables are using 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:

1

Tanner
  • 22,205
  • 9
  • 65
  • 83
Aki K
  • 1,222
  • 1
  • 27
  • 49

2 Answers2

1

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)

Community
  • 1
  • 1
Fradenger
  • 596
  • 3
  • 10
0

Try this-->

convert('string' using utf8)

or

convert('string',char)

or

convert('string' using latin1)

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

Light
  • 375
  • 4
  • 11