How to store Emoji Character in MySQL Database

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

Solution 1:

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😜😀😊😃hzhzhzzhjzj 我爱你 ❌', 49, 1, '2016-09-13 08:02:29', '2016-09-13 08:02:29')

4) Set utf8mb4 in database connection:

  $database_connection = new mysqli($server, $user, $password, $database_name); 
  $database_connection->set_charset('utf8mb4');

Solution 2:

Step 1, change your database's default charset:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

if the db is not created yet, create it with correct encodings:

CREATE DATABASE database_name DEFAULT CHARSET = utf8mb4 DEFAULT 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_unicode_ci;

or alter table

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name MODIFY field_name TEXT CHARSET utf8mb4;

Solution 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) 

Solution 4:

Both the databases and tables should have character set utf8mb4 and collation utf8mb4_unicode_ci.

When creating a new database you should use:

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If you have an existing database and you want to add support:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

You also need to set the correct character set and collation for your tables:

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

or change it if you've got existing tables with a lot of data:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note that utf8_general_ci is no longer recommended best practice. See the related Q & A:

What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.

Solution 5:

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("🇺🇸🇨🇳🇯🇵🇩🇪🔳🔺🆔🆔🆑3⃣5⃣3⃣‼〽➗➗🎦🔆🎦🔆♋♍♋♍⬅⬆⬅⬅🛂🚹🛂🛄🚳🚬💊🔧💊🗿     "));
    System.out.println(decodeStringUrl("Hello+emoticons%2C%2C%F0%9F%98%80%F0%9F%98%81%F0%9F%98%8A%F0%9F%98%8B%F0%9F%98%8E%F0%9F%98%8A%F0%9F%98%8D%E2%98%BA%F0%9F%98%98%E2%98%BA%F0%9F%98%91%F0%9F%98%87%F0%9F%98%98%F0%9F%98%8B%F0%9F%90%84"));
}

public static String encodeStringUrl(String url) {
    String encodedUrl =null;
    try {
         encodedUrl = URLEncoder.encode(url, "UTF-8");
    } catch (UnsupportedEncodingException e) {
        return encodedUrl;
    }
    return encodedUrl;
}

public static String decodeStringUrl(String encodedUrl) {
    String decodedUrl =null;
    try {
         decodedUrl = URLDecoder.decode(encodedUrl, "UTF-8");
    } catch (UnsupportedEncodingException e) {
        return decodedUrl;
    }
    return decodedUrl;
}