CODEWITHSUNDEEP
javamysqljson
user6325615
user6325615

Reputation: 91

Storing emoji's in MySQL getting incorrect String value error

I am storing data which contains emoji's the data is coming in the json form so when I retrieve it comes like

"Giggs'ern har vært å koset seg igjen(square bracket) Dette er en fillesak"

when I store it into MySQL I got this error:

2016-05-0213:12:45.582 [ERROR] pool-1-thread-1 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Incorrect string value: '\xF0\x9F\x98\x8A\x0A<...' for column 'comment' at row 1

I have changed the MySQL database encoding to utf8mb4 but it did not solve the problem.

Upvotes: 9

Views: 9067

Answers (4)

Shivang Agarwal
Shivang Agarwal

Reputation: 1923

If you are facing a similar issue in java and don't have the flexibility to change the charset and collate encoding of the database or that's not working for you, then this answer is for you.

you can use the Emoji Java library to achieve the same. You can convert into alias before saving/updating into database and convert back to unicode post save/update/load from database. The main benefit is readability of the text even after the encoding because this library only alias the emoji's rather than whole string.

Upvotes: 0

Avnish Jayaswal
Avnish Jayaswal

Reputation: 459

Please, follow these steps.

  1. Database: Change Database default collation as utf8mb4.

  2. Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.

How to alter Table

ALTER TABLE Table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

SQLCopy

  1. Set utf8mb4 in database connection:
  $database_connection = new mysqli($server, $user, $password, $database);
 
  $database_connection->set_charset('utf8mb4');

Upvotes: 10

Nyi Nyi
Nyi Nyi

Reputation: 966

THIS WORK FOR ME!

Change your character set of your table to utf8mb4 and collation to utf8mb4_unicode_ci. Lastly, change data type of your column you want to store emoji to blob or longblob.

Upvotes: 3

Δ O
Δ O

Reputation: 3710

I believe you need to use utf8mb4 encoding instead of utf8 (synonym for utf8mb3) both as the storage encoding (server side) and all the connections' encoding (clients' side).

Upvotes: 0

Related Questions