Use urlEncoder to encode your String having emoticons. Store it in DB without altering the MysqlDB. You can store it in solr core(decoded form)if you want or you can store encoded form.
Absolutely it is possible to create your database with any emoji or even display the emoji in your SSMS (SQL Server Management Studio)'s result tab. The simplest possible way is to just type in emoji in the SSMS query window and run the query. It will display the image in the result tab.
The unicodes for emoticons are fully supported by the UTF-8 encoding; however, MySQL's utf8 does not! To save emoticons to a MySQL database we need to use utf8mb4 .
For beginners, emoji is a glyph, think of it as a font. Behind each laughing face, the emoji is a hexadecimal code point. Taking π€ βnerd faceβ emoji for an example, its hexadecimal code point (denoted by U+) is U+1F913 as listed in Emojipedia.
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');
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;
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)
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.
If you are using Solr + Mysql + Java, you can use:
This can be Used :
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;
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;
}
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