How to store Emoji Character in My SQL Database

Question

I am using Emoji character in my project. That characters are saved (??) into mysql database. I had used database Default collation in utf8mb4_general_ci. It show

1366 Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'comment' at row 1

Answer 1

step 1, change your database's default charset:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

step 2, set charset when creating table:

CREATE TABLE IF NOT EXISTS table_name (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

or alter table

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name modify name text charset utf8mb4;

Answer 2

If you are using Solr + Mysql + Java, you can use:

This can be Used :

• case1: When you don`t want to alter DB.
• case2: when you have to import emoticons from your Mysql to Solr core.

In above case this is one of the solutions to store your emoticons in your system.

Steps to use it:

Library used: import java.net.URLDecoder; import java.net.URLEncoder;

1. Use urlEncoder to encode your String having emoticons.
2. Store it in DB without altering the MysqlDB.
3. You can store it in solr core(decoded form)if you want or you can store encoded form.
4. When fetching these emoticons from DB or Solr core you can now decode it Using urlDecoder.

Code example:

import java.net.URLDecoder;
import java.net.URLEncoder;

public static void main(String[] args) {
    //SpringApplication.run(ParticipantApplication.class, args);
    System.out.println(encodeStringUrl("                                                                    

                                                        
            

查看更多

0人赞 添加讨论(0) 举报

Answer 3

The command to modify the column is:

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME TYPE;

And we need to use type = BLOB

Example to modify is as under:-

ALTER TABLE messages MODIFY content BLOB;

I checked that latest mySQL and other databases don't need '' to use in command on table_name, column_name etc.

Fetch and Save data: Directly save the chat content to column and to retrieve data, fetch data as byte array (byte[]) from db column and then convert it to string e.g. (Java code)

new String((byte[]) arr) 

Answer 4

1) Database: Change Database default collation as utf8mb4.

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

Query:

ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

3) Code:

INSERT INTO tablename (column1, column2, column3, column4, column5, column6, column7)
VALUES ('273', '3', 'Hdhdhdh                                                                    

                                                        
            

查看更多

0人赞 添加讨论(0) 举报

Answer 5

I have updated my database and table to upgraded from utf8 to utf8mb4. But nothing works for me. Then I tried to update column datatype to blob, luckily it worked for me and data has been saved. Even my database and table both are CHARACTER SET utf8 COLLATE utf8_unicode

Answer 6

My answer only adds to Selvamani P answer.

You might also need to change any SET NAMES utf8 queries with SET NAMES utf8mb4. That did the trick for me.

Also, this is a great article to port your website from utf8 to utf8mb4. In particular the article makes 2 good points on indexes and repairing tables after converting them to utf8mb4:

INDEXES

When converting from utf8 to utf8mb4, the maximum length of a column or index key is unchanged in terms of bytes. Therefore, it is smaller in terms of characters, because the maximum length of a character is now four bytes instead of three. [...] The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4.

REPAIRING TABLES

After upgrading the MySQL server and making the necessary changes explained above, make sure to repair and optimize all databases and tables. I didn’t do this right away after upgrading (I didn’t think it was necessary, as everything seemed to work fine at first glance), and ran into some weird bugs where UPDATE statements didn’t have any effect, even though no errors were thrown.

Read more about the queries to repair tables on the article.