Emojis are popularly used in websites and applications that have chat functionality. But it can be tricky to store emojis in backend database of your website or application. If you don’t store them properly, then they will be rendered incorrectly in your website or app, whenever you retrieve and display this information. In this article, we will learn how to store emoji in MySQL database.
How to Store Emoji in MySQL Database
Here are the steps to store emoji in MySQL database.
1. Change Database Collation
First step is to change the database collation to utf8mb4. To do this, log into MySQL and run the following command.
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
If your database has not been created then you can run the following CREATE DATABASE statement instead.
CREATE DATABASE database_name DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;
Replace database_name with the name of database where you want to store emojis.
2. Change Table Collation
Next, change table collation to UTF8MB4 as shown below. Replace table_name with name of table where you want to store emojis.
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE table_name MODIFY field_name TEXT CHARSET utf8mb4;
If you have not created the table yet, you can do so with the following command.
CREATE TABLE IF NOT EXISTS table_name ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
3. Store Emoji
Run an insert statement to add emoji data to your table.
INSERT INTO tablename (column1, column2, column3) VALUES ('273', '3', 'Hdhdhdh😜😀😊😃hzhzhzzhjzj');
Now when you display the above stored emoji on your website or app it will be displayed properly.
The key thing is to change the collation of table as well as database to support UTF8 character set.
How to Select Nth Row in Database Table
How to Search String in Multiple databases
How to Split Field into Two in MySQL
How to Fix Can’t Connect to Local MySQL Socket Error
How to Make Case Sensitive String Comparison in MySQL