• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Which Character Set Should Be Used To Store Emojis in MySQL Database

by admin

As some emojis are using unicode code points beyond what can be represented with a three byte utf8 encoding, it is recommended to use utf8mb4 for columns that are used to store emojis. Alternatives are ucs16, ucs16le, and ucs32.

For example:

mysql> CREATE TABLE emoji (id int unsigned NOT NULL PRIMARY KEY, val varchar(12) NOT NULL) DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.57 sec)

-- Insert a value containing the emojis "high voltage sign" and "face screaming in fear":
mysql> INSERT INTO emoji VALUES (1, CONCAT(0xE29AA1, ' is scary ', 0xF09F98B1));
Query OK, 1 row affected (0.07 sec)

mysql> SET SESSION character_set_results = utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM emoji;
+----+-------------------+
| id | val               |
+----+-------------------+
|  1 | ⚡ is scary �� |
+----+-------------------+
1 row in set (0.00 sec)

The output above should be similar to the below screen shot if the terminal supports displaying the emojis:

character set MySQL for emojis

Be sure to test with the whole range of emojis being required for your application. Some emojis such as ⚡ can be stored in MySQL’s 3-byte utf8 encoding, but others such as face screaming in fear cannot.

Filed Under: mysql

Some more articles you might also be interested in …

  1. MySQL Cluster Point-In-Time Recovery (PITR)
  2. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  3. MySQL – How to Set Maximum Rates for Connections and Queries
  4. How to List Users and Privileges in MySQL
  5. Changing the value of lower_case_table_names in MySQL 8
  6. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size
  7. Understanding the REVOKE statement in MySQL
  8. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  9. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  10. How to Restore a Specific Database or Table (MySQL)

You May Also Like

Primary Sidebar

Recent Posts

  • “az storage blob” Command Examples (Manage blob storage containers and objects in Azure)
  • “az storage account” Command Examples (Manage storage accounts in Azure)
  • “az sshkey” Command Examples (Manage ssh public keys with virtual machines)
  • “az redis” Command Examples

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright